Inserting data into MySQL database with VBA

Question

I have a simple macro to insert data from Excel into a MySQL database:

Dim conn As ADODB.Connection
Dim sqlstr As String
Dim rs As ADODB.Recordset
Dim Crow As Long, Lrow As Long
Dim Item As String, Price As Long, weight As Long, category As String

Set conn = New ADODB.Connection

conn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
                                            "SERVER=localhost;" & _
                                            "PORT=3306" & _
                                            "DATABASE=spendings;" & _
                                            "USER=root;" & _
                                            "PASSWORD=root;" & _
                                            "Option=3"


Set rs = New ADODB.Recordset

Lrow = Cells(Rows.Count, 1).End(xlUp).Row

 For Crow = Lrow To 2 Step -1

 Item = Sheets("Sheet1").Range("A" & Crow).Value
 Price = Sheets("Sheet1").Range("B" & Crow).Value
 weight = Sheets("Sheet1").Range("C" & Crow).Value
 category = Sheets("Sheet1").Range("D" & Crow).Value


 sqlstr = "INSERT INTO items VALUES('" & Item & "','" & Price & "','" & weight _
 & "', (SELECT idCategory FROM category WHERE Name='" & category & "'))"

 rs.Open sqlstr, conn, adOpenStatic

 Next

I am getting an error on the following line:

rs.Open sqlstr, conn, adOpenStatic

and no idea how to proceed.

Please post the error, also check what is in your sqlstr variable and copy it out and see if runs OK when not in macro. — Roger Clerkwell
– Roger Clerkwell, Commented Jun 22, 2019 at 9:20
Seems like an Error in your Query. Try the Query that excel is forming in SSMS by printing it first in any cell or Immediate Window. — Mikku
– Mikku, Commented Jun 22, 2019 at 9:24
youre query is waaaaaay off. First you only select one value in your subquery in the values part of the insert statement. Second, your subquery would need to be wrapped in () within that as well. Third, insert into select statements dont use the values part of the statement. Long story short your sql skills need some brushing up on - but hey we all start somewhere :) — Doug Coats
– Doug Coats, Commented Jun 22, 2019 at 15:53
Thank you. I fixed the query, and copied into workbench it works. However still getting the same error when running the macro: run time error -2147217887(80040e21) — Wizard
– Wizard, Commented Jun 22, 2019 at 17:30

bm13563 · Accepted Answer · 2019-06-22 10:49:00Z

As others have suggested, there is definitely a problem with your SQL string. Check out the syntax for the INSERT INTO SELECT statement here. Its often useful to test and debug your SQL strings in a database design tool such as MySQL Workbench before putting them into your code. You could also debug.print(sqlstr) to see what your string is returning e.g:

sqlstr = "INSERT INTO items ('" & Item & "','" & Price & "','" & weight _
& "', (SELECT idCategory FROM category WHERE Name='" & category & "'))"

debug.print(sqlstr)

rs.Open sqlstr, conn, adOpenStatic

I don't know your database structure but I can see some syntax errors in your string. You don't need VALUES before your columns (see above documentation). Also, you're trying insert data into 3 columns:

INSERT INTO items VALUES('" & Item & "','" & Price & "','" & weight _ & "')

but your SELECT query is only returning 1 column:

SELECT idCategory FROM category WHERE Name='" & category & "'.

Posting the debug.print output might help with further debugging.

Wizard · Accepted Answer · 2019-06-22 17:49:56Z

0

Query was wrong. Thank you. Fixed it to

sqlstr = "INSERT INTO items (Name, Price, weight, store_Id, category_Id) VALUES('" & Item & "','" & Price & "','" & weight_
 & "', (SELECT idCategory FROM category WHERE Name='" & category & "'),'" & store & "');"

Copied into workbench and it works: mysql

But I am still getting the same error: run time error -2147217887(80040e21)

answered Jun 22, 2019 at 17:49

Wizard

111 silver badge5 bronze badges

Collectives™ on Stack Overflow

Inserting data into MySQL database with VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related