Switching between Access and SQL

[DTJeff]

Hi all.

Well, my project is progressing quite nicely until ....
... I came to locking.

Having had a bit of experience with both access and SQL, I have decided to make my app run on two different back-ends - Access for single users, and SQL (or MSDE) for multi-user environments.

The problem I am having is I cant change the connection string on start up to the other back end.

If I set up my data environment connection to Access, then only my Access connection string works, and vice versa with sql. If I say have my data environment set up to access (at design time), and then try to change the connection string to a sql one it gives me a "multi step old db" error. I'm sure I've done it this way before, but I just cant get my head around it.

Any help would be appreciated.

Jeff

P.S. I am pulling stuff like database name, servername etc from variables.

 

[johnwm]

Is there any reason that you don't just use MSDE for single users as well? It's free, and doesn't rely on having Access installed on the users machine.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[MagnaRider]

Have you considered using an DSN provider in your ADODB Connection object code?

Doing this would simplfy having to recomplie code based on the deployment, minimize "dll hell" with various User OS and future OLE DB upgrades.

 

[DTJeff]

Thanks for the replies.

I have considered using MSDE for single users, but having had to support a product which uses MSDE before, I know that it can be a bit of a pain to get the thing installed (from a users point of view), and by using an access DB file, nothing would need to be installed (except my software) which would simplify things considerably.

I have considered re-compiling for eachg version, but that would mean having two code bases to support instead of just the one.

I have found something else out since my first post though. If I use the Dataconnection.open "Connectionstring" instead, it gives me a "supplied provider is different from the one in use" error.

I'm sure there must be a way to achieve what I want.

Thanks

Jeff