iterating a table

[jacks]

a probably embarrassing simple question..

I have made a form and placed a button on it. At onclick() I want to iterate through a table named "match".

I'm using access 2003.

I need RecordSet I suppose. And commands like MoveFirst, MoveNext I guess..

Can someone give me a very simple example that must work ?
the table I want to iterate through is called : "match" and one of it's columns is called "pnts" which stands for points.

I want to iterate through the list and proof it's working with a command like print.debug pnts

Do I need to open a database first ? the database name is "vbal".

I'm confused with all the ADO and DAO stuff and differences.. pls help.

 

[gemma-the-husky]

dim rst as recordset
set rst = currentdb.openrecordset "tblname"

rst.movefirst
while not rst.eof
.....do processing
.....rst!fieldname etc
.....rst.movenext
wend

 

[jacks]

I have a table with the name : "wedstrijd"

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset "wedstrijd"

as you explained me to do.

I get a compile error.
expected: end of statement

okay, I changed it to :

Set rst = CurrentDb.OpenRecordset ("wedstrijd")

just to be sure, I'm setting a breakpoint on the next line..
rst.MoveFirst

so, I'm clicking the button again.. and I expect to arrive at the breakpoint without problem.. wrong.

I get an error message first. SIGH.

Runtime error '13'
type mismatch.

okay.. there's a help button on the error message.. and the help text begins.. :
Visual Basic is able to convert and coerce many values to accomplish data type assignments that weren't possible in earlier versions. However, this error can still occur and has the following causes and solutions: etc.etc..

What am I doing wrong ?


When I choose the Debug button on the error message the line
Set rst = CurrentDb.OpenRecordset ("wedstrijd") gets highlighted in yellow with a yellow arrow in front of it. Grrrrrr...

wedstrijd is an existing table.. I'm very sure of that.

 

[jacks]

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tablename", CurrentProject.Connection

this worked. phewww!

 

[Moniker]

Make sure you understand the difference between DAO and ADO as well. DAO is Data Access Object, and is still around for legacy purposes. ADO is Active Data Object, and is the preferred method. ADO is slightly more complex because it allows for transactions and such, but it's also a lot more powerful. Get used to using ADO in future programming as it will perform a lot more tasks.

 

[boblarson]

DAO is Data Access Object, and is still around for legacy purposes. ... Get used to using ADO in future programming as it will perform a lot more tasks.

Gotta disagree with you Moniker on this one. I know that the original plan of MS was to do that, but they have since relented and have even gone back to setting DAO as the default for Access 2003 and above.

 

[jacks]

It took me some time to figure out how to iterate through a table. I'm using Access 2003. I'm not very interested in ADO vs DAO discussion.. I just need the thing to work. And it's frustrating that it's so difficult to get it going.