Skip to content

Making linked MS SQL tables local in MS Access

April 27, 2015

My customer has a MS Access DB with about 15 linked tables.  In Access – that means the tables and their data do not really live in the Access DB at all, there is only a reference to the tables that live somewhere else.  The somewhere else in this case is a MS SQL 2008 Server.  The SQL server, that houses these tables is being retired.  It has been in read-only mode for about 6 months – now it is time to take the server off line, forever.

That leaves me in the position of how to handle the data in those DBs on the server.  My customer wants to still be able to access the data for perpetuity.  For up to 10 years, my customer is supposed to be able to provide data from this DB.

The question then is WHERE DO WE PUT THE DATA?  So she can actually still query it?

My initial attempts were to export the data from the SQL DB into spreadsheets.  Not too fancy – but still provides a copy of the data that is searchable.

My second attempt, a better one, was to export the tables from the SQL DBs into a new Access DB.  Upon completion, all the relevant data from the SQL DB was copied into a new Access DB – I had to recreate the relationships – they did not export – just the data.  This was better than the spreadsheets.

Then – my customer said “James why can’t we make the linked tables local in my existing Access DB?  Hmmmm.  As much as I did NOT want to work any more on this project, I had to admit, it seemed like a fine idea.  I had not considered it.  My customer said she had starting doing this a couple years ago but never finished, I thought it was something I could look at for her.

ODBC defined

Linked tables (access to SQL server) explained

I quickly realized in order for me to do anything with the linked tables in the SQL DB, from my computer, I needed to connect to them.  I created an ODBC connection to the SQL server to the first DB and then was able to refresh the link and right click the table and choose Convert to Local Table.  I followed this process for the remaining tables in DB 1 – but was not able to convert the tables in the other DB.  Then it occurred to me that I needed to create a second ODBC connection to the other DB.  I thought the connection was at the server level and would allow me to refresh – convert to local (both DBs).  In reality – the ODBC connection was at the DB level, not the server level.  Once I created the second connection – I was able to convert the other linked tables to local tables.

linked to local

When I was done – the size of the DB changed from about 50 MB to about 400.  A good indicator that the data had in fact become local – or now resided in the Access DB.

In the end, since I had *moved the data from the SQL DB to the Access DB, my customers were very happy because they could still use all the Queries and Reports she has created over the years to look up data in her old system.

Moral of the story?  Be patient and listen.  Good ideas can come from lots of places.  ODBC connections are to a DB, not to a server.


From → MS Access

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: