Importing a CSV file? Erroring out

[dowlingm815]

The line below codes the vba code that keeps erroring out with a "[31519]: You cannot import this file" message. According to the Access 2003 VBA Language Reference, TransferText Method, the syntax is used correct. expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage.

expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage.
 

below is my code:

DoCmd.TransferText acImportDelim, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", True
 

Any suggestions would be appreciated.

[TheSmileyCoder]

Does the import work when you do it manually?

[dowlingm815]

yes, i can import it in access as a csv file.

[TheSmileyCoder]

You seem to be missing an argument in your syntax. ITs either the specification name or the table name.

[dowlingm815]

i can't seem to locate the error. the credit_memo and the path file appear to be intact.

for time efficiency, the statement was converted into an excel import statement.

however, i welcome any suggestions.

[TheSmileyCoder]

Did you fix the missing argument?

    DoCmd.TransferText _
        transferType:=acImportDelim, _
        SpecificationName:="Credit_Memo", _
        TableName:="", _
        FileName:="L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", _
        HasFieldNames:=True

From your code, I can't guess whether Credit_Memo is your table name or the name of your importspec, but one or the other is missing. I have typed it out differently for you, which may make it more clear what is happening.

[dowlingm815]

i'm having difficultly understanding what an import spec is? the credit_memo is the name of the access table. The following is the vba procedure in full, line 48 is where the executable statement is:

Private Sub ImportRecords()
 
On Error GoTo Err_Hndlr
 
 
    If TableExists("SO_Data") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "SO_Data"
    End If
    If TableExists("SO_Data$_ImportErrors") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "SO_Data$_ImportErrors"
    End If
 
 
    If TableExists("SO_CFHeader") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "SO_CFHeader"
    End If
    If TableExists("SO_CFHeader$_ImportErrors") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "SO_CFHeader$_ImportErrors"
    End If
 
    If TableExists("Credit_Memo") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "Credit_Memo"
    End If
    If TableExists("Credit_Memo$_ImportErrors") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "Credit_Memo$_ImportErrors"
    End If
 
 
    MsgBox "Please be patient while application is uploading the contracts.", vbInformation
 
 
    ' import file
'    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_Data", "L:\UH_Warehouse_Reporting\SciQuestData\SO_Data.xls", True
'    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_CFHeader", "L:\UH_Warehouse_Reporting\SciQuestData\SO_CFHeader.xls", True
'    'DoCmd.TransferText acImportDelim, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", True
'    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.xls", True
'
'    FOR TESTING PURPOSES
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_Data", "C:\Documents and Settings\DowlinMM\My Documents\Development\UH Mgt Inventory Rptg\UH Mgt Inventory Rptg\Sciquest Data\SO_Data.xls", True
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SO_CFHeader", "C:\Documents and Settings\DowlinMM\My Documents\Development\UH Mgt Inventory Rptg\UH Mgt Inventory Rptg\Sciquest Data\SO_CFHeader.xls", True
    'DoCmd.TransferText acImportDelim, "Credit_Memo", "L:\UH_Warehouse_Reporting\SciQuestData\Credit_Memo.csv", True
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Credit_Memo", "C:\Documents and Settings\DowlinMM\My Documents\Development\UH Mgt Inventory Rptg\UH Mgt Inventory Rptg\Sciquest Data\Credit_Memo.xls", True
 
 
    MsgBox "Task Complete!", vbInformation
 
ImportRecords_Exit:
  Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "ImportRecords()"
End Sub
 

[TheSmileyCoder]

The importspec is the details of how the import is meant to run. For instance, in the DoCmd.Transfertext, you have specified that there is a delimiter, but you have not specified what the delimiter is! Such a thing among others are specified in an import spec. If you manually import the file the first time, you can specify such settings, and then save the import spec.

[dowlingm815]

It took a bit a digging, but the following link is how to create an export /import specification in MS Access. My error is that I was looking to code it:

http://www.blueclaw-db.com/export-specifications.htm