CODEWITHSUNDEEP
vbams-accessadoadodbrecordset
Adib Akale
Adib Akale

Reputation: 105

Why and When do I use the New keyword when declaring a new recordset in Access VBA?

What is the difference between 

Dim rs as New adodb.recordset

and 

Dim rs as adodb.recordset

In what situations would I want to use the New statement or not use it? For context, I am working on a ADODB access application that connects to a SQL Server Back end.

Upvotes: 3

Views: 1171

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

The New keyword creates a new instance of an object, here of type ADODB.Recordset.

An object variable needs to have a reference before it can be accessed, so this:

Dim rs As ADODB.Recordset
rs.Open "SELECT * FROM TableName"

...will blow up at run-time with error #91 - "Object or With block variable not set". The important part of that message here is "not set".

When you do:

Dim rs As New ADODB.Recordset

You're really doing:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

The Set keyword is used to assign a reference to an object variable.

Specifically about ADODB.Recordset, I'd say you don't usually want to New up a recordset. Instead, you'll get one by running a parameterized ADODB.Command (e.g. when a command runs a SELECT statement, you'll receive a Recordset result).

I'd advise against using the As New shortcut in procedure scope. Mostly because of this:

Private Sub Test()
    Dim c As New Collection
    c.Add "Test"
    Set c = Nothing
    Debug.Print c.Count 'what happens here?
End Sub

If you think the above code blows up because c is not "set" anymore when c.Count is accessed, you got bitten.

This code behaves as expected:

Private Sub Test()
    Dim c As Collection
    Set c = New Collection
    c.Add "Test"
    Set c = Nothing
    Debug.Print c.Count 'what happens here? that's right, boom!
End Sub

Upvotes: 3

Related Questions