Error: set datPrimaryRS = New ADODB.Recordset

[DKasler]

Im getting an error on the Line

Set datPrimaryRS = New ADODB.Recordset

The error reads "Variable Not Defined" but I've used this same code on 10 or so different projects and NEVER had a problem before...

What gives?

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
    
    ' set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Referral\Referral Rebuild\referrals.mdb;Persist Security Info=False"
    sSQL = "select DrID, SpecialistType, SpecialistLastName, SpecialistFirstName, Address, Fax, Phone, Remarks from Specialists where SpecialistLastName like '" & Text8.Text & "'"
 
   ' open connection
    Set dfwConn = New ADODB.Connection
    dfwConn.Open sConnect
    
    ' create a recordset using the provided collection
    Set datPrimaryRS = New ADODB.Recordset
    datPrimaryRS.CursorLocation = adUseClient
    datPrimaryRS.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
 
    Set MSHFlexGrid1.DataSource = datPrimaryRS
    MSHFlexGrid1.Refresh
    
    Text9.Text = datPrimaryRS![SpecialistFirstName]
    Text10.Text = datPrimaryRS![SpecialistType]

[DKasler]

I dont know why this changed all of the sudden, but I just added

Dim datPrimaryRs As New ADODB.Recordset

and it seems to be working fine now.

[szlamany]

When you DIM - do not use the NEW.

When you SET, then use the NEW.

Did you used to have this as a global/module level declaration?

[demotivater]

szlamany:

Out of curiosity, what are the disadvantages of using new in the Dim statement and taking care of that there, eliminating the need for the set statement?

[szlamany]

From one of my favorite books - WROX publications - ADO 2.6 Programmers Reference.

Dim objRS as New ADODB.RecordSet

This creates the object reference immediately, but the object is not instantiated until the first property or method is called. This means that the object is not instantiated when declared but later in the code - which can lead to debugging problems. A better solution is the use this method:

Dim objRS as ADODB.RecordSet
Set objRS = New ADODB.Recordset

I've seen postings here on the forums where checking the object = Nothing didn't work properly unless the second method was used. Seemed to me to be in reference to .NextRecordSet

[demotivater]

Gotcha. Thanks.