Connect to DB with Linked Tables

Question

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?

Are you building a front end then? If so you could do it by writing vba code and using connection strings in that instead of odbc... — twoleggedhorse
– twoleggedhorse, Commented Dec 14, 2012 at 17:43
I think this has been answered already: stackoverflow.com/questions/11023053/… — twoleggedhorse
– twoleggedhorse, Commented Dec 14, 2012 at 17:44

HansUp · Accepted Answer · 2012-12-14 17:49:55Z

3

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.

edited Dec 14, 2012 at 17:49

answered Dec 14, 2012 at 17:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connect to DB with Linked Tables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related