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
slisse@planetinternet.be
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
Iouri
October 8th, 2001, 08:08 AM
Tom, try this code to link/delete links tables
'ref ADO and ADOX
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim cnAccess As New ADODB.Connection
If optNoRefresh.Value = False Then
'Executing the following line will refresh the link and the table will be Updateable.
tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<servername>;database=pubs;uid=sa;pwd=;"
End If
rs!State = "WA"
rs.Update 'The error occurs here if the link is not refreshed.
MsgBox "Recordset updated"
rs.Close
Set rs = Nothing
End Sub
Iouri Boutchkine
iouri@hotsheet.com
Cakkie
October 8th, 2001, 08:49 AM
This is the code I got from the post from Aaron, and it doesn't work. I'm just linking to an Access database, so no need for SQL server. Anyway, when I replace the Link provider string to and OLEDB connection string, I get a simple error saying
Unable to find installable ISAM
When I replace the connectionstring with and DNS-Less/ODBC connection string, I get another error, saying
You can't use ODBC to import from, export to, or linking an external Microsoft Jet- or ISAM-databasetable to your database"
Any ideas?
Tom Cannaerts
slisse@planetinternet.be
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
The DLL for an installable ISAM file could not be found. This file is required for linking external tables
(other than ODBC or Microsoft Jet database tables). The locations for all ISAM drivers are maintained in the
Windows Registry. These entries are created automatically when you install your application. If you change
the location of these drivers, you need to correct your application Setup program to reflect this change and
make the correct entries in the Registry.
Possible causes:
An entry in the Registry is not valid. For example, this error occurs if you're using a Paradox external
database and the Paradox entry points to a nonexistent directory or driver. Exit the application, correct the
Windows Registry, and try the operation again.
One of the entries in the Registry points to a network drive and that network is not connected. Make sure
the network is available, and then try the operation again.
Iouri Boutchkine
iouri@hotsheet.com
Cakkie
October 8th, 2001, 09:26 AM
Ok, first of all, thanx for the work you did
Second, if we don't believe that, they will probably try to tell us something else...
I reinstalled MDAC, giving the same error.
I tried running the same program on another computer, giving the same error.
I tried running Access, and linked the table manually, it succeeded.
So now please will someone explain me why Access can do it, but I can't, or do we just need to classify this in one of the :M$ f*ck#n@ X-files (I apolologise for the language). Man, if I ever get my hands on :Bill %£^#@"é"#@ Gates...
Tom Cannaerts
slisse@planetinternet.be
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
Iouri
October 8th, 2001, 10:24 AM
What do you want? It is M$. If all their products work as they supposed to, nobody will need us. Everybody can read the nice instruction and run the example. It is a favor that we are getting from MS to keep us busy.
Iouri Boutchkine
iouri@hotsheet.com
Cimperiali
October 8th, 2001, 10:27 AM
You got a 6 from SACEIT. I am furious!
(just kidding...)
You sure you have last jet drivers on your machine?
Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Michael
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.
The Rater
Iouri
October 8th, 2001, 10:29 AM
Try to link the table by executing the followiing command
Yes, I'm sure, I even reinstalled MDAC (2.6). It's just that the code 'should' work, but it doesn't...
Tom Cannaerts
slisse@planetinternet.be
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
Cakkie
October 8th, 2001, 10:46 AM
Heard this one before?
You call it a bug? We call it a feature!
Tom Cannaerts
slisse@planetinternet.be
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
Iouri
October 8th, 2001, 10:49 AM
Tom,
Just now I ran this proc on my PC. It works
Public Sub LinkTable(strDatabase As String)
'==============================================================================
'add ref to Acces 8.0
Dim mobjAccess As Object
On Error GoTo ErrorHandler
Set mobjAccess = New Access.Application
' Open the database
mobjAccess.OpenCurrentDatabase strDatabase
' Print report
' using the DoCmd object
mobjAccess.DoCmd.TransferDatabase acLink, "Microsoft Access", strDatabase, acTable, "TableToLink", "LinkedTable"
Set mobjAccess = Nothing
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then Resume Next 'report was cancelled or not found
End Sub
Iouri Boutchkine
iouri@hotsheet.com
Iouri
October 8th, 2001, 10:52 AM
Don't pay attention where it is said report. I copied the pieces of codes from my different procedures and was lazy to fix it
Iouri Boutchkine
iouri@hotsheet.com
Cakkie
October 8th, 2001, 03:39 PM
Ok guys, I finally got it working, and it was a really really really stupid thing. The thing i was doing (without even knowing) was that I was trying to link a query. Just for the record, the code that does work (using ADOX, no way I'm going to use Access for this (sorry Iouri)).
set Target = new ADOX.Table
Target.Name = "SomeLinkedTable"
set Target.ParentCatalog = MainCatalog
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
Iouri
October 9th, 2001, 07:42 AM
I tried it. Works great!
Iouri Boutchkine
iouri@hotsheet.com
Cimperiali
October 10th, 2001, 04:01 AM
Excelent!
Now, stop being so smart, or I will not be able to stop rating you instead of my best friends...
;-)
Have a nice day, you indisciplinate young Guru...
Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Michael
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.
The Rater
codeguru.com
Copyright Internet.com Inc., All Rights Reserved.