Link table

[QUINABA]

Good Friday to all!

Is it possible to link a table from database "A" to "B" while being in database "C" utilizing a macro? I usually run a bunch of adhoc reports using the same tables and normally would have to create duplicate mdb's to be able to multi-task.

Thanks.

 

[Norie]

Yes you can use the OpenDatabase method of a Workspace object to set references to the other databases.

Straight from Help:

Sub OpenAnother()
	Dim wsp As Workspace
	Dim dbs As Database, dbsAnother As Database
	Dim tdf As TableDef
	
	' Return reference to current database.
	Set dbs = CurrentDb
	' Return reference to default workspace.
	Set wsp = DBEngine.Workspaces(0)
	' Return reference to Another.mdb.
	Set dbsAnother = wsp.OpenDatabase("Another.mdb")
	' Enumerate all TableDef objects in each database.
	Debug.Print dbs.Name & ":"
	For Each tdf in dbs.TableDefs

Debug.Print tdf.Name
	Next tdf
	Debug.Print
	Debug.Print dbsAnother.Name & ":"
	For Each tdf in dbsAnother.TableDefs
		Debug.Print tdf.Name
	Next tdf
	Set dbs = Nothing
	Set dbsAnother = Nothing
End Sub

 

[QUINABA]

Cool!! Thanks Norie! Have a great July 4th weekend!

 

[QUINABA]

Replied too soon I'm a littly confused on how to use the code for my application could you kindly explain?

Thanks

 

[QUINABA]

Bump...

 

[QUINABA]

Bump...

 

[bat17]

If by "Macro" you mean a standard Access Macro then the answer is probably No, if you mean VBA then you can do it.

Here is another section from help. I have not worked through it all but it does demonstrate how to link tables from a remote source.

This example uses the Connect and SourceTableName properties to link various external tables to a Microsoft Jet database. The ConnectOutput procedure is required for this procedure to run.

Sub ConnectX()

Dim dbsTemp As Database
Dim strMenu As String
Dim strInput As String

' Open a Microsoft Jet database to which you will link
' a table.
Set dbsTemp = OpenDatabase("DB1.mdb")

' Build menu text.
strMenu = "Enter number for data source:" & vbCr
strMenu = strMenu & _
" 1. Microsoft Jet database" & vbCr
strMenu = strMenu & _
" 2. Microsoft FoxPro 3.0 table" & vbCr
strMenu = strMenu & _
" 3. dBASE table" & vbCr

strMenu = strMenu & _
" 4. Paradox table" & vbCr
strMenu = strMenu & _
" M. (see choices 5-9)"

' Get user's choice.
strInput = InputBox(strMenu)

If UCase(strInput) = "M" Then

' Build menu text.
strMenu = "Enter number for data source:" & vbCr
strMenu = strMenu & _
" 5. Microsoft Excel spreadsheet" & vbCr
strMenu = strMenu & _
" 6. Lotus spreadsheet" & vbCr
strMenu = strMenu & _
" 7. Comma-delimited text (CSV)" & vbCr

strMenu = strMenu & _
" 8. HTML table" & vbCr
strMenu = strMenu & _
" 9. Microsoft Exchange folder"

' Get user's choice.
strInput = InputBox(strMenu)

End If

' Call the ConnectOutput procedure. The third argument
' will be used as the Connect string, and the fourth
' argument will be used as the SourceTableName.
Select Case Val(strInput)
Case 1
ConnectOutput dbsTemp, _
"JetTable", _
";DATABASE=C:\My Documents\Northwind.mdb", _

"Employees"
Case 2
ConnectOutput dbsTemp, _
"FoxProTable", _
"FoxPro 3.0;DATABASE=C:\FoxPro30\Samples", _
"Q1Sales"
Case 3
ConnectOutput dbsTemp, _
"dBASETable", _
"dBase IV;DATABASE=C:\dBASE\Samples", _
"Accounts"
Case 4
ConnectOutput dbsTemp, _
"ParadoxTable", _
"Paradox 3.X;DATABASE=C:\Paradox\Samples", _
"Accounts"
Case 5
ConnectOutput dbsTemp, _

