I have a database with about 50 tables linked to a SQL Server catalog. I have a catalog for each month using the following naming convention MTHYYYY.
To switch between catalogs I currently update my ODBC connection pointing to a new month then update all linked tables in my Access database using the "linked table manager" selecting "always prompt for new location". The catalogs are secured usingSQL Server authenication and are the same for each catalog.
I would like to use VBA to update my linked tables and or point my ODBC connection to a new month allowing me to run queries across multiple months without have to change my ODBC connection and update all linked tables manually. Also - it would be a bonus if other users / computers could connect to these tables w/o having to manually configure an ODBC connection.
Current Connection: DSN=Prime;APP=Microsoft Office 2003;WSID=SERVER;DATABASE=MTHYYYY;Network=ANON