Hey all
found the answer, posted it on KBase...
here is a copy for your viewing:
Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _
sqlstr As String, dbfile As String, usernm As String, pword As String)
Set dbcon = New ADODB.Connection
dbcon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
"", ""
Set dbrs = New ADODB.Recordset
dbrs.Open sqlstr, dbcon
End Sub
Sub Can()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim SQL As String
Dim filenm As String
Sheets("Cancelled").Select
SQL = "Select * FROM [tblsal]" & _
" WHERE [tblsal].[Status]= '" & Range("C1").Value & "'"
filenm = (ActiveWorkbook.Path & "\Store.mdb")
Call GetCn(adoconn, adors, SQL, filenm, "", "")
Dim xlSht As Excel.Worksheet
Set xlSht = Sheets("Cancelled")
xlSht.Range("A6").CopyFromRecordset adors
SQL = "Delete * FROM [tblsal]" & _
" WHERE [tblsal].[Status]= '" & Range("C1").Value & "'"
Call GetCn(adoconn, adors, SQL, filenm, "", "")
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Set xlSht = Nothing
End Sub