Hi,
I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".
When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.
How can I use DDL statements via ODBC?
I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.
Thanks for help!
Regards,
Stefan D.
Once upon a time (if I recall correctly -and even if I don't it makes for a good 'story')...
OBDC (Open DataBase Connectivity) was established as a result of a consortium of product vendors working to find a common API to allow users using a common 'language' (ANSI SQL) to access data in any vendors database. So far, the supported ANSI SQL is limited to SELECT INSERT, UPDATE, DELETE statements.
Each vendor has proprietary extensions to the SQL Language to create/define/change/remove the database objects.
Short version -you can't.
Your choices are to use the vendor specific database management tool to create the tables, and then use ODBC to handle the data operations,
(OR), embrace ADO, or even OLEDB.
|||Ah, that's a pity, but thanks for your answer!
So then I have to use ADO. Is there a way to get access to any database format that is stored in files (like .mdb) without having to install a driver for it on a standard windows XP installation? For OLE to mdb I have to install the JET-Engine, am I right?
Thanks for answer again!
|||This may be wrong, but, I think that the full JET functionality is included in MDAC 2.x, and that MDAC 2.x is automatically included with and installed with Windows OS (since Win 2000/XP) and service packs.
|||Alternatively, you might also consider using SQL Server Express or SQL Server CE as your backend database and then using the System.Data.SqlClient as your provider API. This would also be beneficial in that your developement experience would be greatly simplified with the data scenario support that is provided in Visual Studio 2005.|||Well, if it is true that JET is installed with Windows service pack, then I wonder why I get an ISAM not found error...
|||My software is already supporting MySQL and Oracle back ends (Oracle is in its XE 10g version also free) with a single class working with ODBC. That's the reason why I wanted to use ODBC to access the mdb, too.
In addition to the two server-based solutions now I want a third, file-based solution which is slim enough for a standard business laptop (which are server-based solutions definitely not) and does not need any extra installation effort (like an extra driver or database server).
So, thanks for your reply, but I still insist on a file-based solution. Any ideas?
|||Stephan,
You might explore SQL Server CE (or maybe it's now called 'Mobile'). Very small footprint, very small demand on resources -and yet suprisingly a lot of functionality.
And as i indicated before, the JET drivers are automatically installed on Windows 2000 and above.
|||ISAM not found errors are often a result of incorrect or improperly formed connection strings.
Check this thread for one type of problem.
|||
That's the reason why I wanted to use ODBC to access the mdb, too.
You CAN use ODBC to access the mdb file -SELECT, INSERT, UPDATE, DELETE works the same as in MySQL or ORACLE.
You just can't use ODBC to CREATE TABLES, etc.
You have to use JET (ADO / OLEDB) for that.
|||Great, thank you!
After you've convinced me that Jet is really installed in standard windows installation, I came closer to my error. Didn't know that the OLE driver does not like additionally specified parameters in that string than those it needs.
Now it works, thanks!
This problem is thus resolved.
Thanks much to all for your help!
|||After you've convinced me that the driver is really installed in an original windows installation, I came close to the real error. My connection string was indeed malformulated.
Thanks to all for their help!
This issue is thus resolved!
No comments:
Post a Comment