DoCmd.TransferDatabase - use a variable for source

[bytes access nubie]

Attached is code to link tables in databases that are in a folder. My variable for the source in the DoCmd.transferD atabase code Dir(strPath & rst!FolderName) is not working right. I want to loop through the db names to link the tables.

each db has only 1 table which is named the same as the db. We have a macro that pulls all the db names from the folder into a table called tblDirectory into the field FolderName (that's a bit misleading, I know). I have very little VB knowledge. I'd appreciate any detailed instructions.

Function LinkTable()

Dim rst As DAO.Recordset
Dim strPath As String
Dim strFile As String

  strPath = "D:\Temp\Testing\"

Set rst = CurrentDb.OpenRecordset("tblDirectory", dbOpenTable)
Do Until rst.EOF
     'ensure exist
       strFile = Dir(strPath & rst!FolderName)
      If Len(Dir(strPath & rst!FolderName)) > 0 Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", Dir(strPath & rst!FolderName), acTable, rst!FolderName, rst!FolderName, False
            End If
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Function

[jforbes]

I didn't see anything glaring obviously wrong with the code, so it would be nice to know how it is not working right. What error are you getting?

[bytes access nubie]

access says, "error 3024, cannot find the file..." and displays the path of my default folder (the documents folder) and the name of the first db in the list. thank you for looking.

[bytes access nubie]

after clicking debug access stops on the line DoCmd.TransferD atabase acLink, "Microsoft Access", Dir("D:\Temp\Te sting\" & rst!FolderName) etc

[jforbes]

I think line 14 should be:

            DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, rst!FolderName, rst!FolderName, False

[bytes access nubie]

unfortunately that did not do it. I get a message that access database engine could not find the object... and lists the first db name in the table. here's the current code:

Function LinkTable()

Dim rst As DAO.Recordset
Dim strPath As String
Dim strFile As String

  strPath = "D:\Temp\Testing\"

Set rst = CurrentDb.OpenRecordset("tblDirectory", dbOpenTable)
Do Until rst.EOF
     'ensure exist
       strFile = Dir(strPath & rst!FolderName)
      If Len(Dir(strPath & rst!FolderName)) > 0 Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, rst!FolderName, rst!FolderName, False
            'DoCmd.TransferDatabase acLink, "Microsoft Access", Dir("D:\Temp\Testing\" & rst!FolderName), acTable, rst!FolderName, rst!FolderName, False
            End If
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Function

[jforbes]

Here is the examples from Microsoft on the

DoCmd.TransferDatabase:
DoCmd.TransferDatabase acImport, "Microsoft Access", _ 
    "C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _ 
    "Corporate Sales for April"

DoCmd.TransferDatabase acLink, "ODBC Database", _ 
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Authors", "dboAuthors"

When they refer to an Access database the full Database Name is listed, including “.MDB”. I have a feeling that may need to be included as well. So maybe Line 14 should be:

            DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName & “.mdb”, acTable, rst!FolderName, rst!FolderName, False

[bytes access nubie]

thanks again. the .accdb is included in the table (see below):

FolderName
AUTH.accdb
MONTHS.accdb
PHASE.accdb
ACCT_LST.accdb

[bytes access nubie]

actually, access tells me the first table name, it says it can't find the folder. so it's having problems finding the actual location

[jforbes]

The picture is getting clearer. I would guess the File is being found since you are getting past line 13 which is testing for it's existence. I should have realized that if that was working, the file extension is included in rst!FolderName.

So since the Extension is in FolderName, it should be stripped out when referring to the tables. You might want to try this for line 14:

DoCmd.TransferDatabase acLink, "Microsoft Access", strPath & rst!FolderName, acTable, Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), Left(rst!FolderName, InStr(rst!FolderName, ".") - 1), False

If it works, it might be best to go back and clean it up by putting table name into a string of it's own.

[bytes access nubie]

wooohooo!! thank you!!! worked!!