Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-11-10
7
Medium Priority
?
61 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 248 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 40

Assisted Solution

by:PatHartman
PatHartman earned 248 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 46

Assisted Solution

by:aikimark
aikimark earned 248 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Assisted Solution

by:nathaniel
nathaniel earned 248 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 52

Accepted Solution

by:
Gustav Brock earned 1008 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 52

Expert Comment

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

916 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