Multiple database problem revisited

[jopaumier]

This is a bit of a combination of two previous threads of mine - referenceing three different databases in a single SQL query and an unrecognized database format.

VB6 using ADO hitting/creating Access databases
MDB1- already exists, primary source of data
MDB2- already exists and has tables of lookup values (in Access 2000)
MDB3- newly created by the program as follows
Set catMDB = New ADOX.Catalog
With catMDB
.Create "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source="C:\MDB3.mdb"
End With
Set catMDB = Nothing

I open a connection to MDB1 as follows:
Set cnInventory = New ADODB.Connection
With cnInventory
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MDB1
End With

I am building a long SQL string to do the following (greatly simplified and using words; I hope it comes across correctly)

SELECT several fields from several tables in MDB1 and a couple fields from two tables in MDB2

INTO PointAnalysisTable IN MDB3

FROM that includes 4 inner joins on tables in MDB1 plus
LEFT JOIN "[C:\Codes].Pollutant" AS poll ON field in table in MDB1=poll.field
INNER JOIN "[C:\Codes].Units" AS unt ON field in table in MDB1=unt.field


The code works great if MDB1 is an Access 2000 or later. But if MDB1 is Access97, then I get the error "Unrecognized database format" for C:\Codes.

I tried adding provider information, for example "Provider=microsoft.jet.oledb.4.0;[C:\Codes].units" but then I got a syntax error in the FROM statement.

In another thread, zemp suggested using ODBC connections and DSN, but I am not familiar with that, and I would have to program the software to do it through code.

Is there a way to have the program open an Access 2000 or Access 97 database opened with Jet 4.0 and not crash?

Thanks, Jim

 

[CCLINT]

If all Dbs are Jet MDBs, you might consider having a main MDB which links the tables from the other MDBs, and then just do all of your queries on the main Mdb. No need then to include paths or connections to the other Mdbs.
The paths for the linked tables in the master Mdb can be refreshed at run time with (Usually) no problems.

This would reduce the number of problems which you are having and help you concentrate on the real work...

 

[JimPaum]

CCLINT,

It sounds like you are suggesting to create the links now between the Jet mdb tables and refresh the links when a user runs the program. I may have mislead you in describing the databases, especially MDB1. MDB1 is a database of the users choosing on his/her system. MDB2 (with all the lookups) is one we will distribute. And the third (is it the master mdb in your reply?) is the one we create every time the program is run (since it can change when the user selects different options). In fact the program will kill this temporary working database every time the user exits the program just so nothing is left hanging around the next time the program is run on that computer. So does all the linking have to be done at runtime? I am searching this site (and will do so on MS's knowledge base) for info on linking tables.

And to be clear, are you referring to a procedure similar to Access' "Get External Data", "Link Tables"?

Another option I have thought of is to copy the two tables I am having trouble with from MDB2 to MDB3 (or will I have a similar problem?). They are small enough that there is minimal impact on the run time.

Thanks for your help
Jim

 

[CCLINT]

Linking tables at run time, even if they didn't exist prior, and even if the tablesholding the links at run time didn't exist before, is really a simple matter.

But, if the tables can be imported, if you are considering this, then just use a SELECT INTO action query, which creates the table and imports the data at the same time (or use an INSERT INTO statement if the table already exists).
Please be aware, that after imorting and deleting data, even complete tables, the db will get bloated, esp. if the imported/deleted data is alot, and you will need to compact the db just that much more often.
With linked tables only the link information is stored.
In your situation, I would go the Linked Table route.

 

[jopaumier]

CCLINT,

Thanks you for your responses. I will investigate table linking. I had started with importing the tables then we switched gears and I decided to use joins (which I think you helped me with in earlier requests).

Jim

 

[CCLINT]

The joins will work, and I thought about mentioning it, but the problem is that with 4 or more mdbs the linking information (paths, db name AND possibly passwords) can get out of hand and start causing headaches when you need to do this every where each time you pull data, where-as with links, you only need to re-link them once and in one proceedure, and it's done with. JET will then be keeping track of it all for each connection and query, and not you - that's the main difference.

 

[jopaumier]

CCLINT,

You are so right about the joins -lost in a maze of parentheses and just making sure the right parts are being joined. I appreciate ALL the help you have provided over the past few months while I struggle this aspect of VB.

Jim