0

I have an Access application that writes to a SQL Server database. I would now like it to write to a different SQL Server database instead.

  1. Made sure the new database if properly configured with dbowner permissions for my user credentials
  2. Wrote a test record using that user credentials
  3. In the Control Panel, set ODBC source to the right database. Tested connection successfully
  4. In Access, Database tools > Visual Basic section, I changed the connection string and substituted the login credentials with the new userid and password.

But when I input values in the form, it STILL saves to the old database.

I did notice that in the Linked Table Manager, the linked tables still point to the older database. But when I select all and click OK, the message is 'All selected tables have been successfully refreshed'.

What do I need to do to point the tables to the new database?

Edit

I did a 'Find' for the old database name and edited the connection string on all the forms where-ever that old database name existed and replaced it with my new database name.

Example:

.ConnectionString = "Provider=SQLNCLI11;Data Source=(old datasource name);Initial Catalog=(old database name);User ID=(old userID);Password=(old password)"

changed to

.ConnectionString = "Provider=SQLNCLI11;Data Source=(new datasource name);Initial Catalog=(new database name);User ID=(new userID);Password=(new password)"
halfer
  • 19,824
  • 17
  • 99
  • 186
user1777929
  • 777
  • 1
  • 10
  • 28
  • How are you connecting to the database? Without some code there is no chance anybody can point you in the right direction. – Sean Lange Jul 19 '17 at 16:52
  • 1
    You need to change the connect string for each linked table. See https://stackoverflow.com/a/32316883/3820271 (ignore the part about the views). – Andre Jul 19 '17 at 17:19
  • I've changed the connect string everywhere. Every place that the older connection string was, I replaced it with the new connection string._____ When adding the new linked tables though, it asked me to select primary keys. There are more than 300 tables. Do I need to select primary keys for ALL the tables? – user1777929 Jul 28 '17 at 15:05

1 Answers1

0

It sounds like you need to delete your old linked tables and create new ones. If you hover over the linked table it should show the connection string.

If you are worried about losing your old linked table, you could rename it to something else.

jshort
  • 353
  • 1
  • 2
  • 15