ADO in Excel VBA

[dugdugdug]

I am using ADO to retrieve data from sheet 1 and 2 into sheet 3, within the same workbook.

The following code does the job correctly.

However, when I have two such workbooks open, say workbook 1 and 2 and run the code separately to retrieve data into sheet 3 of workbook 1 (from sheets 1 and 2 of workbook 1) and retrieve data into sheet 3 of workbook 2 (from sheets 1 and 2 of workbook 2), I get a runtime error message.

I suspect it might have something to do with locking: rs.LockType = adLockOptimistic

Can anyone help?


Sub Get_Data

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

rs.LockType = adLockOptimistic

Dim strFile As String
Dim strcon As String

strFile = ThisWorkbook.FullName

strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFile & ";Extended Properties=""Excel 12.0 Macro;HDR=Yes;IMEX=1;MaxScanRows=0"";"

cn.Open strcon

Dim strSQL As String

strSQL = "SELECT [Sheet1$].[Field1], [Sheet1$].[Field2]" & _
"FROM [Sheet1$]" & _
"LEFT JOIN [Sheet2$]" & _
"ON [Sheet1$].[Field1] = [Sheet2$].[Field1]" & _
"WHERE [Sheet2$].[Field1] Is Null"

rs.Open strSQL, cn

Dim wsm as Worksheets

Set wsm = Thisworkbook.Worksheets("Sheet3")

wsm.Select

wsm.Range("A2").CopyFromRecordset rs

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

 

[VishMishra]

Hi,

Where are you getting the error and what is the error?

Thnks,
Vish
http://www.learnexcelmacro.com

 

[dugdugdug]

Hi,

Where are you getting the error and what is the error?

Thnks,
Vish
http://www.learnexcelmacro.com

With both workbooks open, I run the code in the first workbook and all is fine.

Without closing the first workbook, I run the code in the second workbook and get the following message:

Run-time error '-2147217904 (80040e10)':

No value given for one or more required parameters.

The line in VBA that fails is the following: rs.Open strSQL, cn

This message (the one in bold) is normally seen if the SQL statement is wrong but clearly it's not because if I ran the second workbook with the first workbook CLOSED, all is fine.

 

[Norie]

If you have the workbooks open why are you using ADO?

 

[dugdugdug]

It's to do a left join, selecting mismatches.

 

[Norie]

You could do that without ADO.