MS ACCESS 2000 DATABASE,USING TABLES WITH VISUAL BASIC

[HUGH]

WHAT V.B. CODE DO I REQUIRE TO: OPEN A TABLE AND READ EACH RECORD
SEQUENTIALLY TO THE END?

 

[Ofer]

Without shouting

Open a recordset and loop through the records

Dim MyDB as Database, MyRec as Recordset
' Open the mdb
Set MyDB=CodeDb()
' Open the table
Set MyRec=MyDb.OpenRecordset("Select * From TableName")
' Loop
While not MyRec.Eof

(The loop place)

MyRec.MoveNext
Wend

While in code, Select in the menubar > tools > reference
See if you have reference to Microsoft DAO 3.6, if not add it, or youll get
error message.

 

[Tom Wickerath]

Hi Ofer,

I'd just like to make one small suggestion for improving your code. Use:

MyRec as DAO.Recordset

instead of MyRec as Recordset. This way, you avoid run-time errors
associated with library reference priority. If the default reference for the
ADO library is higher in priority than the DAO library that you had the user
add, the code will still compile, but you'll get a run-time error. More
information here:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/gem_tips1.html

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Without shouting

Open a recordset and loop through the records

Dim MyDB as Database, MyRec as Recordset
' Open the mdb
Set MyDB=CodeDb()
' Open the table
Set MyRec=MyDb.OpenRecordset("Select * From TableName")
' Loop
While not MyRec.Eof

(The loop place)

MyRec.MoveNext
Wend

While in code, Select in the menubar > tools > reference
See if you have reference to Microsoft DAO 3.6, if not add it, or youll get
error message.

 

[Ofer]

Thanks Tom, but this is why I added in the end that he need to add the
reference to DAO 3.6, or he will get an error message.

I rather add the reference once, and then save time typing the DAO everytime
I open a recordset.

 

[Tom Wickerath]

Hi Ofer,

Try running your code with the default reference to the ADO library
included, and set higher in priority that the DAO library. I think you will
see what I mean if you try this experiment. The modification that I suggested
does not mean that one can avoid adding the DAO library as a checked
reference; this is still a requirement.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thanks Tom, but this is why I added in the end that he need to add the
reference to DAO 3.6, or he will get an error message.

I rather add the reference once, and then save time typing the DAO everytime
I open a recordset.

 

[Dirk Goldgar]

Without shouting

Open a recordset and loop through the records

Dim MyDB as Database, MyRec as Recordset
' Open the mdb
Set MyDB=CodeDb()
' Open the table
Set MyRec=MyDb.OpenRecordset("Select * From TableName")
' Loop
While not MyRec.Eof

(The loop place)

MyRec.MoveNext
Wend

While in code, Select in the menubar > tools > reference
See if you have reference to Microsoft DAO 3.6, if not add it, or
youll get error message.

In addition to Tom Wickerath's comments, I think you'd be safer to use
CurrentDb() instead of CodeDb():

Set MyDB=CurrentDb()

CodeDb() will work so long as the code being executed is in the database
that contains the table(s) to be processed -- which admittedly is most
of the time -- but not if the code is in an add-in or other database
that is external to the current project.

 

[Ofer]

Hi Dirk, I work with several mdb's connected to each other by reference, for
some reason all the mdb's that on second level of reference the currentdb
gave me an error, so I had to change all of them to codedb, which worked
great, so I assume there are pluses and minuses for each thechnique.
So you can call it a habit, that I write my code that way

 

[Dirk Goldgar]

Hi Dirk, I work with several mdb's connected to each other by
reference, for some reason all the mdb's that on second level of
reference the currentdb gave me an error, so I had to change all of
them to codedb, which worked great, so I assume there are pluses and
minuses for each thechnique.
So you can call it a habit, that I write my code that way

So lonjg as you understand the difference between CurrentDb and CodeDb.