OpenRecordset on Linked Table

[Randy]

I am trying to use OpenRecordset on a linked table with
the dbOpenTable option. The help file states that this
will produce a runtime error, but doesn't really give any
viable alternatives. The procedure is designed to delete a
record from the table in question. It works if the table
is within the same .mdb.

I have split the database so that tables are in a
separate .mdb, hence the link.

Below is the procedure I've used.

Private Sub lstCatResend_DblClick(Cancel As Integer)

Dim rstCatResend As DAO.Recordset
Dim var1 As String


var1 = Me!lstCatResend.Column(0) 'set var1 to PopID

Set rstCatResend = CurrentDb.OpenRecordset _
("tblVCXPlusCatalystResend", dbOpenTable)
rstCatResend.Index = "PopID"
With rstCatResend
.Seek "=", var1
.Delete
End With
rstCatResend.Close
Me!lstCatResend.Requery

End Sub

 

[Juan M. Afan de Ribera]

Michel Walsh had a good idea

http://www.mvps.org/access/tables/tbl0006.htm

HTH

 

[Marshall Barton]

I am trying to use OpenRecordset on a linked table with
the dbOpenTable option. The help file states that this
will produce a runtime error, but doesn't really give any
viable alternatives. The procedure is designed to delete a
record from the table in question. It works if the table
is within the same .mdb.

I have split the database so that tables are in a
separate .mdb, hence the link.

Below is the procedure I've used.

Private Sub lstCatResend_DblClick(Cancel As Integer)

Dim rstCatResend As DAO.Recordset
Dim var1 As String


var1 = Me!lstCatResend.Column(0) 'set var1 to PopID

Set rstCatResend = CurrentDb.OpenRecordset _
("tblVCXPlusCatalystResend", dbOpenTable)
rstCatResend.Index = "PopID"
With rstCatResend
.Seek "=", var1
.Delete
End With
rstCatResend.Close
Me!lstCatResend.Requery

End Sub

The more common approach is to use a Dynaset recordset and
FindFirst instead of a table type recordset with Seek.

Set rstCatResend = CurrentDb.OpenRecordset _
("tblVCXPlusCatalystResend", dbOpenDYNASET)
With rstCatResend
.FindFirst "PopID = " & var1
If Not .Nomatch Then .Delete
.Close
End With

If you really have a good reason to use Seek, then you have
to open the other database before opening the recordset:

Dim dbBE As Database
Dim strBEPath As Path
strBEPath = Mid(CurrentDb.TableDefs( _
"tblVCXPlusCatalystResend").Connect, 11)
Set dbBE = OpenDatabse(strBEPath)
Set rstCatResend = CurrentDb.OpenRecordset _
("tblVCXPlusCatalystResend", dbOpenTABLE)
With rstCatResend
.Seek "=", var1
If Not .Nomatch Then .Delete
.Close
End With
dbBE.Close : Set dbBE = Nothing

 

[Randy]

Your response was just what I needed. thanks!

-----Original Message-----


Actually, the OpenTable option (with its Seek and Index methods) is only
really there for people who never made the step up from file managers like
dBase.

The R approach is to use the server to send you only the records you really
want in the first place, or preferably not to send any at all.

strSQL = "DELETE FROM tblVCXPlusCatalystResend " & _
"WHERE Pop1 = " & Quoted(Var1) & ";"

db.Execute strSQL, dbFailOnError

IMO, it's just unfriendly to everyone on the same network to transfer an
entire table (what, 500b per record, 15000 records..?) simply to remove one
record and then send all 14999 back again. When the same job could be done
safer and faster with fewer than 100 bytes.

Just a thought...


Tim F

.