I'm getting the following error when trying to open an ADO recordset:
Object variable or With block variable not set
I'm trying to compare two recordsets, but I can't even open them
Public Function CheckData(ByVal application As String)
' Check Application
Select Case application
Dim strCriteria As String
Dim strMsg As String
Dim checkSQL1 As String
Dim checkSQL2 As String
Dim conn As ADODB.Connection
Dim rsADO1 As ADODB.Recordset
Dim rsADO2 As ADODB.Recordset
Set conn = CurrentProject.Connection
checkSQL1 = " SELECT * FROM dbo_tblEmployer"
checkSQL2 = " SELECT * FROM ImportedData"
rsADO1.Open checkSQL1, conn
rsADO2.Open checkSQL2, conn
Dim lngTotalRowsExist As Long
Dim lngTotalRowsMatched As Long
lngTotalRowsExist = 0
lngTotalRowsMatched = 0
Form_frmImport.txtProgress.Text = "Checking for possible duplicates..."
Do Until rsADO2.EOF
lngTotalRowsExist = lngTotalRowsExist + 1
strCriteria = "[EmpName] ='" & rsADO2![EmpName] & "'" & _
" AND [PostCode] ='" & rsADO2![PCode1] & "'" & _
" AND [PostCode2] ='" & rsADO2![PCode2] & "'"
If rsADO1.EOF = True Then
lngTotalRowsMatched = lngTotalRowsMatched + 1
If lngTotalRowsMatched > 0 Then
strMsg = lngTotalRowsMatched & " rows of " & _
lngTotalRowsExist & " total rows in the " & _
"import file were matched in the master file"
' Close the ADO connection
Set conn = Nothing
And before anyone asks, yes I do have a reference to ADO in the References list.
I suspect that you could probably use a query for most of this, in stead of a two-recordset approach (note, the below is just typed, not tested)
I'm creating an import/export application to import data from a spreadsheet and export it into a SQL Server database, although I need to check for duplications before I export the data out, and I'm thinking I may have to split the name up as sometimes the names already inputted may be similar but not exactly the same.
Thanks, I will have a look into your suggestion instead and see how it goes.