Using File>Connection in Access one can connect to database of choice. My application needs to be able to switch from local MSDE database to server-based Sqlserver database.

Can this be done via VBA ?

If not, can I distribute my Access application in any (secure) way that would enable a user to do this ?


__________________

Use ADO to make your connection to either Access or SQL Server. This works great for VBA.

Dim MyDb, MyRec as Variant
Set MyDb = New ADODB.Connection
Set MyRec = New ADODB.Recordset

Then use the MyDb.Open to connect to your database of choice.
A good source for ADO connections is http://www.able-consulting.com/ADO_Conn.htm

From there you can open your recordset like so
MyRec.Open "SELECT * FROM Table WHERE Field = 'Condition'", MyDb, adOpenDynamic, adLockOptimistic, adCmdText

make sure to close your connections when done. I hope this helps.

Thanks for that, but my need is to change the current connection - that referred to by:
CurrentProject.Connection
The connection established by File>Connection when in design mode in Access.
How to change this via VBA

I haven't got the syntax right. I tried:

strConn = "Provider=Microsoft OLE DB Provider for Sql Server;" & _
"Data Source=Sqldev;Initial Catalog=TestRig;" & _
"Persist Security Info=False; ;Integrated Security=SSPI;"
CurrentProject.Connection.Close
CurrentProject.Connection.Open (strConn)

and get the error:
Operation is not allowed when the object is open

CurrentProject.CloseConnection
gives the error:
6008. Microsoft Access encountered an error closing your connection


Any clues ?

I can't do it either, have trawled through msdn exhaustively to no avail. If you've found the answer please let me know. [email protected]

whoops, that should be
[email protected]