Solved

Most effective way to insert a single record in an access table

Posted on 2016-11-10
7
56 Views
Last Modified: 2016-12-01
as the question says, what is the most effective way to insert a single record ? DAO, ADO or SQL??
Thanks
0
Comment
Question by:arcross
7 Comments
 
LVL 4

Assisted Solution

by:bfuchs
bfuchs earned 62 total points (awarded by participants)
ID: 41882944
re efficiency, for a single record it wouldn't matter unless you're doing in a loop thousands of times, however SQL is in general the way to go when dealing with database transactions
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 62 total points (awarded by participants)
ID: 41882989
I agree with bfuchs.  Efficiency is moot when you are dealing with a single record.  I chose based on how I am gathering the data to be inserted.  If it is a matter of copying a row from table a and inserting it into table b where the only arguments are "from FK" and "to FK", I will normally run an append query using DAO execute method.  If the insert is more complicated, I will probably build the record using VBA and insert it using the DAO .AddNew method.  Occassionally, I might use the DoCmd.OpenQuery or DoCmd.RunSQL methods to run a query but the DAO .execute is generally preferred.

I never use ADO since DAO is the Access default.  If you are operating on the Access database from a different platform such as .net, then using ADO is the way to go.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 62 total points (awarded by participants)
ID: 41883320
If you're inserting into a single local table, then use a DAO recordset object.  Keep it open until the program finishes.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 6

Assisted Solution

by:nathaniel
nathaniel earned 62 total points (awarded by participants)
ID: 41883383
Either of the three have the same effect. For consistency, if you are using DAO, then use DAO method, if ADO then use ADO.

The simplier though is SQL (thru Docmd.RunSQL) because this single statement is enough to execute an SQL Update or Insert command. No need to set database or recordset variables.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 252 total points (awarded by participants)
ID: 41883452
Don't know about "efficient", but DAO is always faster - between 8% and 45% - than calling SQL because DAO is the native and direct access to Access tables.

However, for a single record it has no importance as the execution time is only about 2.5 µs.
That is when using CurrentDb.Execute to run the SQL. Calling DoCmd.RunSQL is way slower than the other methods, two to five times slower.

So do as you prefer and what fits best to the scenario. When deciding, have in mind that error handling is better for DAO.

/gustav
0
 
LVL 8

Author Comment

by:arcross
ID: 41884566
Thank you everybody for your input :)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41908422
Nothing more to add as an answer.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question