0

I created an MS Access Database that consists of about 25 ODBC Database Links that are connected to one table on each of these databases. Each of these tables have the same structure, but different data. Then, I Union these tables with a query so that I can have all the data available in one view.

The problem that I'm having, is that every time I try to use the query from a reporting software (Crystal Reports), I have to manually connect to all 25 databases, which is very tedious.

I made a VBA connection Script that connects to each of the databases using its log information, however I don't know how to trigger this code from an event like Opening the Query or something like that.

Any ideas?

Improve this question
12
  • VBA only runs from an office application. Commented Apr 11, 2014 at 17:24
  • @ckuhn203 I understand. I'm talking about MS Access, which is part of Office. Commented Apr 21, 2014 at 12:15
  • so you have your mdb file with its ODBC connections to the 25 or so tables and your union query available in the same mdb file, as a 'sql-specific' union query ... is that right? Commented Apr 21, 2014 at 12:28
  • 1
    An Access query does not expose events which you can use to run VBA code. Could you drive this from Access instead --- set up your connections and then call the Crystal Report? Commented Apr 21, 2014 at 13:18
  • 1
    @Blackhawk An AutoExec macro fires when the db is opened in an Access application session. But if Crystal Reports only reads data from the db file without starting an Access application session, the AutoExec will not run. I don't know how Crystal interacts with an Access db file. Commented Apr 21, 2014 at 15:00

1 Answer 1

Reset to default
2
+50

You'll need to relink the tables in your Access Database on the "External Data" tab. When you get to the "Link Tables" dialog, make sure you click the "Save Password" option before clicking "OK". This will store the password information in the linked table definition. The linked tables you just created will be brought in with a "TableName1" name.

During this next step, DO NO OPEN YOUR QUERIES. They will break if you don't complete this step first.

Delete your original linked tables from the Access database. Then, remove the "1" from your new linked table names. This will effectively replace your original links with ones that have persistent password information.

I'm not 100% positive, but I believe this will fix your issue. I don't believe there is any way to trigger vba or any kind of macro when running your query from Crystal Reports, so your ODBC credentials need to be stored in the linked table definitions.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The save password option does not come up. This is the problem.
So you do not see this? fmsinc.com/MicrosoftAccess/cloud/linked-tables.jpg
I believe it worked! I'll test it out and then award you the bounty. By the way, do you know how to programmatically set this "save password" field through VBA? I could create the tables through VBA but I'm not sure where to find this field.
I think you would have to include it in the connection string of the linked table when creating a new TableDef, but I've never really done that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.