VBA macro in Excel to Run SQL Insert statement

Question

Hey im quite new to VBA and I was hoping someone could help me with last bit of code.

I am trying to take cells from a spreadsheet and add them to a SQL table but I am having trubble running the SQL statement. Here is the code I have so far.

     Private Sub ConnectDB()

       Dim oConn As Object

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "DRIVER={SQL Server};" & _
"SERVER=SERVER02;" & _
"DATABASE=platform;" & _
"USER=5y5t3mus3r;" & _
"PASSWORD=*******;" & _
"Option=3;"
 If oConn.State = adStateOpen Then
  MsgBox "Welcome to Database!"
 Else
 MsgBox "Sorry No Database Access."
 End If


Dim rs As ADODB.Recordset
Dim strSQL As String
Dim Company As String
Dim Address As String
Dim Address1 As String
Dim Address2 As String
Dim County As String
Dim Contact As String
Dim Phone As String
Dim Fax As String


    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"


Sheets("wsheet").Activate

 Set rs = New ADODB.Recordset
rs.Source = Sql


    With wsheet


        MyFile = "C:\Users\Ryan.ICS\Documents\Documents\InsertStatement.txt"
         fnum = FreeFile()
        Open MyFile For Output As fnum


         myRow = 2
          myCol = 4

          For myRow = 2 To InputBox(Prompt:="What is the last row of data?", Title:="Data Row", Default:=1)

          myCol = 4


Company = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address1 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address2 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Address3 = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 2
Phone = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1
Fax = ActiveSheet.Cells(myRow, myCol)
myCol = myCol + 1


        strSQL = "INSERT INTO [sandbox].[5y5t3mus3r].[ryan] (Organisation, Address1, Address2, TownCity, County, Telephone, Fax) VALUES('" & Company & "', '" & Address & "', '" & Address1 & "', '" & Address2 & "', '" & Address3 & "', " & Phone & ", " & Fax & ");"

    Print #fnum, strSQL

     DoCmd.RunSQL strSQL  ***Here is where I am haveing an error it will not run the SQL command.****

        oConn.Execute strSQL **** here is another tag I tried in a number of different ways but i still couldnt get the SQL statement to run



     Next


         End With

            ' Find out how many rows were affected by the Insert.
         Set rs = oConn.Execute("Select @@rowcount")
         ' Display the first field in the recordset.
          MsgBox rs(0) & " rows inserted"

          oConn.Close



           Set rs = Nothing
           Set oConn = Nothing

              Close #fnum


             End Sub

         Function esc(txt As String)

    esc = Trim(Replace(txt, "'", "\'"))

        End Function

The error arises when I am trying to run the SQL statement do I need to create an object or method for this or something.

Any help with this would really be appreciated thanks!

Which error are you getting? `DoCmd`? This is an Access object, not an Excel object. — Olivier Jacot-Descombes, Jan 24 '12 at 13:32
Which error are you getting? DoCmd? This is an Access object, not an Excel object. – Olivier Jacot-Descombes 1 min ago Hey Olivier no I get a Run-Time error '424': Object required Thanks for your quick reply — user1167046, Jan 24 '12 at 13:40
Do a `debug.print strSQL` (after you've constructed it, but before you execute). Then copy the string it will output to the immediate window. Then paste the query into your favorite SQL tool and run it. I think best idea is to get a working INSERT INTO first, and then modify your VBA so it matches. Hope this helps. — jon, Jan 24 '12 at 13:44
Sorry for the late reply I had to go on to some other work. The insert statement works now I had a sheet that I was writing to but I fixed it completely but still getting the same area. Do I not need to call SQL Query Analizer or something? — user1167046, Jan 24 '12 at 15:30
The `;` at the end of the string should be causing an error, However it should not be a `424 object required` error. Your connection snytax and query execution syntax seems fine to me. — Pynner, Jan 24 '12 at 21:15
`Run-Time error '424': Object required` is telling you there is no such thing as `DoCmd`. DoCmd is in Acccess, not Excel. `oConn.Execute` is the one to use. What error do you get with that? — Ben, Jan 26 '12 at 14:25

score 1 · Answer 1 · answered Mar 03 '12 at 05:47

I'd guess it's this line:

rs.Source = Sql

The Source property accepts an IStream as well as a string, so since Sql isn't declared anywhere, it's implicitly an object with a value of Nothing.

My next guess is a couple of lines below that, where does wsheet get assigned?

Of course, all this would be easier if we knew which line the error occurs on... easier still if you set a break point and step into the code -- you don't need to dump variable values to file, you can view them interactively in the debugger.

score 1 · Answer 2 · answered Mar 20 '12 at 13:07

1

Instead of using the INSERT statement, I would suggest creating a stored procedure and passing values into it using the ADOBO.Command object.

answered Mar 20 '12 at 13:07

Derek B. Bell

319
2
12

VBA macro in Excel to Run SQL Insert statement

2 Answers2