Solved

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

Posted on 2016-11-10
7
46 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 35

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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