TransferSpreadsheet vs. TransferDatabase

S

SusanV

Hi all,

I have 2 pieces of code to import data (posted below). The string variable
strFile gives me the entire path where the imported files reside with the
filename. This works beautifully using TransferSpreadsheet, but not with the
TransferDatabase - it seems to want the path separate from the filename. To
be a bit more clear, if I hardcode the path and filename like this it works
fine:

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"

The error I get when using the strFile variable as shown in the PLANS: code
below is "C:\Temp\GenericPlans.dbf is not a valid path."


How can I grab the info from the user input for this? It works wonderfully
with Transfer Spreadsheet and I just can't seem to get this right for
TransferDatabase. I have to have the flexibility, as I know I can't count on
users to always save the dbf in the same place - they receive it via email -
and I also have no control over the filename, but know it will always be
DBase III.

Perhaps some sort of String manipulation to break apart the path and
filename?

Thanks to any and all, code below,

SusanV


''' code start ''''


PUMPS:
MsgBox "Select the file containing the data for the " & tblName
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select Excel file for PUMPS")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If table exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If
DoCmd.TransferSpreadsheet acImport, , "tblTemp", strFile, True




PLANS:
msg = "Do you have a new dbf file for PMO Generic Plans to import?"
rsp = MsgBox(msg, vbYesNo, "New Plan DBF?")
If rsp = vbYes Then
MsgBox "Select the DBF file containing the new Generic Plan data."
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select new PMO Plan DBF file ")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblPMOPlans'"))
Then
DoCmd.DeleteObject acTable, "tblPMOPlans"
End If

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"

''' code end ''''
 
S

Steve Schapel

Susan,

The "hard-coded" example you gave us, and the example using the strFile
variable, are not equivalent.

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"
.... does comply with the correct syntax for the TransferDatabase method.
The 'DatabaseName' argument shows the path to the source database, and
the 'Source' argument shows the name of the source database.

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"
.... you are using the strFile variable for both the 'DatabaseName' and
'Source' arguments. If you want to use variables, you wil need to use 2
variables, one for the path, and one for the file name, so your code
will look somewhat like this...
DoCmd.TransferDatabase acImport, "DBase III", strPath, acTable, _
strFileName, "tblPMOPlans"

--
Steve Schapel, Microsoft Access MVP

Hi all,

I have 2 pieces of code to import data (posted below). The string variable
strFile gives me the entire path where the imported files reside with the
filename. This works beautifully using TransferSpreadsheet, but not with the
TransferDatabase - it seems to want the path separate from the filename. To
be a bit more clear, if I hardcode the path and filename like this it works
fine:

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"

The error I get when using the strFile variable as shown in the PLANS: code
below is "C:\Temp\GenericPlans.dbf is not a valid path."


How can I grab the info from the user input for this? It works wonderfully
with Transfer Spreadsheet and I just can't seem to get this right for
TransferDatabase. I have to have the flexibility, as I know I can't count on
users to always save the dbf in the same place - they receive it via email -
and I also have no control over the filename, but know it will always be
DBase III.

Perhaps some sort of String manipulation to break apart the path and
filename?

Thanks to any and all, code below,

SusanV


''' code start ''''


PUMPS:
MsgBox "Select the file containing the data for the " & tblName
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select Excel file for PUMPS")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If table exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If
DoCmd.TransferSpreadsheet acImport, , "tblTemp", strFile, True




PLANS:
msg = "Do you have a new dbf file for PMO Generic Plans to import?"
rsp = MsgBox(msg, vbYesNo, "New Plan DBF?")
If rsp = vbYes Then
MsgBox "Select the DBF file containing the new Generic Plan data."
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select new PMO Plan DBF file ")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblPMOPlans'"))
Then
DoCmd.DeleteObject acTable, "tblPMOPlans"
End If

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"

''' code end ''''
Click to expand...
 
S

SusanV

Steve,

