title: VBA macro in Excel to Run SQL Insert statement
body: <p>Hey im quite new to VBA and I was hoping someone could help me with last bit of code.</p>
  
  <p>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.</p>
  
  <pre><code>     Private Sub ConnectDB()
  
         Dim oConn As Object
  
  Set oConn = CreateObject("ADODB.Connection")
  oConn.Open = "DRIVER={SQL Server};" &amp; _
  "SERVER=SERVER02;" &amp; _
  "DATABASE=platform;" &amp; _
  "USER=5y5t3mus3r;" &amp; _
  "PASSWORD=*******;" &amp; _
  "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=" &amp; strFile _
  &amp; ";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('" &amp; Company &amp; "', '" &amp; Address &amp; "', '" &amp; Address1 &amp; "', '" &amp; Address2 &amp; "', '" &amp; Address3 &amp; "', " &amp; Phone &amp; ", " &amp; Fax &amp; ");"
  
      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) &amp; " 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
  </code></pre>
  
  <p>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. </p>
  
  <p>Any help with this would really be appreciated thanks!</p>