VBA to import from another database

[LB79]

Hello,

I am having a problem creating an automatic transfer from an external database to my own.
I have previously created this to import an Excel table but when I amend the code for an MDB it debugs.

So far I have

DoCmd.TransferDatabase acImport, "Microsoft Access", acTable, "TRA_tblPOData", "Me!TRA_txtPath", True

Me!TRA_txtPath is the path of this database I want to import a table from.

The debug error is “Run time error 13 – Type Mis Match”.

Can anyone see where I am going wrong?

Thanks for any help

 

[honda882000]

Try not surrounding Me!TRA_txtPath is quotes. Since Me!TRA_txtPath is a string (assuming this is a text/combo box in a form), you do not have to use "". So try replacing your statement with:

DoCmd.TransferDatabase acImport, "Microsoft Access", acTable, "TRA_tblPOData", Me!TRA_txtPath, True

 

[LB79]

Hi and thanks for your advice - I think that helps as its now looking for the file, however, for some reason its looking for a file called 0 instead of the file selected from explorer.

This is what I have so far. The first part of the code allows the user to select a file. The second part of the code is to import the data. Is there anything obvious that I am doing wrong?

Private Sub TRA_cmdOpenPO_Click()
Me.TRA_txtPath.BackColor = 16777215
Me.Repaint
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Me.TRA_txtPath = ""
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.Title = "Please select a file to import"
.Filters.Clear
.Filters.Add "Access Database", "*.mdb"
If .Show = True Then
For Each varFile In .SelectedItems
Me.TRA_txtPath = varFile
Next
Else
End If
End With
End Sub

Private Sub TRA_cmdPOImport_Click()
If IsNull(Me!TRA_txtPath) Then
Me.TRA_txtPath.BackColor = 12566527
Me.Repaint
MsgBox "Please select a file to import.", , "Import"
DoCmd.Hourglass False
Exit Sub
Else
On Error GoTo ErrorMessage
DoCmd.SetWarnings False
DoCmd.Hourglass True
Me.TRA_txtPath.BackColor = 13565951
Me.Repaint
DoCmd.OpenQuery "TRA_qry1_DeletePO"
DoCmd.TransferDatabase acImport, "Microsoft Access", acTable, TRA_tblPOData, Me!TRA_txtPath, True
Me.TRA_txtPath.BackColor = 14548957
Me.Repaint
DoCmd.Hourglass False
End If
DoCmd.SetWarnings True
MsgBox "The table has been imported.", , "Done"
Exit Sub
ErrorMessage:
MsgBox "An error has occured.", , "Error"
DoCmd.Hourglass False
DoCmd.SetWarnings True
End Sub

 

[honda882000]

Whenever you run the first part of the code, did you check that it sets the Me.TRA_txtPath text box to the path string?

 

[LB79]

In my other code that imports Excel I havent set Me.TRA_txtPath as path string, and that works really well... Is this needed because its now an Access table im trying to improt?

Thanks

 

[honda882000]

It probably won't make a difference, but try replacing your first sub-procedure with this, and see if it works:

Private Sub TRA_cmdOpenPO_Click()
    Me.TRA_txtPath.BackColor = 16777215
    Me.Repaint
    
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    
    Me.TRA_txtPath = ""
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    With fDialog
        .AllowMultiSelect = False
        .Title = "Please select a file to import"
        .Filters.Clear
        .Filters.Add "Access Database", "*.mdb"
        .Show
        Me.TRA_txtPath = .SelectedItems(1)
    End With
    
End Sub

 

[honda882000]

I think that your parameters for TransferDatabase are actually flipped around. Check out this http://msdn.microsoft.com/en-us/library/aa164086(office.10).aspx and make sure that you have them in the right order. I can see that at least your 'acTable' is in the wrong place. Hope that helps.

 

[LB79]

Thanks - I now have the code importing!! However, for some reason instead of improting to the table I want, it creates a new table, with the same name but a number at the end. Can you advise how to get around this?
My vb is

DoCmd.TransferDatabase acImport, "Microsoft Access", Me!TRA_txtPath, acTable, "PO_SEARCH_RESULT", "TRA_tblPOData", False

Thanks