1

First of all - my VB skills are almost non-existent so any suggestion/ideas should be a little more elaborate if possible. Here is the problem I am facing -

We have linked database tables and queries in Ms Access 2010. Now a copy of the database has been created on a new SQL server. So I want to re-link the tables so that access points to the tables on the new SQL server. Right now, all the tables are corresponding to the database on the old server. Here is what I have tried -

  1. Open XYD_Outreach_Dev.accdb on the network (this is the dev version of access that we use)
  2. Form a new ODBC connection which creates a new_sql_server.dsn file corresponding to the new SQL server (2012)
  3. Go to External Data -> ODBC Database -> Link to the data source by creating......
  4. Link to a table say - tblAbriContacts
  5. This creates a new linked table dbotblAbriContacts
  6. I rename it to tblAbriContacts at which point Access asks me -> table already exists. Do you want to replace it? I click yes and the table gets replaced
  7. I hit the save button, close the application and restart it. The tblAbriContacts is pointing to the old database again

What could be the issue? Please note that I have to do this for all the database, lookup and junction tables.

Do I have to change some connection string which the access points to as soon as it is launched?

If I try the Linked table manager, nothing happens, it says "All selected tables were successfully refreshed". But nothing happens. I guess that's because the tables haven't been moved from the old database. A new copy of everything has been created on the new server.

Please help me out with this. My work is stuck because of this reason.

JNP
  • 21
  • 1

1 Answers1

0

Can you please check if there is a module or other vba file created and file's on_load event has some code which to assign the connection string for all objects (tables and SPs) and refreshes it every time you restart the application. If you see the connection string there, you might need to replace it with the new one.

Jigar
  • 216
  • 1
  • 8
  • Yes, There was indeed a connection string form which was used to set-up the connection string. Found it and updated it. The tables are now linked. Thanks! – JNP Jul 20 '15 at 20:45