Link between 2 databases

  • Thread starter Thread starter Peter Alt
  • Start date Start date
P

Peter Alt

Hi,

I have to do queries between 2 databases and with dao/vb6 I've linked all
tables of database 1 into database2 as followed and can than do the queries
in database 2. Now I'm beginning to transfer the project in vb.net and I
think db-functions should be transfered in ado.net. But how can I implement
this kind of database/table linking in ado.net? For ado it seems that adox
is the direction, but for ado.net (is there a adox.net?)

Thanks Peter


Dim dbBig As DAO.Database, dbUser As DAO.Database, tabUser As DAO.TableDef,
N&

Set dbBig = OpenDatabase(dbBigFile, False, True, ";pwd=" & PasswordDB)
Set dbUser = OpenDatabase(dbUserFile, True, False, ";pwd=" & PasswordDB)

For N = 0 To dbBig.TableDefs.Count - 1
Set tabUser = dbUser.CreateTableDef(dbBig.TableDefs(N).Name, 0)
tabUser.Connect = ";DATABASE=" & dbBigFile & ";pwd=" & PasswordDB
tabUser.SourceTableName = dbBig.TableDefs(N).Name
dbUser.TableDefs.Append tabUser
Next
 
¤ Hi,
¤
¤ I have to do queries between 2 databases and with dao/vb6 I've linked all
¤ tables of database 1 into database2 as followed and can than do the queries
¤ in database 2. Now I'm beginning to transfer the project in vb.net and I
¤ think db-functions should be transfered in ado.net. But how can I implement
¤ this kind of database/table linking in ado.net? For ado it seems that adox
¤ is the direction, but for ado.net (is there a adox.net?)
¤
¤ Thanks Peter
¤
¤
¤ Dim dbBig As DAO.Database, dbUser As DAO.Database, tabUser As DAO.TableDef,
¤ N&
¤
¤ Set dbBig = OpenDatabase(dbBigFile, False, True, ";pwd=" & PasswordDB)
¤ Set dbUser = OpenDatabase(dbUserFile, True, False, ";pwd=" & PasswordDB)
¤
¤ For N = 0 To dbBig.TableDefs.Count - 1
¤ Set tabUser = dbUser.CreateTableDef(dbBig.TableDefs(N).Name, 0)
¤ tabUser.Connect = ";DATABASE=" & dbBigFile & ";pwd=" & PasswordDB
¤ tabUser.SourceTableName = dbBig.TableDefs(N).Name
¤ dbUser.TableDefs.Append tabUser
¤ Next
¤

You can use either DAO or ADOX but there is no native method in ADO.NET. I don't believe it can be
accomplished via Jet SQL, but if so it's undocumented.

http://msdn.microsoft.com/library/d...n-us/odeopg/html/deovrcreatinglinkedtable.asp

You can also reference tables from another Access database directly via SQL. The below example joins
a local table to a table in an external Access database:

SELECT Table1.[record ID], Table1.[changed], Table1.Field3, Table11.product FROM Table1 INNER JOIN
[MS Access;DATABASE=E:\My Documents\AccessDB.mdb;].[Table11] ON (Table1.[changed] =
Table11.[changed]) AND (Table1.[record ID] = Table11.[record ID])


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top