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
The first statement opens a table-type recordset if that table is a local Access table (because the argument passed is a table,
dbOpenTableis the default)
The second statement opens a dynaset-type recordset (because the argument passed is a query,
dbOpenDynasetis 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