Thanks for your response - although I'm still a bit confused. The first one,
you state:
DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"
... does comply with the correct syntax for the TransferDatabase method.
The 'DatabaseName' argument shows the path to the source database, and the
'Source' argument shows the name of the source database.
Click to expand...

That's what I have above - the path is C:\Test, the dbase filename is
GenericPlans.dbf.
strFile returns the entire path: C:\Test\GenericPlans.dbf

The second one I know is goofy - one of many failed attempts to get the
variable to work.
So, I guess I really have a 2 part question:

1. What would the correct syntax be, using variables strPath and strFile?
2. How do I get those 2 separate variables from the user input box I'm using
to locate the file?

Thanks again,

Susan



Steve Schapel said:
Susan,

The "hard-coded" example you gave us, and the example using the strFile
variable, are not equivalent.

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"
... does comply with the correct syntax for the TransferDatabase method.
The 'DatabaseName' argument shows the path to the source database, and the
'Source' argument shows the name of the source database.

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"
... you are using the strFile variable for both the 'DatabaseName' and
'Source' arguments. If you want to use variables, you wil need to use 2
variables, one for the path, and one for the file name, so your code will
look somewhat like this...
DoCmd.TransferDatabase acImport, "DBase III", strPath, acTable, _
strFileName, "tblPMOPlans"

--
Steve Schapel, Microsoft Access MVP

Hi all,

I have 2 pieces of code to import data (posted below). The string
variable strFile gives me the entire path where the imported files reside
with the filename. This works beautifully using TransferSpreadsheet, but
not with the TransferDatabase - it seems to want the path separate from
the filename. To be a bit more clear, if I hardcode the path and filename
like this it works fine:

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"

The error I get when using the strFile variable as shown in the PLANS:
code below is "C:\Temp\GenericPlans.dbf is not a valid path."


How can I grab the info from the user input for this? It works
wonderfully with Transfer Spreadsheet and I just can't seem to get this
right for TransferDatabase. I have to have the flexibility, as I know I
can't count on users to always save the dbf in the same place - they
receive it via email - and I also have no control over the filename, but
know it will always be DBase III.

Perhaps some sort of String manipulation to break apart the path and
filename?

Thanks to any and all, code below,

SusanV


''' code start ''''


PUMPS:
MsgBox "Select the file containing the data for the " & tblName
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select Excel file for PUMPS")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If table exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If
DoCmd.TransferSpreadsheet acImport, , "tblTemp", strFile, True




PLANS:
msg = "Do you have a new dbf file for PMO Generic Plans to import?"
rsp = MsgBox(msg, vbYesNo, "New Plan DBF?")
If rsp = vbYes Then
MsgBox "Select the DBF file containing the new Generic Plan data."
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select new PMO Plan DBF file ")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblPMOPlans'"))
Then
DoCmd.DeleteObject acTable, "tblPMOPlans"
End If

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"

''' code end ''''
Click to expand...
Click to expand...
 
D

David Lloyd

Susan:

To build on Steve's recommendation, you could take your strFile variable and
break it into a filename and path. For example,

Dim strFileName As String
Dim strPath As String

