To use the first SQL Insert method, you need to know the fieldnames. That string can be large so it may not be that efficient.
I don't have time to finish this 2nd method right now. It needs the loop to add the Excel rows from Height as records. In the loop, use rs.AddNew, iterate through each fieldname in the recordset, and add field values. I added a loop and debug.print to show you how to do these things. After adding the field values for a new record in the range loop, use rs.Update to update the record set. I will finish this when I get time. This method is similar to what I posted in a recent thread. That user was able to gleam what he needed.
' http://www.utteraccess.com/forum/lofiversion/index.php/t1947720.html
Sub InsertIntoMySQL()
Dim height As Long
Dim rowtable As Long, row As Long
Dim strSQL As String
' How to add the ADO object:
' Tools > References > Microsoft ActiveX Data Objects 2.8 Library
Dim oConn As ADODB.Connection
Dim rs As ADODB.RecordSet
Dim col As Integer, wsName As String, dbName As String
Dim a As Variant
wsName = "Film"
dbName = "film"
On Error GoTo ErrHandler
Set rs = New ADODB.RecordSet
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
"SERVER=localhost;" & _
"DATABASE=Sakila;" & _
"USER=root;" & _
"PASSWORD=ken;" & _
"Option=3"
'number of rows with records
height = Worksheets(wsName).UsedRange.Rows.Count
'height = 3
' Create RecordSet
Set rs = New ADODB.RecordSet
' Record locking ' http://www.utteraccess.com/wiki/index.php?title=Beginners_Guide_To_ODBC
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
With rs
strSQL = "SELECT * FROM " & dbName
rs.Open Source:=strSQL, ActiveConnection:=oConn
' Write the field names
For col = 0 To .Fields.Count - 1
'Debug.Print rs.Fields(col).Name
Next col
MsgBox rs.RecordCount
' Write the recordset
'Range("A1").Offset(1, 0).CopyFromRecordset rs
.MoveFirst
a = rs.GetRows
'MsgBox "First Record's film_id: " & a(0, 0), , "Second Record's film_id: " & a(0, 1)
'MsgBox "First Record's title: " & a(1, 0), , "Second Record's title: " & a(1, 1)
If .RecordCount < 1 Then GoTo EndNow
.MoveFirst
For row = 0 To 0 '(.RecordCount - 1)
For col = 0 To .Fields.Count - 1
Debug.Print "Row: " & row, "Field: " & col, .Fields(col).Name, .Fields(col).Value
Next col
.MoveNext
Next row
End With
ErrHandler:
If Err.Description <> "" And Err.Source <> "" Then
MsgBox Err.Description, vbCritical, Err.Source
End If
EndNow:
Set rs = Nothing
oConn.Close
Set oConn = Nothing
End Sub
I inserted the first 3 records from sakila.film database in, https://app.box.com/s/dzbthvp7sn50q9eaijaj. I just changed the film_id autoincrement values. If those 3 records can be updated, you will be set. Notice in the example that you can add field values by using the field's name or the 0-based index for the field.
This will then give you two ways to Insert records once I finish the range loop.
Reply With Quote
Originally Posted by etheer
