[RESOLVED] [Access] Importing csv files into Access through VBA

[setsanto]

Hello everyone,

I swear I'm going crazy. 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

EDIT: For posterity, the answer is that I did not enclose the specification in quotation marks. It should be "UsageCSV".