Retrieve data

[accessman2]

Hi,

I create the table1 in SQL Server and then link the table to MS Access using ODBC. But, when I write do this statement
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from table1")

for i = 1 to rs.recordcount
msgbox rs.field(0)
next i

It only retrieve 1 record only, but the table have 10 records.
What's wrong with it?
I check rs.recordcount, it only loop one time.

Additionally,
it doesn't work this function in the ODBC link table
rs.addnew
rs.field(0) = "hello"
rs.update

And, when we use ODBC link tables,
we cannot delete data in the table.
currentdb.execute "delete * from table1"

it will give out error message, the table is read-only.

Please let me know about it, thanks.

 

[Moniker]

You're not incrementing through the recordset here:

for i = 1 to rs.recordcount
msgbox rs.field(0)
next i

Add an rs.MoveNext in there.

~Moniker

 

[accessman2]

I added it,

for i = 1 to rs.recordset
msgbox rs.field(0).value
rs.movenext
next i

but, it still out 1 record only.

The table have 10 record lines.

I tried msgbox rs.recordset, it only give out 1.
So, it only loop one time.

Thanks.

 

[Moniker]

You have this:

for i = 1 to rs.recordset
msgbox rs.field(0).value
rs.movenext
next i

The For..Loop needs to be rs.RecordCount, not RecordSet. You had that right the first time, so maybe it was just a typo. Also, before you loop through the recordset, add MsgBox rs.RecordCount and make sure it's returning all the records. If it's not, force it to retrieve all the records by adding this:

.
.
Set rs = CurrentDb.OpenRecordset("Select * from table1")
rs.MoveLast
rs.MoveFirst
.
.
<The rest of the code here>

~Moniker