Solved

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

Posted on 2016-11-10
7
40 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 3

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 34

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 49

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 49

Expert Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Auto Filter in Combo Box 7 30
Library not Registered 16 42
Excess tables to Excel BackUp 3 28
Format a Field AFTER UPDATE 5 18
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

929 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now