Seek, but no NoMatch?

[rberkers]

Hi everyone,

Today I did run into a weird problem. For the first time I wanted to use the "Seek" function in a small program and test the result with ".NoMatch".

The strange thing is that after the Seek-function, in the new code line of the name of the recordset "NoMatch" was not among the properties I could select. These are the two lines of code (see for the compete code below):

    rstMerchant.Seek "=", intFindID
    If rstMerchant.NoMatch Then  .....

When I typed "nomatch" manually, Access seems to recognise it (it turned "nomatch" nicely to "NoMatch"), but this is maybe because of the DAO library? But on compiling it doesn't recognise the "NoMatch" property.

So what am I doing wrong? If someone could help me here, that would be great.

Thanks in advance,
Rob.

    Dim blnMainLoop As Boolean
 
    Dim intFindID As Integer
 
    Dim strSQLAccount As String
    Dim strSQLMerchant As String
 
    Dim cnnAccount As ADODB.Connection
    Dim cnnMerchant As ADODB.Connection
    
    Dim rstAccount As ADODB.Recordset
    Dim rstMerchant As ADODB.Recordset
    
    ' ==================================================================================================
 
    strSQLAccount = "SELECT [Contract-id] FROM [103 Account] UNION ALL " _
       & "SELECT Account_ID FROM [161 Reserved Account-Nrs] ORDER BY [contract-id]"
 
    Set cnnAccount = New ADODB.Connection
    cnnAccount.Open CurrentProject.BaseConnectionString
    Set rstAccount = New ADODB.Recordset
    rstAccount.Open strSQLAccount, cnnAccount, adOpenKeyset, adLockOptimistic
    
    strSQLMerchant = "SELECT [Client-id] FROM [100 Merchant] UNION ALL " _
       & "SELECT [Client_ID] FROM [163 Reserved Client-Nrs] ORDER BY [Client-ID]"
    Set cnnMerchant = New ADODB.Recordset
    cnnMerchant.Open CurrentProject.BaseConnectionString
    Set rstMerchant = New ADODB.Recordset
    rstMerchant.Open strSQLMerchant, cnnMerchant, adOpenKeyset, adLockOptimistic
 
    blnMainLoop = True
    intFindID = 0
    
    rstMerchant.Index = "Client-ID"
    
    rstAccount.MoveFirst
    Do While (rstAccount.EOF = False) And (blnMainLoop = True)
        
        If (intFindID = 10000) Then
        
            MsgBox "No more free accountnumbers available!!!!", vbCritical
            blnMainLoop = False
            
        Else
            
            If (rstAccount![Contract-id] - intFindID) > 1 Then
                intFindID = intFindID + 1
            
            rstMerchant.Seek "=", intFindID
            If rstMerchant.NoMatch Then
                Debug.Print "Should be free: ", intFindID
                blnMainLoop = False
            End If
            
        End If
    
    Loop
    
    rstMerchant.Close
    Set rstMerchant = Nothing
    cnnMerchant.Close
    Set cnnMerchant = Nothing
    
    rstAccount.Close
    Set rstAccount = Nothing
    cnnAccount.Close
    Set cnnAccount = Nothing

 

[DCrake]

As far as I am aware, an stand to be corrected, but I thought you could only use .seek on an unsplit database.

 

[LPurvis]

You can use the Seek method on any recordset which supports it.
In DAO this means that the recordset must be a dbOpenTable type. This would indeed usually mean a non-linked table. But you can, of course, open a database object to the MDB/ACCDB in question and then a table type recordset on that.
This means the source of the recordset must also be a table name - not a SQL statement or query.


Similarly, as this is ADO, you must specify a adCmdTableDirect type recordset - and a single table.
If [163 Reserved Client-Nrs] is a table then you stand a chance of achieving this.
If it's a linked table then you'll need to open a connection to the target DB - completely analogous to DAO.

Note that you actually open a connection to the current database. There's no need for this. i.e. instead of
cnnMerchant.Open CurrentProject.BaseConnectionString
you would just use
Set cnnMerchant = CurrentProject.Connection
No need for mutliple connections to the local file. It'll just add overhead.
Where you have
strSQLMerchant = "SELECT [Client-id] FROM [100 Merchant] UNION ALL " _
& "SELECT [Client_ID] FROM [163 Reserved Client-Nrs] ORDER BY [Client-ID]"
rstMerchant.Open strSQLMerchant, cnnMerchant, adOpenKeyset, adLockOptimistic
you'd really now want
strSQLMerchant = "[163 Reserved Client-Nrs]"
rstMerchant.Open strSQLMerchant, cnnMerchant, adOpenKeyset, adLockOptimistic, adCmdTableDirect
IF "[163 Reserved Client-Nrs]" is a table local to the currently open project.

Note also that ADO has no NoMatch property of the recordset. Instead a Seek which fails to find a match will position the recordset at the end of the records (i.e. .EOF = True)

Given that this doesn't look local table oriented, it seems more likely that you'll need to use another method than Seek as David mentioned.
In DAO this would be FindFirst (with which you can use NoMatch).
In ADO that would be Find.
Neither of these methods requires an Index to be specified. (Though having an index on that field should still be beneficial).
However FindFirst can use a regular WHERE clause-like expression, whereas Find accepts only a single field criteria. (But that shouldn't be a problem here).
Once again - you'll check for a match with Find by examining the EOF property afterwards. Note also that you specify the position from which you want to commence the Find. So get back to the start of the recordset if that's where you want to start from. ;-)

FWIW ADO's Filter method is excellent and quite efficient. So when more than one criteria is required - Bob's yer uncle with that.

Cheers.

 

[rberkers]

Woot. That is a lot of useful information. This weekend I will experiment with it all.

Thank you so much.

Regards,
Rob.

 

[gemma-the-husky]

dont you need to define which index you want to use, with seek?

or does it use the PK by default

 

[LPurvis]

You do indeed need to specify the index to use. Since Rob's example code already included that, I mentioned it only in the context that FindFirst and Find don't need it ;-).