Multiple SQL Databases for Same Access Front End
I am a bit of a newbie at this stuff, so be gentle.
Here is the situation
I have two locations, with a SQL server in each location.
I have a SQL database at location A which is replicated to location B (All working OK)
I have one front end access 2007 front end database that i would like to use for both sites.
I have set the database to link to tables stored in location A SQL, using a ODBC connection i created. (this works fine)
I have modified the front end to point to location B SQL and created an ODBC for that one, and again tests ok.
However i would like to put in a start up form that asks the end user what location A or B and depending on the response given, use VBA to set the data source to the correct location.
Currently the tables are linked (access linked tables) whilst this was fine when i had only one location it is painful now with two, as i am still developing some forms, which means i have to maintain two front end applications, which is prone to error sooner or later.