ADO Workspace or linking tables...

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

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

Private Sub cmdDeleteLinkedTable_Click()

cat.Tables.Delete "lnkAuthors"
MsgBox "Link Deleted"

End Sub

Private Sub Form_Load()

cmdDeleteLinkedTable.Caption = "Delete Linked Table"
cmdLinkTable.Caption = "Link Table"
optNoRefresh.Caption = "Don't Refresh Link"
optRefresh.Caption = "Refresh Link"

cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb;"
cat.ActiveConnection = cnAccess

End Sub

Private Sub Form_Unload(Cancel As Integer)

'Clean up code
Set cat = Nothing

cnAccess.Close
Set cnAccess = Nothing

End Sub

Private Sub cmdLinkTable_Click()

Dim rs As New ADODB.Recordset

tbl.Name = "lnkAuthors"
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<servername>;database=pubs;uid=sa;pwd=;"
tbl.Properties("Jet OLEDB:Remote Table Name") = "Authors"
tbl.Properties("Jet OLEDB:Create Link") = True

cat.Tables.Append tbl

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.CursorLocation = adUseClient
rs.Open "Select * from lnkAuthors", cnAccess, adOpenStatic, adLockOptimistic

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
[email protected]

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
[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

>Unable to find installable ISAM


http://support.microsoft.com/support.../Q155/6/66.asp

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
[email protected]

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
[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

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
[email protected]

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

Try to link the table by executing the followiing command

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\QMS\data.mdb", acTable, Tables(I), Tables(I)

Iouri Boutchkine
[email protected]

Yes, I'm sure, I even reinstalled MDAC (2.6). It's just that the code 'should' work, but it doesn't...

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

Heard this one before?

You call it a bug? We call it a feature!

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

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
[email protected]

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
[email protected]

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

Target.Properties("Jet OLEDB:Link Datasource") = "Path to database"
Target.Properties("Jet OLEDB:Remote Table Name") = "SomeTable"
Target.Properties("Jet OLEDB:Create Link") = true

MainCatalog.Tables.Append Target




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

I tried it. Works great!

Iouri Boutchkine
[email protected]

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