Import CSV file to SQLITE database via VBA - Access

Ok, we need a few things.

First up, we need from Access:

Browse to csv file – select

Import the csv file to a table.

Now, export to a SQLite.

But, we missing a few questions here.

First up, is the SQLite database the same database and in the same location all the time?

Have you installed the SQLite ODBC driver, and have you been able to link to SQLite from Access?

So, we kind of need the above all sorted out. Especially the connection to SQLite.

And it not clear if you plan/want to create a new table for each csv import, or you going to clear out a table, and re-fill?

Lets assume the following:

You have a linked table to SQLite working from Access.

You can click on this linked table in Access, and you can see/view and even edit the data FROM Access, but the database and table is of course a linked table to SQLite.

If all the above is working? And ALL of the above so far takes ZERO code. So you “need” to get the above part working.

And we assume that each csv import is to create a new table in the SQLite database?

Then the code would look like this:

Paste this sub into a standard access code module.

Save it, and then with cursor anywhere in the code, hit f5 to run.

  Sub CsvImportToSQL()

      Dim strCsvFile    As String

      Dim f             As Object
      Set f = Application.FileDialog(3)
      f.Filters.Clear
      f.Filters.Add "Csv file", "*.csv"
      If f.Show = False Then
        ' no file selected - quite
        Exit Sub
      End If

     Dim strFromTable  As String
     strFromTable = f.SelectedItems(1)

     Dim strTable      As String
     ' get only name of file without extension for Access table name
     strTable = Mid(strFromTable, InStrRev(strFromTable, "\") + 1)
     strTable = Left(strTable, InStr(strTable, ".") - 1)

     strTable = InputBox("Select table = " & strFromTable, "Inport to Access table", strTable)

     If strTable = "" Then
        ' user hit cancel - exit
        Exit Sub
     End If

     ' transfer the table into access

     DoCmd.TransferText acImportDelim, , strTable, strFromTable, True

     ' ok, now ask for table name to export to in SQLite

     Dim strSQLiteTable   As String
     strSQLiteTable = strTable

     strSQLiteTable = InputBox("Table name to export for SQLite", "SQL lite table name", strSQLiteTable)
     If strSQLiteTable = "" Then
        ' user cancel - don't transfer - exit
        Exit Sub
     End If

     ' now transfer table to SQL site

     Dim strCon As String
     strCon = CurrentDb.TableDefs("Hotels").Connect
     ' (replace above Hotels with a KNOWN WORKING LINKED table to SQLite)

     DoCmd.TransferDatabase acExport, "ODBC Database", strCon, acTable, strTable, strSQLiteTable

     MsgBox "table exported to SQLITE"



  End Sub