SQL linked to Access add more tables

0
votes

Ok, I am using an Access 2010 database linked to a SQL 2012 backend. All is well. I migrated using the Migration Wizard - this creates the link without using any pre-created ODBC\DSN file etc. This works great as I do not have to deploy any DSN file to users (it is used by 21 users) I have noticed that linked tables have the below in the Properties\Description:

ODBC;DRIVER=SQL Server;SERVER=XXX;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=XXX;Network=DBMSSOCN;TABLE=dbo.tblCountries

Now my problem is if I create a new table in the SQL backend, how do I link without using ODBC\DSN? How do I link so it behaves the same as when migrated?

I have tried creating a table then using the description from above and changing the table name to no avail. If I do link via ODBC to the SQL backend, link to the new table then change the ODBC to match that above, Access does not let you change the connection string?

So in summary, are you able to link to new tables in SQL server, after you have used the migration wizard to link?

THanks,

Michael

1
sqlsql-serverms-accessodbcms-access-2010
Don't have access to Access at the moment (say that three times fast) but if I recall correctly there is a menu open called "Linked Table Manager" or something similar. I think you can do it there. - dazedandconfused

1 Answers

0
votes

The DSN connection string is actually stored with the linked table. That's why your users can connect to the linked table without needing to set up a DSN connection.

I do very similar work on a daily basis with Microsoft Access connecting to a SQL database server. I have set up ODBC connections on my development machine, which allows me to easily list the tables available on the SQL server databases, and link to them in my Access applications. When linking to the table, I always save the connection string with the table link, so the end users never need the ODBC connection.

In a sense, you can think of your ODBC connection as a template for connecting to the database. As long as you always save the connection string with the linked table, your users won't need the DSN connection to access the database.

There are times when it is appropriate to use a DSN connection, but in my experience, I find it far more practical to just save the full connection string with the linked table and manage the links using the Linked Table Manager.

Hope that helps!

Adam