Ok, what I need to do is to select data cross databases. This should be preferrably done using a single SQL statement using ADO. Does anybody know a way to do this, like creating some sort of workspace in which I can load several tables/databases.
I was thinking about some sort of ADO workspace (don't know if this even exists), or using an Access database and dynamically link the tables from different databases using ADOX. However, I got some code to do that (search the board, some code from Aaron Young), but it keeps giving me errors that the input table cannot be found (although i'm VERY SURE that both databasepath and tablename are spelled correctly). Maybe anyone knows anotherway to get the job done.

This is the code I am using now (wich gives errors)

private Sub AddDatabase(strName as string, strProvider as string, strConnect as string)

Dim Source as ADOX.Table
Dim Target as ADOX.Table
Dim cnn as ADODB.Connection
Dim SourceCatalog as ADOX.Catalog

set cnn = new ADODB.Connection
cnn.Open "Provider=" & strProvider & ";Data Source=""" & strConnect & """;"

set SourceCatalog = new ADOX.Catalog
set SourceCatalog.ActiveConnection = cnn

for Each Source In SourceCatalog.Tables
set Target = new ADOX.Table
set Target.ParentCatalog = MainCatalog
Target.Name = strName & "." & Source.Name
MainCatalog.Tables.Append Target
Target.Properties("Jet OLEDB:Link Datasource") = strConnect
Target.Properties("Jet OLEDB:Remote Table Name") = Source.Name
Target.Properties("Jet OLEDB:Create Link") = true
Target.Properties("Jet OLEDB:Link Provider string") = strConnect

next Source

End Sub



MainCatalog is a catalog object pointing to an empty database, the database I'm trying to link is northwind.mdb, the database opens fine, the linking doesn't work.


Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook