Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005

    Unanswered: Recordset Open Error

    Hi All,

    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
        Case "CRM"
            Call TableConfig(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] & "'"
                rsADO1.Find strCriteria
                If rsADO1.EOF = True Then
                    lngTotalRowsMatched = lngTotalRowsMatched + 1
                End If
            If lngTotalRowsMatched > 0 Then
                strMsg = lngTotalRowsMatched & " rows of " & _
                         lngTotalRowsExist & " total rows in the " & _
                         "import file were matched in the master file"
                MsgBox strMsg
            End If
    End Select
    ' Close the ADO connection
    Set conn = Nothing
    End Function
    And before anyone asks, yes I do have a reference to ADO in the References list.

    Anyone have any ideas?


  2. #2
    Join Date
    Nov 2004
    They aren't instantiated

    Set rsADO1 = New ADODB.Recordset
    Set rsADO2 = New ADODB.Recordset

    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)
    SELECT <columnlist>
    FROM dbo_tblEmployer e INNER JOIN ImportedData i ON
            e.EmpName   = i.EmpName AND
            e.PostCode  = i.PCode AND
            e.PostCode2 = i.PCode2

  3. #3
    Join Date
    Sep 2005
    Quote Originally Posted by RoyVidar
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts