MS Access: Import Specification not working through VBA

Question

I have an import specification for csv files which, when I run it on a file through the GUI, works just fine. However, when I run it through VBA, for some reason it forgets that one column is supposed to be a Text column and makes it a Number column instead, thereby causing tons of errors.

My code is below. It works in the sense that everything runs properly, but for some reason the import specification for the CSVs does not run properly. The meaningless case switch is a place holder, as I will need to add more types of reports after I get the first working.

Sub ImportDE(Folder As Object)
Dim db As DAO.Database
Dim names As DAO.Recordset
Dim Files As Object, file As Object, SubFolders As Object, subfolder As Object
Dim ExString As Variant
Dim check As Boolean
Dim FileChange As String

Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0
db.TableDefs.Refresh

Set names = CurrentDb.OpenRecordset("SELECT Old FROM DENames")

Set Files = Folder.Files
Set SubFolders = Folder.SubFolders

For Each subfolder In SubFolders
    ImportDE subfolder
Next

With names
    Do While Not .EOF
        ExString = .Fields(0)
        For Each file In Files
            If InStr(file.Type, "Worksheet") > 0 Then
                If InStr(file.Path, ExString & ".xls") > 0 Then
                    DoCmd.TransferSpreadsheet _
                        TransferType:=acImport, _
                        SpreadsheetType:=acSpreadsheetTypeExcel9, _
                        TableName:="tblImport_" & ExString, _
                        filename:=file.Path, _
                        HasFieldNames:=True, _
                        Range:="A:CT"
                    db.TableDefs.Refresh
                End If
            ElseIf InStr(file.Type, "Comma Separated") > 0 Then
                If InStr(file.Path, ExString & ".csv") > 0 Then
                    Select Case ExString
                    Case "Usage"
                        DoCmd.TransferText _
                            TransferType:=acImportDelim, _
                            SpecificationName:=UsageCSV, _
                            TableName:="tblImport_" & ExString, _
                            filename:=file.Path, _
                            HasFieldNames:=True
                        db.TableDefs.Refresh
                    End Select
                End If
            End If
        Next
        .MoveNext
    Loop
End With
db.Close:   Set db = Nothing
End Sub

Am I missing something obvious? Why doesn't the import spec work properly?

HansUp · Accepted Answer · 2013-05-17 18:57:01Z

2

The TransferText SpecificationName parameter is supposed to be a string expression. Since the code does not declare a variable named UsageCSV, I'm guessing that is the literal name of the specification. If that's correct, enclose the name in double quotes.

DoCmd.TransferText _
    TransferType:=acImportDelim, _
    SpecificationName:="UsageCSV", _
    TableName:="tblImport_" & ExString, _
    filename:=file.Path, _
    HasFieldNames:=True

answered May 17, 2013 at 18:57

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HansUp Over a year ago

You're welcome. That issue has bitten many folks. Make friends with Access' online help feature. It's far from perfect, but still worth checking to make sure you're using command parameters correctly. Good luck.

Simon Van Duffelen · Accepted Answer · 2015-06-23 19:40:54Z

2

At the top of your code module After Option Compare Database Add Option Explicit. This will require all of your variable to be declared and you will never have this issue again

answered Jun 23, 2015 at 19:40

Simon Van Duffelen

211 bronze badge

Collectives™ on Stack Overflow

MS Access: Import Specification not working through VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related