Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-11-10
7
Medium Priority
?
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 39

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 51

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 51

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

660 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