using recordset on linked tables

[rana]

hi,
is it possible to use recordset on a linked table. I have this, yes mundane i know,:
dim db as database
dim rstRequests as recordset
set db = currentdb
Set rstRequests = db.OpenRecordset("Requests", dbOpenTable)
With rstRequests
.Index = "PrimaryKey"
.Seek "=", Item_ID
.Edit
![Returned?] = True
.Update
MsgBox "Item has been returned.", vbInformation
End With

But it stops at "Set rstRequest = db.openrecordset ..."
and tell me that it is an invalid operation. Is there any way to change records on a linked table? I thought you could do it since you aren't actually changing the design. hmmm.

 

[DougP]

I pasted your code in Access '97 database I had and created a Table and a few other things so it would work.
And it did work OK.

If you are using Access 2000 you have to add References to VBA cuase it don't know what you are doing anymore.
Access '97 had them built, in 2000 VBA you have to turn them on. Stupid I know.

Open your code window in VBA then click tools, references
I use "Microsoft DAO 3.6"

 

[cmmrfrds]

Once you have added your refernce in Access 2000, then you can use DAO or ADODB record sets.

ADODB syntax

Dim RSMT As New Recordset, cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
RSMT.Open SqlString, cnn, adOpenStatic, adLockReadOnly

DAO syntax

Dim RSMT As DAO.Recordset, dbs As DAO.Database, SqlString as String
Set dbs = CurrentDb
SqlString = "put your sql string in here"
dbs.Execute SqlString

I have been using ADODB since it is the recommended Microsoft way, but I use DAO when I need to do a recordsetclone.

Either works fine with linked tables. CHeck out help for additional syntax information.

Jerry

 

[TheRealDeal]

In short though, what would you consider to be the best ADO opening of recordsets?