Access front-end to multiple identical sql server back-ends

I have setup a simple retail system consisting of a local SQL Server (Express) back-end and an Access front-end which works very well. The front-end also consists of an 'admin' form to edit various system settings / client data etc.

There is in fact now 7 identical systems in place and I have a need to add the facility for the admin form to be used at one (central) location to update settings for one or more of the remote locations as required.

I'd like to add a dropdown to the admin form, from where a location can be selected and then the form will allow editing of that location's data. I'm fine with setting up remote access to sql server tables / router ports etc. but not sure of the best way to implement the location selection without having 7 identical forms, each linked to the different location's tables.

Is there a way to link different tables via code so the dropdown selection would run a subroutine to perform the table linking and enable the same admin form to then update a particular location? I guess I could link all the tables to the Access db in the first place then just change the recordsource of each form/subform required as one solution?

Hope that makes sense, any other ideas / suggestions welcome.
Thanks
nigelr99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ggzfabCommented:
In a similar case I've created a form with a tab control. The tab-name is the environment and the subform is working with the linked table(s) from the tab name.
You could make this dynamic, but why bother when the (sub-)form's are identical and the effort to copy/paste a subform is minimal...
0
mbizupCommented:
Are you looking for a way to automatically relink to different SQL Server back-ends?

Take a look at this article:
http://access.mvps.org/access/tables/tbl0010.htm

You might be able to start with this code as a basis and revise it to determine the connection string based on a drop-down selection from the admin page.

(Untried/untested by me, but it looks promising)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You don need either.

You can link all sets of tables:

tblOne_1
tblTwo_1
tblThree_1
tblOne_2
tblTwo_2
tblThree_2

Now, to use location 2, just rename:
tblOne_2
tblTwo_2
tblThree_2

to:

tblOne
tblTwo
tblThree

given that these are the names your forms use.

When finished, rename them back before switching to another location.

Also, this method is extremely fast as no relinking takes place.

/gustav
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is there a way to link different tables via code so the dropdown selection would run a subroutine to perform the table linking and enable the same admin form to then update a particular location? >>

  Yes, and changing the table links sounds like the best approach.   Each linked table contains a .Connect property, which either points to a DSN, or contains all the info to get to a data source directly (Server name, database name, username, password, etc).

 Along the same lines, there is one alternative approach and that have the tables all use a DSN, and have a routine that changes where the DSN points to.  I would use this if you need users outside of the application to have access to the data (say from Excel).

 If no need exists, then I would drop using a DSN and just modify the tabledefs .connect property directly.  All you would need to do is replace the value for the server and/or database name.

Jim.
0
nigelr99Author Commented:
Thanks - some good suggestions there to consider.

One question gustav - can I rename linked tables in code or are you thinking along lines of manually renaming each time?

I've thought along the lines discussed by ggzfab but my admin form actually contains 8 subforms on different tab pages so that would give me 56 subforms altogether which doesn't seem very efficient.

My best course of action would initially seem to be changing tabeldef.connect property as suggested by JDettman
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<My best course of action would initially seem to be changing tabeldef.connect property as suggested by JDettman >>

 That's what Mbizup pointed out.   I was only pointing out that changing DSN's (if you use them) was another option rather then relinking the tables (which you could always leave pointing to the same DSN).

Jim.
0
Gustav BrockCIOCommented:
You can easily rename a table via code:

CurrentDb.TableDefs("tblTest_2").Name = "tblTest"

/gustav
0
nigelr99Author Commented:
Thanks for all the pointers - gave me just what I was looking for!

I was able to use the code in the provided link with very little modification. I then created a local table containing my locations with server name / database name / uid and password. A simple form containing a dropdown of these locations then allowed me to connect all linked tables to selected db - sorted!

I wasn't able to figure out how to put the dropdown on an existing form and then 'refresh' the form based on the drop down selection - it seems Access doesn't take a requery or refresh as a trigger to look at the revised tabledef and keeps the previous connection open. This isn't a big problem for me as I'm re-mapping a couple of dozen tables anyway.
Thanks
0
nigelr99Author Commented:
Apologies - my last comment was incorrect as I didn't in fact use the code in the link provided by mbizup but followed through with that idea and eventually used the less complicated code provided at the link below which re-links all odbc tables using passed server/database parameters.

Just thought I should add this in case anyone else comes across the question. Cheers!

http://www.accessmvp.com/djsteele/DSNLessLinks.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.