Use of CurrentDb.OpenRecordset in VBA
I am editing something in VBA in MS access. Can someone please tell me the difference between below two statements.
1. Set rs1 = CurrentDb.OpenRecordset("tblOPCEPLTB")
2. Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblOPCEPLTB")
What I am guessing is 2nd line fetches all rows from table tblOPCEPLTB and store in rs variable. So what does 1st do? After executing 1st what should it have?
Please help here
1 answer
-
answered 2018-10-11 19:24
Erik A
The first statement opens a table-type recordset if that table is a local Access table (because the argument passed is a table,
dbOpenTable
is the default)The second statement opens a dynaset-type recordset (because the argument passed is a query,
dbOpenDynaset
is the default)The main difference between dynaset-type and table-type recordsets is that table-type recordsets support indexes.
The following code will succeed and look up the entry with a primary key value of 5
Set rs = CurrentDb.OpenRecordset("tblOPCEPLTB") rs.Index = "PrimaryKey" rs.Seek "=", 5
The following will fail, since seeks are only supported on table-type recordsets:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblOPCEPLTB") rs.Index = "PrimaryKey" rs.Seek "=", 5
If you only wish to append records to a table, then specify that when opening the recordset:
Set rs = CurrentDb.OpenRecordset("tblOPCEPLTB", Options:=dbAppendOnly)
This way, no records will get locked or loaded, since the recordset will only support appending