MultiDatabse - Seperate Reports

[Dsquad]

My company has multi locations that each go under a different business name. Thus, we maintain 7 different databases on a single SQL server. The SQL databases are identical in design.. I have written various reports that are identical for each company (except for heading info etc, etc).

The problem is, when I make any changes to the master, I have to go and edit each report header for each company as well as use the LINK TABLE manager to point to each companies DSN (which takes forever)..

I use FIle DSN for each comany that is located on a network drive so all usr can access it.

My question is: If I want only 1 access database, is it possible to prompt for which DSN I want to use each time the database is run ??

Any help would be greatly appreciated....

 

[Pat Hartman]

Try this article to get yourself started.

Q128878 - ACC How to Create a Customized Attach ODBC Table Form

 

[Dsquad]

ummm

Can you link where to go ?

(sorry new to these)

 

[Dsquad]

Try this article to get yourself started.

Q128878 - ACC How to Create a Customized Attach ODBC Table Form

Where do I go to get:

Q128878 - ACC How to Create a Customized Attach ODBC Table Form

 

[Pat Hartman]

You can find this at the Microsoft Knowledge base site. Search for article number - 128878.

http://support.microsoft.com/support/c.asp?FR=0

 

[Dsquad]

You can find this at the Microsoft Knowledge base site. Search for article number - 128878.

http://support.microsoft.com/support/c.asp?FR=0

Thanks for the help Pat.. I went through the process explained on the MS site.. It more applies to actual table navigation and not database navigation.

All of the databases have identical structure. I just want to be able to somehow change which DSN the access database points to via some sort of magical selection screen.


The only other option I have currently is to allow users access to the DATABASE / LINK MANAGER options, but that would be way to scary...

I am sure there is an easy way to accomplish this, but it escapes me.... HELP !!

 

[Pat Hartman]

These articles give you a little more information. There are no articles on the kb that EXACTLY answer your question. You CAN do what you want. I don't have sample code to post. You will need to interpolate the information from the articles. There is a sample in the solutions database that shows how to relink Access tables. The procedure is basically the same for ODBC except that the connection strings are different. If you want to see your current connection strings so you know how to modify the code, open the MSysObjects table and look at the connect field for your linked tables. You can also see the string in the LinkedTables Manager dialog but the strings are long and there is no way in this dialog to expand the field view.

ACC2000: Procedure to Create Data Sources and Relink ODBC Tables

ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables

Solutions sample db