"ExcelTable", _
"Excel 5.0;" & _
"DATABASE=C:\Excel\Samples\Q1Sales.xls", _
"January Sales"
Case 6
ConnectOutput dbsTemp, _
"LotusTable", _
"Lotus WK3;" & _
"DATABASE=C:\Lotus\Samples\Sales.xls", _
"THIRDQTR"
Case 7
ConnectOutput dbsTemp, _
"CSVTable", _
"Text;DATABASE=C:\Samples", _
"Sample.txt"
Case 8
ConnectOutput dbsTemp, _
"HTMLTable", _

"HTML Import;DATABASE=http://" & _
"www.server1.com/samples/page1.html", _
"Q1SalesData"
Case 9
ConnectOutput dbsTemp, _
"ExchangeTable", _
"Exchange 4.0;MAPILEVEL=" & _
"Mailbox - Michelle Wortman (Exchange)" & _
"|People\Important;", _
"Jerry Wheeler"
End Select

dbsTemp.Close

End Sub

Sub ConnectOutput(dbsTemp As Database, _
strTable As String, strConnect As String, _

strSourceTable As String)

Dim tdfLinked As TableDef
Dim rstLinked As Recordset
Dim intTemp As Integer

' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
Set tdfLinked = dbsTemp.CreateTableDef(strTable)

tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked

Set rstLinked = dbsTemp.OpenRecordset(strTable)

Debug.Print "Data from linked table:"

' Display the first three records of the linked table.
intTemp = 1
With rstLinked
Do While Not .EOF And intTemp <= 3
Debug.Print , .Fields(0), .Fields(1)
intTemp = intTemp + 1
.MoveNext
Loop
If Not .EOF Then Debug.Print , "[additional records]"
.Close
End With

' Delete the linked table because this is a demonstration.
dbsTemp.TableDefs.Delete strTable

End Sub

[/quote]

 

[QUINABA]

Thanks Bat17, I appreciate your help! I'm terribly lost though... I can't seem to make the code work. I only want to link a table from First.mdb to Second.mdb while being in Third.mdb. It seems so simple but the solution looks complicated. Any assistance will be greatly appreciated.

 

[mdmilner]

This should be a little more clear.
Tables can be explictly referenced by the pathname to the mdb (access database). This is not linking, but instead, opening a recordset so you can manipulate a table.

Public Function SubmitAllEventsRS()
Dim dbs, dbsdump As DAO.Database
Dim ws As Workspace
Dim strSQL, strDB, tblName As String
Dim rsl, rs, rsd As DAO.Recordset
Dim x As Long

Set dbs = CurrentDb

strDB = "path_to_other_mdb"
 ' example
 ' C:\Documents and Settings\username\My Documents\be_SiteVisit.mdb
 ' 
Set dbsdump = DBEngine.Workspaces(0).OpenDatabase(strDB)

Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Set rsd = dbsdump.OpenRecordset(strSQL, dbOpenDynaset)

Set rs = Nothing
Set rsd = Nothing
Set dbsdump = Nothing
Set dbs = Nothing
End Function

A SQL string to insert into another database table would end up looking like:

INSERT INTO [path_to_file].[tablename] (fieldnames_here) SELECT fieldnames_here FROM sourcetable

It's really going to depend on what you need to do with the tables. Again though, this is not linking, it's opening and manipulating tables in other databases via VBA code. Naturally, you can use SELECT queries instead of the INSERT example above.

Mike

 

[bat17]

try this cut-down version

Sub linkTable()
Dim strConnect As String
Dim dbsTemp As Database
Dim tdfLinked As TableDef
Dim strSourceTable As String
Dim strSourceDB As String
Dim strTargetDB As String
Dim strTargetTable As String

strSourceDB = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" 'path to source DB
strSourceTable = "Employees" ' table to link from
strTargetDB = "C:\DB1.mdb" ' Database to put link in
strTargetTable = "MyLinkedTable" ' name for linked table

Set dbsTemp = OpenDatabase(strTargetDB)
strConnect = ";DATABASE=" & strSourceDB
Set tdfLinked = dbsTemp.CreateTableDef(strTargetTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked

Set tdfLinked = Nothing
dbsTemp.Close
Set dbsTemp = Nothing
End Sub

Peter