By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

MS Access - SQL Server backend location moved

P: 2
Hi,

We have an old MS Access frontend application which has a SQL Server backend. Recently the SQL Server database used as the backend was moved to a new server. I have updated the server location the ODBC DSN and it connects fine but when trying to use the MS Access application it is still pointing to the old server for some reason. I thought it would only be a matter of changing the server location in the ODBC but evidently the backend SQL Server location is hardcoded in the MS Access application somewhere. Problem is I can't find it and do not know where to update this. Any help would be much appreciate.
Sep 10 '07 #1
Share this Question
Share on Google+
3 Replies


Scott Price
Expert 100+
P: 1,384
Hi,

We have an old MS Access frontend application which has a SQL Server backend. Recently the SQL Server database used as the backend was moved to a new server. I have updated the server location the ODBC DSN and it connects fine but when trying to use the MS Access application it is still pointing to the old server for some reason. I thought it would only be a matter of changing the server location in the ODBC but evidently the backend SQL Server location is hardcoded in the MS Access application somewhere. Problem is I can't find it and do not know where to update this. Any help would be much appreciate.
This link is to a thread that discusses three ways to refresh the links between frontend/backend databases. http://www.thescripts.com/forum/thread688654.html

You should particularly look at post #16 and the comments that relate to it.

Regards,
Scott
Sep 10 '07 #2

P: 2
Hi Scott,

I wanted to thank you for your reply! It was much appreciated! I read through the thread provided and specifically post #16 and that does look like it will work for what I need. The problem then was I really do not know MS Access that well and I was not sure how to apply it. I am more skilled in SQL Server and web programming. I had been asked to move the sql server database which I did but was not aware that it would break this old MS Access application which is still in use but did not have anyone supporting it.

I had made a few attempts to apply the code provided in post #16 of the thread but had not got it to work so far and was in the process of writing you to see what I was doing wrong when I decided to try something and it worked for me.

What ended up working was refreshing the table links using the "linked Table Manager" as in http://www.techonthenet.com/access/tables/refresh_links.php. I actually had tried the linked table manager before several times with no luck. I had MANY linked table in this access db. I had changed the server in the DSN but the DSN used was still the same so I had been doing a "Select All" and hitting "OK" and after a few minutes it would come back and say "All selected linked tables were successfully refreshed" and at that point I could open the tables but the QUERIES and FORMS were still looking at the old server address for some reason and were erroring out because the DB no longer existed on the old server. I was not thinking that I would have to "Prompt for a new location" since the DSN was the same name but that is what ended up working. The last time I did this I did a "Select All" in the linked table manager AND I selected "Always Prompt For New Location". I had to reselect the DSN for each linked table which was tedious but once they were all successfully refreshed in this method the queries, forms and everything was then working and pointing to the new server.

Anyway I just wanted to share this in case it might help anyone else someday and thank you for your reply and help in steering me in the right direction.

Regards,
Ed
Sep 12 '07 #3

Scott Price
Expert 100+
P: 1,384
You're quite welcome, and thanks for posting back here with what worked for you!

Regards,
Scott
Sep 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.