Equivalent Recordset code for 2003 vs Access 97?

[Terri58ds]

Hi folks, I'm new to your forum and need some help figuring out the 2003 equivalence to the 97 recordset code I am used to.

I would like to add, update, delete data to my tables using recordsets.
I learnt Access 97 at college 10 yrs ago and haven't used it for many years. Now I have to do a project at work using Access 2003. I find that code syntax from old college projects doesn't work in 2003. From what I've read it is because of a change from DOA to ADODB

This is a typical example of what I would do on Access 97
What is the equivalent code to do this in Access 2003?

This example is an unbound form for tracking projects, and has a combo box to lookup the project then display its data. This code updates the changes from the textboxes to the table.

Private Sub cmdUpdate_Click()
Dim rstSET As Recordset, dbDataBase As Database 
Dim strcriteria As String, intCustomerNumber As Integer
Dim intAnswer As Integer
 
    Set dbDataBase = CurrentDb()
    Set rstSET = dbDataBase.OpenRecordset("Projects", dbOpenDynaset)
    strcriteria = "ProjectNumber =" & ProjectNumber
    rstSET.FindFirst strcriteria    'Find Project
    If rstSET.NoMatch Then
        MsgBox "No Such Project..."
    Else
        'Prepare the current record for editing
    rstSET.Edit
        'make the necessary changes to the record
        rstSET!ProjectName = ProjectName
        rstSET!ProjectedCost = ProjectedCost
        rstSET!StartDate = StartDate
        rstSET!ProjectedEndDate = ProjectedEndDate
        rstSET!Status = Status
        rstSET!EndDate = EndDate
        rstSET!ProjectDescription = ProjectDescription
    'Save the changes to the current record
    rstSET.Update
    End If
rstSET.Close
dbDataBase.Close
Exit_Rtn:
ProjectNumber.SetFocus
Exit Sub
End Sub

I have scoured the forum here and bought 3 books a week ago:
Special Edition Using MS Office Access 2003 / Roger Jennings/Que ;
Beginning Access 2003 VBA / Denise Gosnell/ Wrox ;
Access 2003 Inside Out/ John Viescas/ Microsoft ;

They all have snippets of code referring to the ADODB which have things like :

Dim cnConnection as ADODB.Connection
Dim rsRecordset as ADODB.Recordset
Set rsRecordset = New ADODB.Recordset
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.mdb;"
 
etc..

but they don't have an example similar enough for me to figure out how to do it. The books also vary how and where they write the Dim cnConnection type info so it's a bit confusing.

Any help is much appreciated in advance

TerriAnn

 

[pbaldy]

You can still use your DAO code. First make sure the DAO reference is checked in Tools/References, then tweak your code:

Dim rstSET As DAO.Recordset, dbDataBase As DAO.Database

 

[Terri58ds]

Thanks Paul.
Aha! That's why my 97 project converted to 2000 still works (DOA 3.6 is checked) but the new project (DOA not checked) doesn't accept the same code.

However, the one book mentions that DOA only works with Jet engines, whereas ADO also works with SQL as well as stating ...
"...if you expect your aplication might one day upsize to anActiveX data engine such as SQL you should consider using the ADO architecture as much as possible."
As SQL is in our foreseeable future, I would prefer to do the code right first time.
Any comments?

TerriAnn

 

[boblarson]

Actually, a few things to note:

1. DAO will still work with databases using SQL Server. It won't work if doing direct work (stored procedures, etc.) but it will work with linked tables.

2. New projects take on the default references of the program that it is created in. The defaults as far as DAO and ADO are these:

Access 97 - DAO default (and ADO not available as it didn't exist prior to 2000)
Access 2000 - ADO is the default
Access 2002 - ADO is the default
Access 2003 - Both ADO and DAO checked by default in new projects
Access 2007 - DAO checked by default.

 

[LPurvis]

>> "anActiveX data engine"

What?? That's just crazy talk.
I'll be sure to save that information to my Ergonomic Hard Disk and look at it on my Floppy Monitor. :-s

OK - to be fair they're probably refering to an engine which is exposed via an OLEDB provider... (But they should say that then shouldn't they).


DAO is technically able to directly access SQL Server data (well - it used to be able to, prior to 2007 when they removed ODBCDirect from DAO, and it sort of did so directly).
I do agree with your book's statement that ADO is more appropriate.
MS (while bigging up DAO once again) would no doubt advocate linked tables and using passthroughs to work with server data.
Alas I can't quite bring myself to fall in line on that one.
Nothing wrong with them at all as a technology (linked tables are handy and easy and PT's are actually very good - and offer a very efficient way to perform bulk imports to local tables) but executing your requests on the server is hard to beat.

But the old adage that you must update to ADO from DAO to stay with the times - is well behind the times. lol
That ADO is easily capable of working with server data (and indeed, by its very nature, almost any datasource) doesn't mean you can necessarily easily jump.
If you've created an application designed against a Jet BE you'll usually have some work to do to switch to server (unless you're using linked tables).
(Or unless you've put a lot of thought and effort into deliberately making it easy to switch).
ADO effectively is an API which wraps the OLEDB Providers. While it's not difficult to learn (it's OM has only the three main objects in the hierarchy to learn right away) - it can be much more complex than DAO in its own way.
You'll want a decent tutorial on it though - a book most likely perhaps?
AFAICR it was the absolute all time classic Access Developers Handbook which first introduced me to ADO. (The 2000 edition should explain the transition nicely - and any edition in the series is just... well... priceless).
Oh - and if you're wanting a more modern book than I plug my mate's writings lol.
Access MVP Brent Spaulding wrote the DAO and ADO chapters of "Microsoft Office Access 2007 VBA" so they will be good. (I've still not read all of them yet... sorry Brent!)

 

[Terri58ds]

Thanks to all for your help.

Paul: I've checked DOA and tweaked my code as you suggested. This is great as I can get up and running fast by grabbing bits of old code.

Bob: Thanks for that explanation which helps a lot. My new project was actually started on my home machine (Access 2000) hence only the ADO was checked; now I'm continuing it at work (Access 2003) where I converted to 2002-2003 format.

LPurvis: re: that's just crazy talk. Hey, what do I know? That's why I'm here, asking people who can give intelligent replies instead of just sucking up what the book says.
Thanks for the other info which I'll read 20 times to digest.
When I upgrade to 2007 I'll keep a lookout for Brent's book. Last week I basically dashed into Chapters in a panic and bought a copy of each of the 3 books they had for 2003. Luckily I can expense the ~$150.