Macro Query using OLEDB gives runtime error '1004'

[CoreyMagnussen]

Hi all,

I have a macro that is running a query in Access. My SQL string has an array variable in it that changes on each iteration of a for-next loop.

For the first K, the macro pulls the code in fine but on the second iteration I am getting a runtime error '1004' - Application-defined or object-defined error on the .CopyFromRecordset rs line.

I have tried moving the rs.close (ADODB.recordset) and cn.close (ADODB.connection) and the Set in and out of the for-next loop with no success.

For K = 2 To x
    If Cells(K, 3) = stationselect Then PN(K) = Cells(K, 1)
Next
K = 1
Worksheets("sheet4").Activate
For K = 2 To 9999
PNA = PN(K)
dbfullname = "M:\WISE.mdb"
MySql = "SELECT ADVPRTP_DRP_DATA.PART_NUMBER, ADVPRTP_DRP_DATA.ATTRIBUTE, ATTR_VALUE, PART_FINISH_CODE, FICHE_NOTE, FAMILY " & _
    "FROM ADVPRTP_DRP_ATTR, ADVPRTP_DRP_DATA, ADVPRTP_DRP_PART WHERE " & _
    "ADVPRTP_DRP_DATA.PART_NUMBER=('" & PNA & "') and ADVPRTP_DRP_ATTR.ATTRIBUTE=ADVPRTP_DRP_DATA.ATTRIBUTE and ADVPRTP_DRP_DATA.PART_NUMBER=ADVPRTP_DRP_PART.PART_NUMBER" 'Stack your SQL string
PNA = Empty 'Clear SQL variable string
 
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
       & dbfullname & ";"  'Create DB connection
Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = MySql 'Pass your SQL
    .Open , , adOpenStatic, adLockOptimistic '.Open , , 3, 3 '
    myCnt = .RecordCount
    If myCnt > 0 Then
        .MoveLast: .MoveFirst
         'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
            3 fields in the sql pass
        Sheets(4).Range(Cells(1, 1), Cells(myCnt, 6)).CopyFromRecordset rs
    End If
End With
rs.Close
cn.Close
Set rs = Nothing

The code continues and there is a next that closes the loop.

Thanks so much for the help!

 

[Andrew Poulsom]

Welcome to the Board.

I don't know if it's the cause of your problem but your code always writes the data starting at A1.

 

[CoreyMagnussen]

Mr. Poulsom,

Thank you for your quick response.

Further in the loop it extracts the data from these cells and clears them. Just to check I had it copied to a different set of cells the second time around and I recived the same error as above.

Maybe I'm making a fundamental mistake in misusing this function. I'm very new to connections and recordsets.

Any other help would me much appreciated.