strFileName = Right(strFile, Len(strFile) - InStrRev(strFile, "\"))
strPath = Left(strFile, InStrRev(strFile, "\"))

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi all,

I have 2 pieces of code to import data (posted below). The string variable
strFile gives me the entire path where the imported files reside with the
filename. This works beautifully using TransferSpreadsheet, but not with the
TransferDatabase - it seems to want the path separate from the filename. To
be a bit more clear, if I hardcode the path and filename like this it works
fine:

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"

The error I get when using the strFile variable as shown in the PLANS: code
below is "C:\Temp\GenericPlans.dbf is not a valid path."


How can I grab the info from the user input for this? It works wonderfully
with Transfer Spreadsheet and I just can't seem to get this right for
TransferDatabase. I have to have the flexibility, as I know I can't count on
users to always save the dbf in the same place - they receive it via email -
and I also have no control over the filename, but know it will always be
DBase III.

Perhaps some sort of String manipulation to break apart the path and
filename?

Thanks to any and all, code below,

SusanV


''' code start ''''


PUMPS:
MsgBox "Select the file containing the data for the " & tblName
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select Excel file for PUMPS")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If table exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If
DoCmd.TransferSpreadsheet acImport, , "tblTemp", strFile, True




PLANS:
msg = "Do you have a new dbf file for PMO Generic Plans to import?"
rsp = MsgBox(msg, vbYesNo, "New Plan DBF?")
If rsp = vbYes Then
MsgBox "Select the DBF file containing the new Generic Plan data."
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select new PMO Plan DBF file ")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblPMOPlans'"))
Then
DoCmd.DeleteObject acTable, "tblPMOPlans"
End If

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"

''' code end ''''
 
S

Steve Schapel

As mentioned by David, you need to extract the path and file from the
strFile. Then, as I mentioned, the syntax for the code will be like this...
DoCmd.TransferDatabase acImport, "DBase III", strPath, acTable, _
strFileName, "tblPMOPlans"
 
S

SusanV

THANK you David - that looks perfect!!

<note to self: Hit the string manipulation chapters hard this weekend>


David Lloyd said:
Susan:

To build on Steve's recommendation, you could take your strFile variable
and
break it into a filename and path. For example,

Dim strFileName As String
Dim strPath As String

strFileName = Right(strFile, Len(strFile) - InStrRev(strFile, "\"))
strPath = Left(strFile, InStrRev(strFile, "\"))

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or
warranties.


Hi all,

I have 2 pieces of code to import data (posted below). The string variable
strFile gives me the entire path where the imported files reside with the
filename. This works beautifully using TransferSpreadsheet, but not with
the
TransferDatabase - it seems to want the path separate from the filename.
To
be a bit more clear, if I hardcode the path and filename like this it
works
fine:

DoCmd.TransferDatabase acImport, "DBase III", "C:\TEST\", acTable, _
"GenericPlans.dbf", "tblPMOPlans"

The error I get when using the strFile variable as shown in the PLANS:
code
below is "C:\Temp\GenericPlans.dbf is not a valid path."


How can I grab the info from the user input for this? It works wonderfully
with Transfer Spreadsheet and I just can't seem to get this right for
TransferDatabase. I have to have the flexibility, as I know I can't count
on
users to always save the dbf in the same place - they receive it via
email -
and I also have no control over the filename, but know it will always be
DBase III.

Perhaps some sort of String manipulation to break apart the path and
filename?

Thanks to any and all, code below,

SusanV


''' code start ''''


PUMPS:
MsgBox "Select the file containing the data for the " & tblName
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select Excel file for PUMPS")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If table exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblTemp'"))
Then
DoCmd.DeleteObject acTable, "tblTemp"
End If
DoCmd.TransferSpreadsheet acImport, , "tblTemp", strFile, True




PLANS:
msg = "Do you have a new dbf file for PMO Generic Plans to import?"
rsp = MsgBox(msg, vbYesNo, "New Plan DBF?")
If rsp = vbYes Then
MsgBox "Select the DBF file containing the new Generic Plan data."
strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select new PMO Plan DBF file ")
If strFile = "" Then
MsgBox "User Cancelled Operation"
Exit Function
End If
'If exists, delete before proceeding
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblPMOPlans'"))
Then
DoCmd.DeleteObject acTable, "tblPMOPlans"
End If

DoCmd.TransferDatabase acImport, "DBase III", strFile, acTable, _
strFile, "tblPMOPlans"

''' code end ''''
Click to expand...
 
S

SusanV

Thanks again Steve, that's exactly what I did, and it's working perfectly.

You guys are the best!

;-D

Susan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DoCmd.TransferDatabase acImport - an Excel DBF 2
Export an Access Table as an dbase III file 8
TransferDatabase to a variable name 6
Importing EXCEL Spreadsheet into ACCESS using TransferSpreadsheet method 4
TransferDataBase won't work 3
db.execute strSql 0
TransferDatabase problem 6
Importing EXCEL Spreadsheets into Access -- Macro or VBA? 1

Top