Export data to Access from Excel VBA

Question

I am brand new to access and struggling to understand how to relate access table/column/row formatting to excel exporting in VBA.

I have created an access table called Data and three columns Food, Drinks, Color.

I would like to export these range of cells to Access from my excel spreadsheet:

Foodrng = Workbooks(xlFile).Sheets("ToBeExported").Range("D6")
Drinksrng = Workbooks(xlFile).Sheets("ToBeExported").Range("E6")
Colorrng= Workbooks(xlFile).Sheets("ToBeExported").Range("B12:B21")

Everything online says I should use this for because of my version:

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"

I would like to use INSERT TO formatting to write to my database because I will be expanding the database columns quite a bit, and I feel this is the easiest way to keep track of which is going where.

strSql = "INSERT INTO Data (Food, Drinks, Color) VALUES (Foodrng, Drinksrng,Colorrng)"

I always get a syntax error when executing:

Set rs = cn.Execute(strSql)

What is the correct way to export to the Access database using the above method? Any/all information will be super helpful as I am brand new to Access

My full code:

    Foodrng = Workbooks(xlFile).Sheets("ToBeExported").Range("D6")
    Drinksrng = Workbooks(xlFile).Sheets("ToBeExported").Range("E6")
    Colorrng= Workbooks(xlFile).Sheets("ToBeExported").Range("B12:B21")

    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source= C:\Users\User1\MyDBase.accdb"
    cn.Open strConnection

   strSql = "INSERT INTO Data (Food, Drinks, Color) VALUES (Foodrng, Drinksrng,Colorrng)"

    Set rs = cn.Execute(strSql)
    'MsgBox rs.Fields(0) & " rows in MyTable"
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

Answer 1

When inserting using queries, you need to pass values using parameters. I highly recommend using recordsets over insert queries.

A normal insert query can only insert one row at a time. You will need to adjust the code to insert one row at a time. You can either use a recordset, or execute a query for each row.

Foodrng = Workbooks(xlFile).Sheets("ToBeExported").Range("D6") 'Adjust ranges to select single cells
Drinksrng = Workbooks(xlFile).Sheets("ToBeExported").Range("E6")
Colorrng= Workbooks(xlFile).Sheets("ToBeExported").Range("B12:B21")

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source= C:\Users\User1\MyDBase.accdb"
cn.Open strConnection

strSql = "INSERT INTO [Data] ([Food], [Drinks], [Color]) VALUES (?, ?, ?)"
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = cn
    .CommandText = strSql
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, , foodRng) 'adVarWChar for text 
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, , Drinksrng) 'adInteger for whole numbers (long or integer)
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, , Colorrng)
    .Execute
End With
cn.Close

Answer 2

There maybe also another way, or two, perhaps worth consideration.

The first would be to set up your Excel spreadsheet as a linked table in Access - maybe it can be done with VBA. This would save you the requirement to copy the data. You might also be able to set up your target worksheet and use insert..select directly from ADODB to insert the data into Excel from Access.

The second, would be to completely avoid Access altogether if your requirements allow for this. Excel can be used as a database to some extent and supports SQL querying. https://selectcompare.com/blogs/news/write-select-statements-for-excel-spreadsheets