Working With Access VBA Recordsets Using DAO or ADO: 3 Key Points With VBA Recordsets
Right here are 3 essential factors to take note and be conscious of when coding with Entry VBA using possibly DAO or ADO to hook up with other databases.
There are widespread traps and useful suggestions to search out for when using Accessibility VBA code doing the job with the recordsets object and in this posting (portion 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 Access VBA for a when, then you may well have come across two library solutions (DAO or ADO). Equally libraries assist the Recordset object but they have distinct members (i.e. houses, strategies and situations).
DAO is the indigenous reference to Microsoft Entry databases and is the purely natural selection when coding to other Entry databases methods as it an implicit relationship to tables in Entry.
ADO nevertheless is utilized for other databases varieties outside the Microsoft Entry framework when wanting to hook up to external knowledge resources and is considered the versatile of the two.
There are execs and downsides amongst the two library kinds but often they can exist alongside one another in the identical database setting which may perhaps lead to some confusion. If this is the situation, the purchase they look in the references list usually takes precedence. To avoid ambiguous referencing, the ideal practice (if you insist on possessing each) is to be explicit in your coding. For illustration,
Dim rs as Recordset – can refer to both library but using
Dim rsDAO As DAO.Recordset
Dim rsADO As ADODB.Recordset – will make the two objects express.
2. Trying to transfer involving documents when there are no records
If you do not code to catch and exam the recordset item selection and you attempt to navigate in just this array, it will toss an error.
Procedures like MoveFirst, MoveLast, MoveNext, or MovePrevious will lead to glitches when no data have been discovered. Therefore, make positive you insert an If take a look at just before iterating by records employing some thing like:
If Not rs.BOF And Not rs.EOF Then
3. Recordset default Kinds can differ involving table and query connections
If you use the OpenRecordset system to a query or an attached table (a table not regionally stored), the default argument kind is dbOpenDynaset compared to a nearby solitary table which employs dbOpenTable.
If you have created you Entry databases in a stand-alone atmosphere where the default is dbOpenTable and then split the Access database into a entrance and back-close environment, the code will fall short to run.
As a result modify the default from dbOpenTable to dbOpenDynaset.For example:
Established rs = db.OpenRecordset(“MyLocalTable”, dbOpenDynaset)
I have other traps and ideas to be aware of with Access VBA DAO and ADO and will publish these in my next report so retain a glance out!
