ODBC Database sources across multiple personel and Access database queries.

Question

ODBC Database sources across multiple personel and Access database queries.

Anonymous

Currently running linked ODBC database source tables into multiple Access queries. These tables are imported as a linked table to the source and granted permissions to them through one main password into the server. Effective February 2010, a new security measure was passed to have the password expire every 90 days. Which means in turn, after 90 days from 2/1/2010 all my linked tables will no longer be able to go to the ODBC database source for its information without updating the password.

Currently these tables are linked and the 'Save Password' checkbox is marked so that anyone can access this information without having to get special permissions. When having multiples of these types of databases I would have to go one-by-one and update the passwords etc.

So question is:

We are wanting to be able to have this ODBC work without having to 'Save Password' on each linked table. Is there a way in Access to have either:

1) Update to my new password in very few steps without having to import and re-save all tables again

2) Have the ODBC database source tables work for all without having to use a password to constantly ping the source

Basically, I dont have my password saved in all the tables so when this occurs the tables that belonged to an ex-employee will also break, and come that next Monday "the sky is falling" routine will happen unless I can find a way to have the tabnle update with my new password. Even if I only had to change the password once on each database, instead of on each of the 10-50 tables per database that we currently have.

Any insight/suggestions?!? Really stuck on this one and am needing even just a partially good/quick solution ASAP.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

So from what I gather, as I am not a major programmer...but should be able to implement this code if needed.

This code will basically just allow me to re-link the multiple tables in a single database when I first pen the database and run the code from above....Correct?

That's correct. The idea is that if the server side password has changed, you run this procedure with the new password within the connection string.

But let me ask my questions a bit differently for what I am really needing....the code above is a nice bandaid solution if needed but not really where we are needing to go for this implementation. Here is a couple of questions regarding what I would like.

1) Now is there a way to have the ODBC database source NOT prompt for a password if I set these tables up without a password that way everyone could run it once I have already pulled the linked table over once? (basically not save password in the checkbox then when ran it would not always prompt for a password)

I'm not sure if I understand well. If the connection requires a password, the server will ask for it. There is no way round.Whatever way you try to connect, e. g. through ODBC from Access, you have to follow these rules.

2) Implement a way to store multiple user ID and passwords that would update when the 90 day password expired. I am really wanting everyone to maintain their own passwords for the ODBC and wanting Access to grant them full access to the Access database .mdb (maybe it prompts for a password the first time they run it after a password change, but then never again until 90 days later when the password expires again)?

hopefully something is possible.

Well, here you could build an environment around, based on my short sample. Once you know that the password has changed you can let the user enter the new password in a form, and replace the password parameter in my sample with the one entered. The rest will work. Example:

strConnect = "ODBC;" & _

"Driver={SQL Server};" & _

"Server=YourServerOrIP;" & _

"Database=YourDB;" & _

"UID=User;" & _

"Pwd=" & Me!fldNewPassword.Value & ";"

The rest will work.

--

HTH - Peter

0 comments

2 additional answers

Answer 1

So from what I gather, as I am not a major programmer...but should be able to implement this code if needed.

This code will basically just allow me to re-link the multiple tables in a single database when I first pen the database and run the code from above....Correct?

But let me ask my questions a bit differently for what I am really needing....the code above is a nice bandaid solution if needed but not really where we are needing to go for this implementation. Here is a couple of questions regarding what I would like.

1) Now is there a way to have the ODBC database source NOT prompt for a password if I set these tables up without a password that way everyone could run it once I have already pulled the linked table over once? (basically not save password in the checkbox then when ran it would not always prompt for a password)

2) Implement a way to store multiple user ID and passwords that would update when the 90 day password expired. I am really wanting everyone to maintain their own passwords for the ODBC and wanting Access to grant them full access to the Access database .mdb (maybe it prompts for a password the first time they run it after a password change, but then never again until 90 days later when the password expires again)?

hopefully something is possible.

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more