W
WDR
I am trying to use the DoCmd.TransferSpreadsheet command to import records in
an Excel 2000 spreadsheet file (has one sheet) but it is not working and
would appreciate your expertise. Error msg I'm getting indicates either key
violations, or data types/field sizes not matching, or a PK violation. Thank
You. More details below.
The import process I want to accomplish will actual APPEND the records to an
existing Access 2000 table. The table has no PK and doesn’t need one. I
desire to have a few of the fields indexed but took them off in just trying
to get the import/append to work. Field types my Access table has are Date,
Text, Memo, Yes/No and Number(the number fields used to be Text – all they
store are years). Other variations I’ve attempted are:
- I’ve tried using and not using field names (w/corresponding TRUE/FALSE)
- I’ve tried importing TRUE/FALSE fields into Yes/No Access fields but have
since changed the True/False on the Excel side to “Yâ€/â€N†and therefore
changed those Access field types to Text.
- I’ve tried formatting the Excel columns to Text that correspond to Text on
the Access side. I did this for all data types.
I keep hearing about linking, but in my situation I don’t think that will
work unless the linking can be handled within the process I’ve pasted below
(I don’t want to end-user to have to manually do anything in this process).
I have multi-Excel users emailing a single sheet file with records which I
want to import into a history table in Access. In case you’re wondering, all
the data validation occurs on the Excel side.
I could go on-n-on with additional info but one other note is that I can
import into a new table which is not my desire PLUS when the new table is
created it has an additional field with a data type of Autonumber as the PK.
I do this import via File/GetExternal Data menu.
*****- THIS IS THE IMPORT PROCESS THAT DOES NOT WORK -********
Sub ImportSpreadsheetFiles()
Dim strFile, strFolder, strBackupFolder, strBkupFolderFile,
strFolderFile As String
strFolder = "C:\Documents and Settings\" + Environ("UserName") + "\My
Documents\ImportFilesForAccess\"
strBackupFolder = strFolder & "FilesAfterImport\"
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, 8, "PastJobs", strFolder &
strFile, True
Name strFolder & strFile As strBackupFolder & strFile
strFile = Dir$(strFolder & "*.xls")
Loop
End Sub
an Excel 2000 spreadsheet file (has one sheet) but it is not working and
would appreciate your expertise. Error msg I'm getting indicates either key
violations, or data types/field sizes not matching, or a PK violation. Thank
You. More details below.
The import process I want to accomplish will actual APPEND the records to an
existing Access 2000 table. The table has no PK and doesn’t need one. I
desire to have a few of the fields indexed but took them off in just trying
to get the import/append to work. Field types my Access table has are Date,
Text, Memo, Yes/No and Number(the number fields used to be Text – all they
store are years). Other variations I’ve attempted are:
- I’ve tried using and not using field names (w/corresponding TRUE/FALSE)
- I’ve tried importing TRUE/FALSE fields into Yes/No Access fields but have
since changed the True/False on the Excel side to “Yâ€/â€N†and therefore
changed those Access field types to Text.
- I’ve tried formatting the Excel columns to Text that correspond to Text on
the Access side. I did this for all data types.
I keep hearing about linking, but in my situation I don’t think that will
work unless the linking can be handled within the process I’ve pasted below
(I don’t want to end-user to have to manually do anything in this process).
I have multi-Excel users emailing a single sheet file with records which I
want to import into a history table in Access. In case you’re wondering, all
the data validation occurs on the Excel side.
I could go on-n-on with additional info but one other note is that I can
import into a new table which is not my desire PLUS when the new table is
created it has an additional field with a data type of Autonumber as the PK.
I do this import via File/GetExternal Data menu.
*****- THIS IS THE IMPORT PROCESS THAT DOES NOT WORK -********
Sub ImportSpreadsheetFiles()
Dim strFile, strFolder, strBackupFolder, strBkupFolderFile,
strFolderFile As String
strFolder = "C:\Documents and Settings\" + Environ("UserName") + "\My
Documents\ImportFilesForAccess\"
strBackupFolder = strFolder & "FilesAfterImport\"
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0
DoCmd.TransferSpreadsheet acImport, 8, "PastJobs", strFolder &
strFile, True
Name strFolder & strFile As strBackupFolder & strFile
strFile = Dir$(strFolder & "*.xls")
Loop
End Sub