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 von Asmuth

    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