VBA Code for Transferring ACCESS Table to SQL Server

Question

I was trying to Transfer my Access Table Data over to SQL server but I am getting an error which says

Object Variable or With block variable not set

I've already connected my Access database with SQL server but now facing problem transferring data to SQL server with VBA code.

Can you please help me with it?

Here is my 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 

I am getting error at the 2nd last line:

db.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=IT-TEMP2-8470P\SQLEXPRESS2014;DATABASE=Discrepancy].SFTransfersDB ( ID, TO ) SELECT ID,TO FROM SFTransfersDB" 

Answer 1

It seems you are attempting an ad hoc distributed query. If server permits it, consider executing this statement after your ADO connection. No need for calling Access objects.

ADOConn.Execute "INSERT INTO dbo.SFTransfersDB ([ID], [TO])" _
           " SELECT [ID], [TO] FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', " _
           "'Data Source=""C:\Path\To\Access\Database.accdb""')...SFTransfersDB;" 

Answer 2

You declare two Database variables, but don't initialize them (you don't set them to anything):

Dim db As Database
Dim Discrepancy As Database

So this

Set db = Discrepancy

is the same as

Set db = Nothing

and so when you try to use db, you get the error that db is not set, which it isn't.