Changing DSN (1 Viewer)

NearImpossible

Registered User.
Local time
Today, 03:10
Joined
Jul 12, 2019
Messages
225
Currently I have 2 matching databases, 1 for Production and 1 for Testing, that are setup with different DSN files for linked tables and queries.

Is there a way to easily change the DSN setup on the access side instead of removing all the linked tables/queries to the Testing DB and re-linking them to the Production DB whenever I make a change to the access side of things?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:10
Joined
Apr 27, 2015
Messages
6,286
Hey there NearImpossible,

Several ways to go about this. I think the best way would convert to DSN-less connections. You could use two connection strings, one for the Production and the other for Testimg.

Then it would be a simple matter of commenting out whichever connection you did not want to use...
 

isladogs

MVP / VIP
Local time
Today, 08:10
Joined
Jan 14, 2017
Messages
18,186
Agree with NG
I do this all the time using DSN-less connections
You will still need to remove/relink but the process should be simpler.

You may find the discussion on this thread useful: https://www.accessforums.net/showthread.php?t=77571
In post #18, I supplied the code I use for DSN-less connections
 

June7

AWF VIP
Local time
Today, 00:10
Joined
Mar 9, 2014
Messages
5,424
I use VBA to modify table links for Access backend. What db platform are your backends?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:10
Joined
Oct 29, 2018
Messages
21,358
Fourth vote for using VBA (you are basically using a DSN-less connection already). Cheers!
 

NearImpossible

Registered User.
Local time
Today, 03:10
Joined
Jul 12, 2019
Messages
225
Thanks to all, i'll check the link later. It is definitely a bit of a pain have to remove/readd every time, however I did find help with the tables, just not the queries

  1. Click on External Data > Linked Table Manager
  2. Check the box at the bottom for "Always prompt for new location"
  3. Select all tables
  4. Click OK
  5. Select the DSN file to use

June7 - I am using SQL for the backend
 
Last edited:

NearImpossible

Registered User.
Local time
Today, 03:10
Joined
Jul 12, 2019
Messages
225
some of the queries are pass-through queries

Guess I could just leave the DSN alone on the queries as the tables are the same in both DBs....
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:10
Joined
Feb 19, 2002
Messages
42,981
This database has a form that lets you pick a connection string. It also relinks pass through queries.
 

Attachments

  • RelinkODBC.accdb
    500 KB · Views: 95

NearImpossible

Registered User.
Local time
Today, 03:10
Joined
Jul 12, 2019
Messages
225
Agree with NG
I do this all the time using DSN-less connections
You will still need to remove/relink but the process should be simpler.

You may find the discussion on this thread useful: https://www.accessforums.net/showthread.php?t=77571
In post #18, I supplied the code I use for DSN-less connections

Any chance you could upload that zip file on here as it requires me to create an account and login in order to download it?

please advise

thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:10
Joined
Oct 29, 2018
Messages
21,358
Any chance you could upload that zip file on here as it requires me to create an account and login in order to download it?

please advise

thank you
Hi. Did you try Pat's demo in post #9? What happened?
 

isladogs

MVP / VIP
Local time
Today, 08:10
Joined
Jan 14, 2017
Messages
18,186

Attachments

  • RelinkCodeExample.zip
    122.2 KB · Views: 80

NearImpossible

Registered User.
Local time
Today, 03:10
Joined
Jul 12, 2019
Messages
225
Thanks, i'm going to try to work on this next week, for some reason, I can't seem to understand how to set this up for some reason, which is driving me nuts because I can usually look at something and eventually figure it out
 

isladogs

MVP / VIP
Local time
Today, 08:10
Joined
Jan 14, 2017
Messages
18,186
No problem. If you need further help just come back for advice
 

NearImpossible

Registered User.
Local time
Today, 03:10
Joined
Jul 12, 2019
Messages
225
Hi. Did you try Pat's demo in post #9? What happened?

From what I could tell, I would have to build a query to assign the PK for every one of my tables in order to use that one.

Could be wrong, as it was just a quick look so I figured I would check out isladogs example as well
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:10
Joined
Feb 19, 2002
Messages
42,981
No. You don't need to assign PKs manually. The pass-through queries in the db are there because Access doesn't "see" a PK when some views are linked. So, when the view is linked manually, it asks for the user to pick a PK. If that happens, refreshing the link requires rebuilding the PK. If you don't have that problem, you don't need to manually run those type of pass-through queries. They are there as examples.
 

Users who are viewing this thread

Top Bottom