ODBC connection to linked tables using wrong ID (1 Viewer)

[Alc]

I have a database that was created around 15 years ago. Back then, a number of the linked ODBC tables were called other things and required different IDs and passwords in order to connect to them.

One of the users here was getting an error message when they try to run a select query against two of these tables (which was how I started investigating and found out about the whole ID and password change thing). The message is as follows:

 Run-time error '3146':
  
 ODBC-Call failed.
  
 [IBM][CLI Driver][DB2/LINUXX8664] SQL0551N "XXXXX" does not have the privilege to perform operation "SELECT" on object {tablename}. SQLSTATE = 42501 (#-551)

Clicking directly on either of the tables in the select query also informed me that "XXXXX" did not have adequate permissions.

1. I changed all references in the VB and queries, to refer to the new tables and not the old

2. I changed a section of code that runs when the database opens, from

 'Set db = OpenDatabase("", False, False, _
'         "ODBC;DSN=TRGTPRD2;" & _
'         "UID=XXXXX;PWD=YYYYYY;DATABASE=TRGTPRD2;")

to

 'Set db = OpenDatabase("", False, False, _
'         "ODBC;DSN=TRGTPRD2;" & _
'         "UID=AAAAAA;PWD=BBBBB;DATABASE=TRGTPRD2;")

Where the XXXXX,YYYYY, AAAAA and BBBBB are valid IDs and passwords.

3. I created a new link to the ODBC database, using the new ID and password, overwriting the old one.

4. I deleted all linked ODBC tables and reconnected using the new ID and password combination. Also removing the tables whose names have changed and replacing them with links to the new, relevant ones (checking that table structure hasn't altered)

5. I saved, closed and reopened the database

When I click directly on the tables in question, they now open without any problems. or error messages However, when I run the original, problematic query, I still get exactly the same error message as I did originally.

Looking in table MSysQueries, I can confirm that the connection string used when the query was run did include the old ID.

I'm relatively new to ODBC connections from Access, so I apologise if this is a really basic concept, but I can't work out where the query is still picking up the old ID from?

 

[Alc]

Addendum: I should have made it clear in the original post, but the problematic query is a pass-through query, just in case it gives anyone any ideas.

 

[Minty]

I think the pass through queries connection string is saved in the query itself. Look in the query properties, you'll probably find the old connection string.

 

[Alc]

I just found that out, using the clutching at straws approach. Not very familiar at all with pass through queries, although I have learned a fair but in the last few days.

Made the relevant change, all now working, then came on to update my post in case it helped anyone else and saw your comment. Many thanks.