recordcount crashes Access 2007 (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 04:30
Joined
Oct 30, 2008
Messages
1,257
Code:
Private Sub btnProcess_Click()

    Set cnn = New ADODB.Connection
    Set rx = New ADODB.Recordset
    Dim sql As String, sq2 As String
    Dim r As DAO.Recordset
    sq2$ = "SELECT CDTRacks.TTrack, CDTracks.TSingle, CDTracks.TTitle"
    sq2$ = sq2$ & " FROM CDTRacks"
    sq2$ = sq2$ & " WHERE (((CDTRacks.TCat)=" & ThisDisk()
    sq2$ = sq2$ & ")) ORDER BY CDTRacks.TTrack;"
    
    Set r = CurrentDb.OpenRecordset(sq2$)
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "\\JOHN\Desktop\Build11.mdb"
        cnn.Open
            r.MoveFirst
            Do
                sql$ = "SELECT AComment, Title FROM tblMain4"
                sql$ = sql$ & " WHERE Title = "
                sql$ = sql$ & Chr$(34) & r!TTitle & Chr$(34)
    
                With rx
                    .Open sql$, cnn, adOpenStatic, adLockReadOnly
                    'Debug.Print .RecordCount
                    .Close
                End With
                r.MoveNext
            Loop Until r.EOF
        cnn.Close
    r.Close
    Set r = Nothing
    Set rx = Nothing
    Set cnn = Nothing
    
    MsgBox "Done"
End Sub
Seems consistent but can't see why. If I un-rem 'Debug.Print .RecordCount
that's then end of it. Otherwise gets to "Done". Any suggestions?
 

June7

AWF VIP
Local time
Today, 07:30
Joined
Mar 9, 2014
Messages
5,425
Instead of Chr(34), I use apostrophes for text delimiters:

sql$ = sql$ & " WHERE Title = '" & r!TTitle & "'"

Is Build11.mdb db with this code? If yes, can use CurrentProject.Connection for the ADO recordset.

I really don't see why the code would crash on Debug.Print. Works for me.
 
Last edited:

kirkm

Registered User.
Local time
Tomorrow, 04:30
Joined
Oct 30, 2008
Messages
1,257
Build11.mdb is a separate DB on a networked drive. But it is pathed correctly and present.
Sometimes Title contains an apostrophe and then I hit a snag.. yes? Once I get this working I'll try using parameters.
Works for you? Will have to delve deeper.. but no error msg, just dies. Darn !
 

June7

AWF VIP
Local time
Today, 07:30
Joined
Mar 9, 2014
Messages
5,425
I handle possible apostrophes in data with:

Code:
Replace(r!TTitle, "'", "''")

Doubling a special character 'escapes' it so the SQL will recognize it as a regular character.
 

kirkm

Registered User.
Local time
Tomorrow, 04:30
Joined
Oct 30, 2008
Messages
1,257
Understood June, thanks.
I've proved the Networked drive is the problem... or at least if I move the mdb file to the same folder and change to Data Source= "Build11.mdb" then it all works ok.
Does anyone know why the net path fails and/or whyAccess crashes?
 

Minty

AWF VIP
Local time
Today, 15:30
Joined
Jul 26, 2013
Messages
10,355
I'll ask a daft question, without the debug, how do you know it's actually working ?

The rx recordset isn't actually doing anything so it may well not be working and you wouldn't know it. Try adding

Code:
rx.CursorLocation = adUseClient

to your code before you open the recordset.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 28, 2001
Messages
27,003
I'll point out that when you do a .Open on a recordset, the variable has to first be SET. You are opening a recordset on an object (which technically is treated as an address of a data structure) but have not properly implemented a Set Rx= ... to initialize it as a pointer. I'm surprised at "crashes Access" but wouldn't have been surprised if you said "signals an error." On the other hand, bad addresses are notorious for crashing programs in mini-computers AND mainframes.
 

kirkm

Registered User.
Local time
Tomorrow, 04:30
Joined
Oct 30, 2008
Messages
1,257
I finally sorted it (after 2 days!) Doc, I think you're wrong about the Set, unless I'm missing something. The problem was my Provider string, I was using JET OLEDB.4 and after changing to ACE OLEDB.12 it worked ok.
Tried that addition too, Minty, didn't seem to do anything. The Open & Close operation without error had me thinking it should return a record count.

Thanks for the replies. Nice to have other ideas.
 

June7

AWF VIP
Local time
Today, 07:30
Joined
Mar 9, 2014
Messages
5,425
Glad you worked it out. Sorry I didn't make connection between using Access 2007 and the connection declaration:eek:
 

kirkm

Registered User.
Local time
Tomorrow, 04:30
Joined
Oct 30, 2008
Messages
1,257
Thanks June ... onto next problem !
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 28, 2001
Messages
27,003
After doing some research, I cannot find anything definitive on mandatory use of SET for ADO recordsets. I have noted in this research that there is a difference in the way ADO and DAO objects maintain their internal reference counts, which may be related to the use of the SET verb.

In any case, if this code ever pops out an Error 91, your solution will involve the SET verb. If it works, great.
 

Users who are viewing this thread

Top Bottom