Switch SQL Connections

[tredd68]

What is the best way to connect to different SQL Server Databases from a form
in Access 2007? I have a server with multiple databases. I want my users to
be able to click on a button from a form to switch the connection from
database to another.

 

[Sylvain Lafontaine]

For an ADP project or for a MDB or ACCDB database file with ODBC Linked
tables? For an ADP project, I would say to use the
CurrentProject.CloseConnection and CurrentProject.OpenConnection method.
Instead of trying to discover each available servers and database on the
network, you could store the address of the server in a table in each
accessible database (or locally in the VBA code or in properties) for an
easy display.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[tredd68 via AccessMonster.com]

Thanks, I will give it a shot.

For an ADP project or for a MDB or ACCDB database file with ODBC Linked
tables? For an ADP project, I would say to use the
CurrentProject.CloseConnection and CurrentProject.OpenConnection method.
Instead of trying to discover each available servers and database on the
network, you could store the address of the server in a table in each
accessible database (or locally in the VBA code or in properties) for an
easy display.

 

[Vadim Rapp]

Hello,

t> What is the best way to connect to different SQL Server Databases from a
t> form in Access 2007? I have a server with multiple databases. I want my
t> users to be able to click on a button from a form to switch the
t> connection from database to another.

If this is the same server, then you can use the same connection, no need to
switch anything. Dynamically concatenate the sql string and then specify it
as recordsource etc. Like this:

dbname = inputbox("enter db name:")
me.recordsource = "select * from " & dbname & "..table where ...... "


Regards