Adding to a Recordset using VBA (SQL Server Backend)

[primagic]

I am trying to add a record to a recordset using VBA with the following code:

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim var As Variant
    Set db = CurrentDb
    Set rs = db.OpenRecordset("NextTaskCriteria")
    
    
    
        
    For Each var In Me.lstSources.ItemsSelected
        With rs
            .AddNew
            .Fields("Source") = CLng(Me.lstSources.Column(0, var))
            .Fields("EmployeeID") = Me.SelectedEmployeeID
            .Update
                 
        End With
    Next
    
    MsgBox "Updated successfully...", vbInformation
    
       
    rs.Close
    db.Close
    
    
        
    Set rs = Nothing
    Set db = Nothing

However, its stops on the first line with the error User-defined type not defined.

I am using an SQL backend, would there be different code to update or add to a recordset?

 

[MajP]

Your error is because you do not have a reference to
Data Access objects
Go to your references and add it.

The error comes because since you do not have a reference it thinks you are defining an object type.

There are two competing object models
DAO and ADO

From Access you can use either. DAO is older and more optimized for Access, ADO is general and more robust. Outside of Access you would have to use ADO to interface with Sql Server.

Dim rs As DAO.Recordset
Dim rs1 As ADODB.Recordset

The two above recordsets are completely different with many different properties, and methods.

One thing a forms recordset
set rs = forms("formName").recordset
returns a DAO recordset

If you are going to be doing a lot with your sql server backend you may want to go to ADO so you can port your code into other vb,vb.net front ends.

 

[MajP]

Also my guess is you are using Access 2000

Prior to A2K the default reference was
DAO
Then in 2000 there was this belief that ADO would replace DAO, and DAO would be deprecated. Probably had to do with the move to .Net.
But although ADO provides a ton of capability, DAO is so optimized for Access that it was clear that there was no desire to give up DAO.
So in A2003 I believe Access included both references as defaults.
And I think in 2007 they went back to DAO being the default reference.

 

[primagic]

Ok. I thought I had the reference checked but I didnt. Now I get an error Object variable or With Block Variable not set.

It stops on the line:

Set rs = db.OpenRecordset("NextTaskCriteria")

 

[primagic]

I am using 2007

 

[MajP]

if this is what you mean by first line:
Dim db As DAO.Database

Then check the reference to DAO.

 

[primagic]

yes i checked reference and it wasn't set so I checked it. Now i get the error Object variable or With Block Variable not set on the following line:

Set rs = db.OpenRecordset("NextTaskCriteria")

 

[MajP]

I think you can use both, but maybe I am wrong
You could try going with ADO
Dim rs As New ADODB.Recordset
rs.Open "NextTaskCriteria", CurrentProject.Connection, adOpenDynamic

But try this first.
set rs = currentdb.OpenRecordset("NextTaskCriteria",dbopendynamic)