title: VBA Code for Transferring ACCESS Table to SQL Server
body: <p>I was trying to Transfer my Access Table Data over to SQL server but I am getting an error which says </p>
  
  <blockquote>
    <p>Object Variable or With block variable not set</p>
  </blockquote>
  
  <p>I've already connected my Access database with SQL server but now facing problem transferring data to SQL server with VBA code.</p>
  
  <p>Can you please help me with it?</p>
  
  <p>Here is my code:</p>
  
  <pre><code>Public Sub ADOtest()
  
     Dim ADOConn As New ADODB.Connection
     Dim ADOCom As New ADODB.Command
  
  
     On Error Resume Next
  
     ADOConn.ConnectionString = "Driver={SQL Server};Server=IT-TEMP2-8470P\SQLEXPRESS2014;Database=Discrepancy;Trusted_Connection=True;"
     ADOConn.Open
  
     Debug.Print ADOConn.State
  
     Dim db As Database
     Dim Discrepancy As Database
     Set db = Discrepancy
     Dim ID As Variant
  
     db.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=IT-TEMP2-8470P\SQLEXPRESS2014;DATABASE=Discrepancy].SFTransfersDB ( ID, TO ) SELECT ID,TO FROM SFTransfersDB" 
  
  
  End Sub 
  </code></pre>
  
  <p>I am getting error at the 2nd last line:</p>
  
  <pre><code>db.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=IT-TEMP2-8470P\SQLEXPRESS2014;DATABASE=Discrepancy].SFTransfersDB ( ID, TO ) SELECT ID,TO FROM SFTransfersDB" 
  </code></pre>