Can't execute a SQL statement

• Question

• 

  

  0

  Sign in to vote

  I use the following code to append data from MS Excel spreadsheet into MS Access table. In MS Excel I define the data range; for the code below I call it Stage. This used to work in the past; however, now I'm getting an error message stating that the data engine could not find the object Stage. The code breaks on the SQL execution statement. Any ideas why i the data engine is not recognizing Stage anymore and is there any other way to go about this? Many thanks

  Dim rstblODC As New ADODB.Recordset
  Dim rsODC As New ADODB.Recordset
  Dim cn1 As ADODB.Connection
  
  Dim varODC_Date(), varAmount(), varRecordcount, varODC_Date1 As Variant
  Dim strSource1, strSource2 As String
  Dim intUserMsg, intUserMsg2 As Integer
  
  Set rstblODC = New ADODB.Recordset
  
  Set cn = New ADODB.Connection
  Set cn1 = CurrentProject.Connection
  
  
  strSource1 = InputBox("Enter Date mm-dd-yyyy", "IPS")
  strSource2 = "C:\Users\Owner\Downloads\Transactions-Download-" & strSource1 & ".xlsx"
  
  With cn
  
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .CursorLocation = adUseClient
      .ConnectionString = "Data Source=" & strSource2 & ";Extended Properties=Excel 12.0;"
      .Open
      
  End With
  
  varSQL = "SELECT * FROM stage"
  
  cn.Execute varSQL
      
  rsODC.Source = varSQL
  
  rsODC.Open , cn

  • Moved by Cherry BuMicrosoft contingent staff Tuesday, November 21, 2017 8:35 AM Move from vb.net

  Saturday, November 11, 2017 3:30 PM