Linked Tables with ODBC – password prompt

[Bennie47250]

Have a PC that has been running FMS’s product Total Visual Agent for several years now. One of the things I’m using it for is to run macros and most of the times these macros have queries that hit ODBC linked tables on both our AS400 and MS SQL databases.

Until a few weeks ago this PC just sat in the corner and ran, did not need to really monitor it. But recently it has “lost” the ability to log onto the linked tables. The queries will attempt to run but fail after not connecting to the databases.

To connect, I can go to the ODBC manager, open the data source and test it. This works for the MS SQL data sources but for my AS400 data sources, don’t have the same data source options. To reconnect, I can go into the linked table manager and refresh the links. I have looked at the MSysObjects table and for the MS SQL tables the connect field has the data source name, user ID and password. However the AS400 tables only has the data source name if it is using the client access driver.

I would reboot this PC weekly and the first time it accessed the AS400 table it would prompt for a log on password but then it would be good till the next time I rebooted the PC. However never really had any issues with the MS SQL databases.

The PC does not receive MS updates so it has not received an update that broke this.

Any thoughts as to what might be going on and how to resolve?

Thanks Bennie

 

[kjv1611]

To make sure the password is saved (if that's what you want, understanding that it is stored unencrypted in the database file), you can:
1. Delete or rename the current linked table(s)
2. Re-Select and Link in the same table(s).
3. This time, in the linking process, make sure you check the box on the right that says "remember password" or "save password".. you'll get one or two warning prompts from MS Access, just ignore those.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57