CODEWITHSUNDEEP
sqlsql-serverms-access
steventnorris
steventnorris

Reputation: 5896

Connect to DB with Linked Tables

I want to connect to an SQL Server using Access, like Linked Tables. However, the DB is saved on a shared folder on a shared drive. When I created a data source, it is machine specific, so users on other machines can't access it.

Is there a way to connect tables from the SQL Server using a connection string, like you can with pass-through queries?

Upvotes: 3

Views: 513

Answers (1)

HansUp
HansUp

Reputation: 97101

You could use a DSN when first creating ODBC links to SQL Server. Then later change the .Connect property of the link TableDefs to use DSN-less connections. (See Using DSN-Less Connections for details.)

Here is an example of a working ODBC link to SQL Server which doesn't depend on a DSN. Note the .Connect property is a single text string, and I split that string on the semicolons to make it easier to read.

? Replace(CurrentDb.TableDefs("dbo_tblFoo").Connect, ";", ";" & vbCrLf)
ODBC;
DRIVER=SQL Server;
SERVER=HP64\SQLEXPRESS;
Trusted_Connection=Yes;
APP=2007 Microsoft Office system;
DATABASE=testbed

Or you could use the DoCmd.TransferDatabase Method with a DSN-less connection string to create the link.

Either way, your users would not need a DSN to use those linked tables.

Upvotes: 3

Related Questions