Working With Access VBA Recordsets Applying DAO or ADO: 3 Crucial Factors With VBA Recordsets

Listed here are 3 critical factors to be aware and be conscious of when coding with Access VBA making use of either DAO or ADO to link with other databases.

There are common traps and useful ideas to look out for when applying Entry VBA code operating with the recordsets item and in this posting (component 1) I will address the first three factors in this article and now.

1. Which library to use DAO or ADO?

If you have been utilizing Entry VBA for a though, then you may possibly have occur across two library selections (DAO or ADO). Each libraries assistance the Recordset object but they have various customers (i.e. homes, strategies and situations).

DAO is the indigenous reference to Microsoft Access databases and is the all-natural selection when coding to other Obtain database methods as it an implicit link to tables in Access.

ADO having said that is used for other databases sorts outside the Microsoft Obtain framework when seeking to join to exterior facts sources and is considered the adaptable of the two.

There are professionals and drawbacks concerning the two library varieties but from time to time they can exist with each other in the identical databases surroundings which may well bring about some confusion. If this is the circumstance, the purchase they seem in the references listing requires priority. To avoid ambiguous referencing, the greatest apply (if you insist on owning both equally) is to be express in your coding. For case in point,

Dim rs as Recordset – can refer to either library but applying

Dim rsDAO As DAO.Recordset

Dim rsADO As ADODB.Recordset – will make equally objects specific.

2. Seeking to transfer concerning documents when there are no information

If you do not code to capture and take a look at the recordset object selection and you endeavor to navigate within this array, it will toss an error.

Techniques like MoveFirst, MoveLast, MoveNext, or MovePrevious will cause mistakes when no data have been located. Consequently, make confident you incorporate an If examination prior to iterating by documents applying a little something like:

If Not rs.BOF And Not rs.EOF Then

3. Recordset default Varieties can fluctuate involving desk and query connections

If you use the OpenRecordset system to a query or an attached desk (a desk not domestically saved), the default argument form is dbOpenDynaset compared to a local solitary desk which uses dbOpenTable.

If you have produced you Obtain databases in a stand-by itself surroundings in which the default is dbOpenTable and then break up the Access database into a entrance and again-stop atmosphere, the code will fall short to run.

Consequently adjust the default from dbOpenTable to dbOpenDynaset.For illustration:

Set rs = db.OpenRecordset(“MyLocalTable”, dbOpenDynaset)

I have other traps and strategies to be mindful of with Obtain VBA DAO and ADO and will publish these in my following post so maintain a search out!