Type mismatch on DataBaseTransfer routine

[LarryE]

I have a Public Function called from a custom Ribbon Button that opens
FileDialog to select a file and then uses that file as a table import source
file to import a table into my open file. I am having trouble passing my
FileDialog selection to the DoCmd.DatabaseTransfer sourcefile parameter.
Whats wierd is that the 'For Each In.SelectedItems' and the 'With' staements
I use each require variables to be defined as Variant and so does
DoCmd.DatabaseTransfer sourcefile parameter. But when I use a Variant as the
sourcefile parameter, I get a Type Mismatch error. This dosen't make sense.
Here is my code:

Dim RestoreDialog As Office.filedialog
Dim ImportFile As Variant (this is my variable and required to be
Variant)
Set RestoreDialog = Application.filedialog(msoFileDialogFilePicker)
With RestoreDialog
..Title = "Select Backup File To Restore And Click 'OK'"
..Filters.Add "Access Databases", "*.accdb"
If .Show = True Then
For Each ImportFile In .SelectedItems
DoCmd.DeleteObject acTable, "Audit Program"
DoCmd.TransferDatabase acImport, "Microsoft Access", ImportFile,
"Audit Program", "Audit Program", 0, 0
End If
End With

Does anyone know what is wrong here? Should I be converting the Variant to
String somehow. But then the For and With statements won't work. When I look
at Microsoft website it states that the DoCmd.TransferDatabase sourcefile
parameter must be defined as a Variant data type. But is it really supposed
to be a String? GGGGGGGGGGrrrrrrrrrrrrr. I tried using:
"C:\ORCAS Backup" & Mid$(ImportFile, InStrRev(ImportFile, "\"))
but got the same thing.

Thank you in advance for any ideas. I thought I had this stuff. I guess no
one really does.

 

[Douglas J. Steele]

The correct syntax for TransferDatabse syntax is

DoCmd.TransferDatabase [transfertype], databasetype, databasename[,
objecttype], source, destination[, structureonly][, saveloginid]

You're missing the objecttype (acForm, acMacro, acModule, acQuery, acReport
or acTable)

The default is acTable, but you still need to put the comma to use the
default.

 

[LarryE]

Sheeeesh...missing a basic parameter...I belong back in Access Kindergarten.
Glad this is an anonamous forum. Thanks Doug
--
Larry

The correct syntax for TransferDatabse syntax is

DoCmd.TransferDatabase [transfertype], databasetype, databasename[,
objecttype], source, destination[, structureonly][, saveloginid]

You're missing the objecttype (acForm, acMacro, acModule, acQuery, acReport
or acTable)

The default is acTable, but you still need to put the comma to use the
default.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a Public Function called from a custom Ribbon Button that opens
FileDialog to select a file and then uses that file as a table import
source
file to import a table into my open file. I am having trouble passing my
FileDialog selection to the DoCmd.DatabaseTransfer sourcefile parameter.
Whats wierd is that the 'For Each In.SelectedItems' and the 'With'
staements
I use each require variables to be defined as Variant and so does
DoCmd.DatabaseTransfer sourcefile parameter. But when I use a Variant as
the
sourcefile parameter, I get a Type Mismatch error. This dosen't make
sense.
Here is my code:

Dim RestoreDialog As Office.filedialog
Dim ImportFile As Variant (this is my variable and required to be
Variant)
Set RestoreDialog = Application.filedialog(msoFileDialogFilePicker)
With RestoreDialog
.Title = "Select Backup File To Restore And Click 'OK'"
.Filters.Add "Access Databases", "*.accdb"
If .Show = True Then
For Each ImportFile In .SelectedItems
DoCmd.DeleteObject acTable, "Audit Program"
DoCmd.TransferDatabase acImport, "Microsoft Access", ImportFile,
"Audit Program", "Audit Program", 0, 0
End If
End With

Does anyone know what is wrong here? Should I be converting the Variant to
String somehow. But then the For and With statements won't work. When I
look
at Microsoft website it states that the DoCmd.TransferDatabase sourcefile
parameter must be defined as a Variant data type. But is it really
supposed
to be a String? GGGGGGGGGGrrrrrrrrrrrrr. I tried using:
"C:\ORCAS Backup" & Mid$(ImportFile, InStrRev(ImportFile, "\"))
but got the same thing.

Thank you in advance for any ideas. I thought I had this stuff. I guess no
one really does.

.