update mysql table from excel sheet

Hi,

I'm wondering how I can update a mysql table from an excel sheet. Do I need some VB script?

Thank you,
Victor
Victor KimuraSEO, Web DeveloperAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Here is a quick example. I have commented it so you won't find any trouble understanding it. Please set a reference to the Microsoft ActiveX Data Objects Library in your Excel Workbook. Fire up the Visual Basic Editor using Alt-F11. Go to the Tools top menu item of the Visual Basic Editor and choose References. In the list of references, check the Microsoft ActiveX Data Objects 2.8 Library.

Hope this helps :)

Sid

Code

Dim rs As ADODB.Recordset
Dim cn As ADODB.Cennection
Dim wb As Workbook

Private Sub UpDateMySqlTbl()
    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    Set wb = ActiveWorkbook
    '~~> Chaneg to the relevant sheet
    Set ws = Sheets("Sheet1")
    
    '~~> Update as required
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & _
        "DATABASE=yourdatabase;" & "USER=yourdbusername;" & _
        "PASSWORD=yourdbpassword;" & "Option=3"

    With ws
        For rowCursor = 1 To 100 '<~~ Update Row numbers as required
            '~~> FIELD1, FIELD2, FIELD3 are fields in your table. Change as applicable
            '~~> FIELD1VALUE, FIELD2VALUE, FIELD3VALUE are values that you want to update. Change
            '~~> these as applicable. Right now they are picking up values from Col A, B and C
            FIELD1VALUE = .Range("A" & i).Value
            FIELD2VALUE = .Range("B" & i).Value
            FIELD3VALUE = .Range("C" & i).Value
            strSQL = "INSERT INTO MyTable (FIELD1, FIELD2, FIELD3) " & _
                "VALUES ('" & FIELD1VALUE & "', " & "'" & FIELD2VALUE & "', " & _
                FIELD3VALUE & ")"
            rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
        Next
    End With
End Sub

Open in new window

0
 
SiddharthRoutCommented:
You can do it from vbscript, VBA Excel or SQL itself :)

Sid
0
 
SiddharthRoutCommented:
Sorry, I just notice it it mysql.

Would you like a VBA Excel code?

Sid
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi SiddharthRout,

Thank you for the info. Sorry, I'm still new to VB coding. Just a few questions:

1) How do I reference Microsoft ActiveX Data Objects Library? Do I have to download the library or is it already installed?

2) This line:
FIELD1VALUE = .Range("A" & i).Value

Open in new window


I'm just wondering what the i in the brackets is.

3)  What is adLockOptimistic? Is this to lock the table while it's inserting?

Thank you,
Victor
0
 
SiddharthRoutConnect With a Mentor Commented:
1) How do I reference Microsoft ActiveX Data Objects Library? Do I have to download the library or is it already installed?

>> As mentioned in my last Post Start the Visual basic Editor and then click on the tools menu, select References. In the list of references, check the Microsoft ActiveX Data Objects 2.8 Library.

2) I'm just wondering what the i in the brackets is.

Oops, A typo from my end. Please change the line

For rowCursor = 1 To 100

to

For i = 1 To 100

3)  What is adLockOptimistic? Is this to lock the table while it's inserting?

See this link. It not only explains that but explains other locks as well. :)

http://www.w3schools.com/ADO/met_rs_open.asp

Sid
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi SiddharthRout,

I receive this error after the line cn.Open = line. update mysql table runtime error
0
 
SiddharthRoutCommented:
>>>I receive this error after the line cn.Open = line.

   '~~> Update as required
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & _
        "DATABASE=yourdatabase;" & "USER=yourdbusername;" & _
        "PASSWORD=yourdbpassword;" & "Option=3"

Please some me the code that you are using in that line. I hope you have amended it as I suggested in the code.

Sid
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi,

I have this:
    '~~> Update as required
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & _
        "DATABASE=test;" & "USER=root;" & _
        "PASSWORD=8469lovingJesus;" & "Option=3"

I had it updated. I'm still receiving the error. The settings (server, db name, user, password) are correct. I believe the driver name is correct but I'm not certain.

Much thanks,
Victor
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi broomee9,

I think you should reword your request to something that is more euphemistic (i.e. kinder and gentler). The words you use seem to convey a condemning attitude. Please remember that we are paying members and without the paying members this would be simply another forum.
0
 
bromy2004Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.