Question about ADO/Access/ODBC/SQL Server

[Thai]

Hello, I am about to write a database program that I would like it to be compatible with a ms access DB OR sql server.. I think I know the direction I need to head for this to work, but can you gurus please verify some things for me. First, I would have the connection string go through ODBC instead of directly to the ms access DB using jet, correct? I have not used ODBC, so how would the connection string look and what does it work? Any information or links to a odbc tutorial that has to do with sql server/access would be helpful. Thank you!

Thai

[Gen-X]

1. Connection string through ODBC instead of Access Jet
Yes. ODBC stands for "Open Data Base Connectivity" which means by using it you can actually change whether it is using Access OR SQL Server simply by changing the DSN that you create in the ODBC control panel.

2. How does it look

Basically this depends on what kind of information you want to send to the server but here is a sample.

cnString = "ODBC;DSN=MyDatabase;"

This means that you have been into control panel and configured a new system DSN and called it "MyDatabase". This is the BEST method because you can change it manually and it isn't HARD CODED into your application. But if you wanted to hardcode it, use ODBC and NOT use a DSN you could do this instead :

cnString = "ODBC;SERVER=MyServer;Database=MyDatabase;uid=LoginName;pwd=MyPassword;Driver=SQL Server;"

What this means is that a SQL Server database exists on the server "MyServer", there is a database inside it called "MyDatabase", I am using the name "LoginName" to connect with the password "MyPassword" and please use the driver "SQL Server".

But if you want to change between access and SQL Server you will need 2 completely different strings. Instead you can create the DSN and not have to worry about all the other variables other than "ODBC;DSN=MyDSN;"

3. Links to tutorials
I have found that the MSDN and standard help files that come with VB are not bad on this topic. They take a little getting used to but you could also do a file search on your machine for the word "ODBC" and because it is installed there should be a ODBC.hlp or similar file that has everything you would ever like to know about it.

4. Things you should know
Although you can interchange between Access and SQL Server which is the whole point of ODBC you have to be very aware of the fact that both of these databases have their own functionality that the other one doesn't. This means that unless your SQL Statements are completely vanilla (comply perfectly with the TRANSACT-SQL syntax) swapping between one and the other isn't smooth.

For example. SQL Server has what are called "system tables" which allow you to have a look at tables, fields, foreign keys and everything else of the database you are connected to. You can write SQL queries that return this information. If you DID write these kinds of queries and then changes the back end to be an Access Database they wouldn't work because only SQL Server has those functions.

Hope it helps.