Open Connection and Recordset objects to use SQL for sheet to sheet data movement

Question

I am trying to open Connection and Recordset to use SQL to move data between worksheets within the open workbook. To be clear all the data source sheets are open in the current workbook that I am trying to copy data from and to a different worksheet within the same open workbook.

I have used this code with different inputs to copy data from closed workbooks into the current workbook without error.

The error I am getting is the

"[Microsoft][ODBC Device Manager] Data source name not found and no default driver specified"

.

The error occurs here:

objRecordSet.Open strSQL, objConnection, 0, 1, 1

The debugger says:

objConnection.Open is <Expression not defined in context>

objConnection.Open : <Expression not defined in context> : Empty :        UserForm1.cbPrepareUpload_Click

objConnection : "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=F:\Temp04\Test.xlsm;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OL"

The environment is Windows 7 64 bit, Office 2010, 32 bit.

I have been agitating the little grey cells over this all day. I have tried to boil this down to the basics but now I am stuck.

Any ideas would help. Thanks, CraigM

Here is the code.
======================================================

 Private Sub cbPrepareUpload_Click()

 Dim HaveHeader As Boolean

 Dim UseHeaderRow As Boolean

 Dim i As Long

 Dim RowToTest As Long

 Dim mySheet As String

 Dim shName As String

 Dim sh As Worksheet

 Dim strConnect As String

 Dim strSourceRange As String

 Dim strSource As String

 Dim strSourceFile As String

 Dim strSourceSheet As String

 Dim strSQL As String

Dim strTarget As String


 Dim objConnection As ADODB.Connection
 Dim objRecordSet As ADODB.Recordset


 Dim wksName As Worksheet


 Set objConnection = New ADODB.Connection
 Set objRecordSet = New ADODB.Recordset


 strSourceFile = "F:\Temp04\Pricing.xlsx"

 strSourceSheet = "Pricing"

 strSQL = "SELECT * FROM  [Sheet$3] & ;"

 HaveHeader = True

 UseHeaderRow = True

 strSource = "Pricing"

strTarget = "BF_Upload"

 For Each wksName In Sheets 

    If wksName.Name = strTarget Or wksName.Name Like strTarget & "*" Then i = i + 1
 Next

 If i = 0 Then

 Else
     Worksheets(strTarget).Activate                            

     ActiveSheet.Name = strTarget & "-" & (i + 1)              

 End If

 Worksheets.Add(Before:=Worksheets(Worksheets.Count)).Name = strTarget   

ActiveSheet.Name = strTarget                      

Sheets(strTarget).Move Before:=Sheets(1)                         

strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=" & ThisWorkbook.FullName & ";" & _
         "Extended Properties=""Excel 12.0;HDR=Yes"";"

 objConnection.Open strConnect

 objRecordSet.Open strSQL, objConnection, 0, 1, 1

 Sheets(strSource).Cells(2, 1).CopyFromRecordset objRecordSet

 objRecordSet.Close
 objConnection.Close

 Worksheets(strTarget).Activate

  End Sub

Answer 1

Is the "strConnect" parameter intentionally commented out in objConnection.Open 'strConnect Uncomment that parameter and things will hopefully work

edit: also strSQL = "SELECT * FROM [Sheet$3] & ;" is wrong. It should be strSQL = "SELECT * FROM [Sheet3$];" (the $ sign was in the wrong place and there was a stray & symbol in there too)