MS Access 2003/2007 - Import Specification for data import from csv file used in VBA.....validate field names?

Question

I have an import which I made with the wizard, at least far enough just to save the specification. It imports CSV files, with headers, quote text qualifiers, and comma delimited. I then use the import specification in some vba that fires from a button click event.

Here are some things I am wondering:

So if the fields in the data are out of order the import fails? if the data does not contain all the fields the import fails? if there are extra fields in the data the import fails?

So is there any kind of validation I can do to the data getting imported th ensure that the fields are the same as the spec, they are in the right order (if they need to be), etc.

This works pretty well, but I just see that if something in the data is out of the normal it will import anyway, and throw the whole time off.

Also....this is not a typical access set up...this is mainly for a team of data analysts to import files into a mdb...there is not an front end with this.

thanks justin

Answer 1

You can inspect the CSV file using an ADO recordset.

Public Sub InspectCsvFile()
    Const cstrFolder As String = "C:\Access\webforums"
    Const cstrFile As String = "temp.csv"
    Dim i As Integer
    Dim strConnect As String
    Dim strSql As String

    'early binding requires reference, Microsoft ActiveX Data Object Library '
    'Dim cn As ADODB.Connection '
    'Dim rs As ADODB.Recordset '
    'Dim fld As ADODB.Field '
    'Set cn = New ADODB.Connection '
    'Set rs = New ADODB.Recordset '

    'late binding; no reference needed '
    Dim cn As Object
    Dim rs As Object
    Dim fld As Object
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        cstrFolder & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
    'Debug.Print strConnect '
    cn.Open strConnect
    strSql = "SELECT * FROM " & cstrFile & ";"
    rs.Open strSql, cn
    Debug.Print "Fields.Count: " & rs.Fields.Count
    For i = 0 To rs.Fields.Count - 1
        Set fld = rs.Fields(i)
        Debug.Print i + 1, fld.Name, fld.Type
    Next i
    Set fld = Nothing
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

If that connection string doesn't work for you, see Connection strings for Textfile

Answer 2

I would provide them with a protected spreadsheet in the correct format. The protection ensures that they cannot alter it.

Provide them with the error report if/when it fails to import.