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

    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

    Code:
    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
            
            rsADO2.MoveFirst
            Dim lngTotalRowsExist As Long
            Dim lngTotalRowsMatched As Long
            
            lngTotalRowsExist = 0
            lngTotalRowsMatched = 0
            
            Form_frmImport.txtProgress.SetFocus
            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
                
                rsADO1.MoveNext
            Loop
            
            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
            
            rsADO1.Close
            rsADO2.Close
    End Select
    
    ' Close the ADO connection
    conn.Close
    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?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    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)
    Code:
    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
    Roy-Vidar

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    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
  •