How do you share an access file that uses (SQL Server) linked tables?

[dcol]

Hi



ODBC connections are really confusing me

How do you share an access file that SQL Server ODBC linked tables without creating DSN on each local machine?

I've inherited a database and I'm trying to enhance it by adding new linked tables

When I open the file, a number of linked tables, forms and queries open
All pointing to a SQL Server instance

When the file first opens, where do the linked tables come from? i.e. is there a macro (with security) that enables the linking
I can't understand how they become available for use if I do not have the appropriate DSN on my machine
Or am I missing something blatantly obvious!

Problem is, I can open the file and manipulate data held within these tables, but I can't add another linked table without locally creating an ODBC DSN reference
If I do that, how would it work on another machine that does not have the same reference?
It also looks different to the original ODBC connection string - leading me to suspect that the original string is held somewhere

Why, if there's something already under the hood allowing me to access the tables on my SQL server

Hope this question makes sense

Thanks

Damian.

[ItsMe]

I use VBA code and the SQL Server Native Client 11.0 ODBC driver. You can apply your connection string to a table or query's properties.

This post may be helpful ...
https://www.accessforums.net/access/...tml#post302766

[dcol]

Thanks

I can get a inked table working but the connection string does look different and I've no idea how they set this up originally
When you say 'I first delete the existing linked tables and then use records from a local table to recreate the linked tables' what do you mean?
Is there an additional local table that I need to create

[ItsMe]

I can get a inked table working but the connection string does look different and I've no idea how they set this up originally

If you look at one of the existing linked tables while in Design View, you can view the table's properties. In the Properties Sheet and under Description, you will see the Description property. This is the value you will use in VBA for the Connect property.

When you say 'I first delete the existing linked tables ... what do you mean?

The code creates a new table. If the table name already exists as a valid table object, the code will fail. The topic of that thread is about updating the connection string for existing tables. So, although it is relevant to the question regarding how to create a new linked table, it is not a perfect match.

When you say 'I ... use records from a local table to recreate the linked tables' what do you mean?

The following line of code assigns a name to the new table object being created.
t.SourceTableName = strTableName

the variable, strTableName, gets its value from a DAO recordset. This recordset is from a table. Within that table is a list of names that represent all of the tables I wish to destroy and then recreate.

Is there an additional local table that I need to create

No, but you will need to have a list of tables you wish to create. So, if you do not have a table, I am not sure where you will get the list of names. In the following example I am assigning the name of a table using a string literal.
t.SourceTableName = "MyFavoriteTableName"

[dcol]

Thanks, very helpful