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

I'm not too good with SQL but maybe you need to pass the variable's reference instead of the literal string? strSql = "INSERT INTO Data (Food, Drinks, Color) VALUES (" & Foodrng & ", " & Drinksrng & ", " & Colorrng & ")" (You may also try Foodrng.Address if it doesn't work) — BruceWayne
– BruceWayne, Commented Apr 3, 2018 at 19:21

Erik A · Accepted Answer · 2018-04-04 13:25:13Z

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

edited Apr 4, 2018 at 13:25

answered Apr 3, 2018 at 19:40

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

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

16 Comments

Erik A Over a year ago

@Parfait Nope, that doesn't work and that's why the code comment Adjust ranges to select single cells. The proper way is to use a recordset and iterate through the cells. Or even better: import from Access instead of exporting from Excel. That way, you can customize more easily.

Erik A Over a year ago

@Parfait I guess that's true. Added clarification.

Austin Over a year ago

Compile error: User defined type not defined. This happenes on the first AND second cmd variable

Erik A Over a year ago

Set a reference to ADO (Microsoft ActiveX Data Objects) if you want to use ADO. Doing this all with late bindings is unnecesarily hard.

Austin Over a year ago

I will have multiple users with different versions of access (Therefore different library versions). Wont using early binding cause unwanted issues?

|

Piotr Rodak · Accepted Answer · 2018-11-03 10:03:05Z

0

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

answered Nov 3, 2018 at 10:03

Piotr Rodak

1,9411 gold badge13 silver badges8 bronze badges

Collectives™ on Stack Overflow

Export data to Access from Excel VBA

2 Answers 2

16 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related