HELP! Access97 programmer can't grasp Access2000

[6kleg]

I'm embarassed to have to ask this. I've done extensive work in Access 97 because it's what my employer uses. I've resisted converting anything that I created to Access 2000 because I know I can't go back. But now I've reached the point where I want to make some stuff in 2000 because it's what I have, but I'm having trouble getting over the 2000 hump.

Specifically, when I need to open a recordset in a module in 97, I don't even have to think about typing these 4 lines:

Dim dbs as Database
Dim rst as Recordset
Set dbs = CurrentDB
set rst = dbs.OpenRecordset("tblMyTableName")

It's automatic.

Now that I'm ready to do some 2000 work, I can't seem to get the syntax for those 4 little statements right in my head. I want to use the Access 2000 standard ADO as though everything is being created from scratch. What should I write to replace those 4 statements?

I'll appreciate the help.

 

[RoyVidar]

Just keep using DAO, the only thing you need extra is
1 - ensure Microsoft DAO 3.6 Object Library is checked (in VBE - Tools | References)
2 - do explicit declaration of all DAO objects

[tt]Dim dbs as dao.Database
Dim rst as dao.Recordset[/tt]

Else, one way of obtaining a similar recordset in ADO could look like this:

[tt]dim rs as adodb.recordset
set rs=new adodb.recordset
with rs
.activeconnection=currentproject.connection
.cursortype=adopenkeyset
.locktype=adlockoptimistic
.open "tblMyTableName",,,,adcmdtable
end with[/tt]

Roy-Vidar

 

[6kleg]

Roy-Vidar

Thanks for the quick reply. That asnwers some wuestions and generates a few more.

1. Will setting the reference to 3.6 be required when the Access App is loaded on another computer?

2. What's up with all that ADO stuff. Why is the ADO thing so complicated? It looks the stuff you have to go through with straight VB applications for the connection.

 

[GingerR]

Hey there.

1. In order to ensure the references I put in my db's get to every user's machine, I paste this code (which I copied from TT years ago) into every db i create, and put it in a module which launches when the db starts up. It is equivalent to going to a machine where a reference is MISSING (and the user calls you with "some wierd error message"), manually unchecking and rechecking it:

Function FixUpRefs()
Dim r As Reference, r1 As Reference
           Dim s As String

           ' Look for the first reference in the database other
           ' than Access and Visual Basic for Applications.
           For Each r In Application.References
              If r.Name <> "Access" And r.Name <> "VBA" Then
                 Set r1 = r
                 Exit For
              End If
           Next
           s = r1.FullPath

           ' Remove the Reference and add it back.
           References.Remove r1
           References.AddFromFile s

           ' Call a hidden SysCmd to automatically compile/save all modules.
           Call SysCmd(504, 16483)
End Function

2. Relax. I only use DAO, and that took me years to begin using and months to become comfortable with. But I'm sure whenever I begin to use ADO, it will become as easy as DAO is for me now. I'm sure it will for you too. Copy a few lines and paste them where ever you need them...no biggie, right?

Have a good one--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[6kleg]

Thanks GingerR,

That seems like a good tip. Are you also saying I shouldn't bother with ADO. It sounds like everyone thinks it's too much trouble.

If DAO is still the thing to use, and all I have to do is include the 3.6 reference and put "DAO." in front of all my Database and Recordset declarations, then I should have no worries, right?

Does this mean that all of the Access programmers who aren't concerned about connecting outside the current database still use DAO?

 

[RoyVidar]

The reference will follow the application/db, so the answer to #1 is no (unless there's something wrong - note - be sure to apply a couple of service packs - the 2000 version without SP is quite buggy)

#2 those lines above is probably as natural and automatic for me, as the dao stuff is for you, though I try to reduce recordset approaches to the minimum, and focus on doing most of the stuff through executing queries.

DAO is a native Jet/Access library, so lot of stuff is faster through DAO than ADO, and also some things are possible in DAO, and not in ADO. ADO is a newer technology, designed to be able to connect to all kinds of databases.

In this thread I did a little "rant"? on DAO/ADO conversion thread702-992308, some of it related to your last question.

Roy-Vidar

 

[6kleg]

Roy_Vidar,

I read your rant. I think opinions are very useful. You stated that you made the conversion to ADO. In hindsight, and related only to Access, was it worth it?

I tried to help a guy a few years ago who was creating an application in VB6 with an Access database so that he could make an .exe (although he did not know how to code). I thought I could help him but I found VB6 to be such a different animal than VBA, we both gave up on it, but I did learn a little about connection strings.

I'm not against learning to write those extra lines to be able to use ADO, but if I plan to stay with native tables in Access, it sounds like there is no benefit.

What's your opinion.

 

[MichaelRed]

Just to assure that the discussion is "interesting".

Beyond Ms. (in any / all of it's praiseworty and not-so praiseworty endeavours). Way back when (choose your situation), life was slower and, perhaps simpler. Going back just a couple of 'generations', the norm for 'workers' was to have one occupation and one job. Us low-lifers could often count on at least some retirement from our (working) life employer.

Today, the general expectation is to have several different carrears / each with multiple employers none of whom provide much (if any) residual benifits. Two weeks after your last 'pay check', the employer appears hard pressed to even rember your name, much less provide 'benefits'.

The 'moral of the story' seems to many to be the age old adage: Standing still is really falling behind. Loosely translated, I say that if you aren't learning the newest (whatever) today, hten five years from NOW you will not only be un-employed, you will be unemployable. Learn ADO or not, the choice is only what you will learn, not IF you will learn. Get into another profession, Get intimate with the welfare system, keep up with the technology of today and the future. There are the (generic) choices.





MichaelRed

 

[6kleg]

I can always count on a lively opinion from Michael Red.

Thanks.