How do I insert data from a Recordset into a MySQL database?

Asked Mar 27 '16 at 16:57

Active Mar 27 '16 at 17:07

Viewed 985 times

I have created a record set from Excel data, but I am not able to select the record set and insert the data into the database:

Sub insrt()

        Dim xlXML             As Object
        Dim adoRecordset      As Recordset
        Dim rng               As Range
Dim Conn_lyr
Dim ConnString_lyr
Dim Vsql_lyr As String

   Range("B6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
        Set rng = Selection
        Set adoRecordset = CreateObject("ADODB.Recordset")
        Set xlXML = CreateObject("MSXML2.DOMDocument")
        xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML)
        adoRecordset.Open xlXML

ConnString_lyr = "DRIVER={SQL Server Native Client 11.0}; SERVER=xxxxxxxx;UID=xxxxxxx;PWD=xxxxxxx;"

Set Conn_lyr = CreateObject("ADODB.Connection")
Vsql_lyr = "insert into " + Range("E1").Value + ".DBO." + Range("f1").Value + " select * from adoRecordset"

With Conn_lyr
.ConnectionString = ConnString_lyr
.Open
.Execute (Vsql_lyr)
End With
Conn_lyr.Close
Set Conn8 = Nothing

End Sub

edited Jul 09 '18 at 19:34

Community

asked Mar 27 '16 at 16:57

Kaleem

Possible duplicate of [insert full ADO Recordset into existing ACCESS table WITHOUT LOOP](http://stackoverflow.com/q/32821618/4088852). – Comintern Mar 27 '16 at 17:09
i need help with inserting into SQL database Table... – Kaleem Mar 27 '16 at 17:35
The only difference should be the connection string. – Comintern Mar 27 '16 at 17:37
Not working... have tried modifying it in many ways..Any other solution pls – Kaleem Mar 27 '16 at 20:31
"not working" is not a useful description of your problem – Tim Williams Mar 28 '16 at 06:15
Any other approach is possible?? – Kaleem Mar 28 '16 at 18:18

How do I insert data from a Recordset into a MySQL database?

0 Answers0