VB6, ODBC connection to ACCESS or MS SQL Server

Hi Dhaest, thanks for the response.
In the case of MS SQL Server do I not have to include UserID & Password in the connection string, in which case I don't really want to make that info available in a configuration file.
Thanks

Just put a 0 or 1 in the configuration file then and then you can select the connection with an if-statement in your code

Do you realise the the SQL dialect, the API, and the fundamental architecture between MS Access and SQL is quite different?

You aren't going to be able to just run the same code through two different ODBC drivers. Its not going to work.

Thanks again Dhaest.

Hi nmcdermaid, thanks for pointing that out, is it possible to briefly point out the problems I need to be aware of with regards to the SQL dialect and the API?

Thanks

I don't know your application architecture - whether you use ADO to connect and use recordsets heavily, or whether you submit SQL statements through it.

I suggest that you do up a quick proof of concept and manualy switch the DSN name on your application. You'll quickly see that calling two different database systems with the same code doesn't work very well.

As a very simple example, MS Access has a SQL function called DSum which does not exist in SQL Server. So if you were to submit that SQL to MS Access it would work, and if you were to submit it to SQL Server it wouldn't.


You can of course code around all of this stuff but you are looking at a lot of effort and a maintenance nightmare.

If you purely use recordsets then maybe you could code around it but then you are not leveraging the power of SQL Server and you may as well stay on MS Access.

Just again..... you need to quickly do a proof of concept application and test this concept of what you are trying to do. You will quickly find there are a lot of issues.

Hi nmcdermaid,
Thanks for the comprehensive response. I am using ADO recordsets throughout the application and do not use any SQL functions such as DSUM. All my SQL statements are relatively straight-forward Select, Update or Delete statements. I am aware of the differences in referring to date values between MS Access & SQL Server, i.e. #date value# as opposed to 'datevalue' and was hoping this would be my only area of contention. If you know of any other areas that I should be aware of I'd appreciate it if you could let me know.
Thanks again

You can also show a form to the user allowing him to select the database, and internally use an if else statement to choose the appropriate connection string.