how to get the path for the linked tables ?

[kunmun23]

I have a VBA form with an "Export Table" button on it for exporting the currently linked tables to another database. The user choses the path for the destination database using a Browse button.

The problem is when I use either 'Copyobject' or 'Transferdatabase acExport' commands VBA only transfers the links not the table. So I did it in 3 steps

1) Import the linked tables into the current database.
2) Export these imported tables to the destination database.
3) Delete the imported tables from the current database.

Now the problem is in the first step. How do I find the path of the currently linked tables so I can do a 'TransferDatabase acImport' ? Currently I have the path hard-coded in the code which I don't like.

Also is there a better way to do what I am trying to do above i.e. export tables ?

Please help..this is urgent and I already have wasted a lot of time experimenting and trying to figure it out.

Thanks in advance !!

 

[CajunCenturion]

One way to get this information is from ADOX. You'll need to include a reference to the ADO Extension for DDL and security, and then you can use some variant of the following code:

Dim DB_Cat As New ADOX.Catalog
Dim DB_Table As New ADOX.Table
Dim DB_Prop As Object
   
DB_Cat.ActiveConnection = CurrentProject.Connection
For Each DB_Table In DB_Cat.Tables
   Set DB_Prop = DB_Table.Properties.Item("Jet OLEDB:Link Datasource")
   MsgBox DB_Table.Name & " is in mdb " & DB_Prop.Value
Next
      
Set DB_Prop = Nothing
Set DB_Table = Nothing
Set DB_Cat = Nothing

Some of the factors that you need to watch out for. I cannot guarantee that the name of the property "Jet OLEDB:Link Datasource" will be the same, nor can I guarantee that this property will exist for not linked tables. I do know that you may find the Value to be an Empty String ("&quot if the table is not linked.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[LittleSmudge]

Adapt this module to your needs

Function GetLinkedConnectStrings()
' # # # # # # # # # # # # # # # # # # # # # #
' This proc prints to the debug window
' the full pathname of the linked tables
' # # # # # # # # # # # # # # # # # # # # # #
On Error GoTo Err_GetLinkedConnectStrings

Dim db As Database
Dim tbf As TableDef

DoCmd.Hourglass True
Set db = CurrentDb
  
  For Each tbf In db.TableDefs
    DoEvents
    If Not Left(tbf.Name, 4) = "MSys" Then
      'ignore system tables
      Debug.Print tbf.Name & "; " & tbf.Connect
    End If
  Next tbf

Exit_GetLinkedConnectStrings:
On Error Resume Next
DoCmd.Hourglass False
db.Close
Set db = Nothing

Exit Function

Err_GetLinkedConnectStrings:
  Select Case Err.Number
  Case 0    'insert any Errors you wish to ignore here
    Resume Next
  Case 3011 'object not found
    Resume Next
  Case 3045 'database already in use
    Beep
    MsgBox "The database is in use by someone else. " _
         & "Go and evict them and try again.", , "You are not alone."
    Resume Exit_GetLinkedConnectStrings
  Case Else 'All other errors will trap
    Beep
    MsgBox "Error deleting tables.@" & Err.Number & "; " & Err.Description
    Resume Exit_GetLinkedConnectStrings
  End Select
  Resume 0  'FOR TROUBLESHOOTING
End Function

and then remember that step two will RENAME the table imported because it won't allow a linked table and an imported table to have the same name.

'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[kunmun23]

thanks LittleSmudge tbf.Connect is exactly what I needed...Cajun ur solution was good too..but I don't want to add any references that would cause dependency problems when I distribute the application. Thanks anyways !!