Update MySQL database PHP




Update MySQL database PHP

Updating existing data in a MySQL database is easily done by using this VBA Macro code. Many are using the methodology when working with websites developed in PHP and MySQL.


Explanation

This VBA Macro code is optimized for updating an exsisting MySQL database. You need a connector, ODBC for latest version mysql.com download the excel file ate the bottom of this page or copy and paste the code directly. In the file there are some data that needs to be added according to your set up of the MySQL database and connection. Fill in the data and add the required fileds that you are going to update. Push the buttom and you will be updating existing data in your MySQL database. MySQL is one of the most effective databases and the best thing is that it is used free of charge.

You have to enable the Microsoft ActiveX Data Objects X.X Library in order to execute the VBA Macro code.

 

 

Code

Sub UpdateMySQLDatabasePHP()

' For detailed description visit http://www.vbaexcel.eu/


Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

User_ID = Range("h1").Value                      'i d user or username
Password = Range("e3").Value                    ' Password
Server_Name = Range("e4").Value             ' IP number or servername
Database_Name = Range("e1").Value         ' Name of database
Tabellen = Range("e2").Value                     ' Name of table to write to

rad = 0
While Range("a6").Offset(rad, 0).Value <> tom
    TextStrang = tom
    kolumn = 0
    While Range("A5").Offset(0, kolumn).Value <> tom
        If kolumn = 0 Then TextStrang = TextStrang & Cells(5, 1) & " = '" & Cells(6 + rad, 1)
        If kolumn <> 0 Then TextStrang = TextStrang & "', " & Cells(5, 1 + kolumn) & " = '" & Cells(6 + rad, 1 + kolumn)
        kolumn = kolumn + 1
    Wend
    TextStrang = TextStrang & "'"
    SQLStr = "UPDATE " & Tabellen & " SET " & TextStrang & "WHERE " & Cells(5, 1) & " = '" & Cells(6 + rad, 1) & "'"
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    Cn.Execute SQLStr
    rad = rad + 1
Wend
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

 

 

 

Download excel file! Update-MySQL-Database-PHP.xls

Comments

Hello, could you pls advise, why i got following error? --------------------------- Microsoft Visual Basic --------------------------- Compile error: User-defined type not defined

Comment made by: Agus , 2010-09-07 13:18:39


[ODBC Driver Manager] The data source name not found and there was no default driver specified. I'm trying to access a remote computer to the server and am getting this error. Any hint will be appreciated. Thank you.

Comment made by: Denfox , 2012-05-16 14:01:11


How can you only make it so it updates a few rows or columns? like 3 or so.

Comment made by: Bob , 2012-05-30 02:18:21


The Data source name not found error comes from well, the data source not being found. Make sure you have ODBC (Data Sources) installed, and have setup a connection to your database within that ODBC Manager. In that program, once you have set up a connection there will be a column labeled "Driver" copy whats in that column to the Driver= xxxxxx in your code. Also, make sure you have Microsoft ActiveX Data Library 2.8 enabled. To do so, go to excel> hit alt-F11> Tools> References> scroll down until you see Microsoft ActiveX Data Objects 2.8 Library> Check the box> hit ok> re-run your code.

Comment made by: RandomRanger , 2014-08-12 19:01:50


These lessons on VBA/MySQL are wonderful - They have set my learning effort ahead by many weeks. I am familiar with VBA but not a pro. You use the terms "tom", "kolumn" and "rad" that I an unaware of their meanings. Where do theu come from?Oracle badly needs a lesson from you on how to teach.

Comment made by: Jim Waksh, jswalshAcwcruises.net , 2015-05-09 20:34:21


I keep getting an error at the first Dim line of the code. Have been able to establish the connection to the database using the from other sources connector in excel and downloaded the full database. Any ideas why the Macro wont run?

Comment made by: Andrew , 2015-08-20 21:18:44


I keep getting an error at the first Dim line of the code. Have been able to establish the connection to the database using the from other sources connector in excel and downloaded the full database. Any ideas why the Macro wont run?

Comment made by: Andrew , 2015-08-20 21:46:13


I keep getting an error at the first Dim line of the code. Have been able to establish the connection to the database using the from other sources connector in excel and downloaded the full database. Any ideas why the Macro wont run?

Comment made by: Andrew , 2015-08-20 22:08:08


I have been able to do the update with vba on all data except dates - can you provide some vba code for updating MySQL date from excel vba?

Comment made by: jswalshAcwcruises.net , 2016-02-12 22:17:29


Write Comment:

Comment:

two + 3 minus 1=


Your name: