VBA Macro upload data from Excel spreadsheet to phpmyadmin database

[shy4x4]

Hi all,

I have been searching for an answer on this problem for about a week now but to no avail and I am beginning to think that it might not be possible. So thought I would ask some experts to get their thoughts!

What I am attempting to do is write a macro which could connect to a phpmyadmin database from excel and upload data from excel to the phpmyadmin database. I have seen a lot of content on the reverse of this which is pulling data from the database into excel, however I would like to automate the process of uploading data everyday from the excel spreadsheet to the database.

So far I have been able to write the below VBA which successfully connects and disconnects from the DB however does someone out there have any VBA or guidance as to what the code might be to insert data from the excel spreadsheet into the phpmyadmin database?


Private Sub UserForm_Initialize()
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
con.ConnectionString = "server=test.tesing.com.au" & _
";driver=MySQL ODBC 3.51 Driver;db=online;" & _
"uid=MSmith;pwd=Password;" & _
"Option=16386"
'con.CursorLocation = adUseClient
con.Open

***thinking that the macro insert code will go here but need to see the format it should be written in***

con.Close
End Sub

Thanks all for your assistance here.

 

[shy4x4]

I have worked it out, placing my code here to help others.


Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset

'remove dangerous characters
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
End Function

Private Sub cmdInsertData_Click()
On Error GoTo ErrHandler
Set rs = New ADODB.Recordset
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=YourServerNameHere;" & _
"DATABASE=YourDatabaseNameHere;" & _
"USER=YourUserNameHere;" & _
"PASSWORD=YourPasswordHere;" & _
"Option=3"

'number of rows with records. testingupload is the sheetname.
Dim height As Integer
height = Worksheets("testingupload").UsedRange.Rows.Count

'insert data into SQL table. testingupload is the sheetname.
With Worksheets("testingupload")
Dim rowtable As Integer
Dim strSQL As String
For rowtable = 2 To height
strSQL = "INSERT INTO YourTableNameHere (YourFieldHere, YourFieldHere, YourFieldHere) " & _
"VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 3).Value)) & "')"
rs.Open strSQL, oConn ',adOpenDynamic, adLockOptimistic
Next rowtable
End With

MsgBox "Insert with success " & Trim(Str(rowtable - 2)) & " records", vbInformation, "Verification Data Entry"
ErrHandler:
If Err.Description <> "" And Err.Source <> "" Then
MsgBox Err.Description, vbCritical, Err.Source
End If
End Sub