Best way to insert Data (1 Viewer)

[FahadTiger]

Hi Expert..
What is the best way to insert data from unbound form into a table?

• By Run SQL
• By DAO.Recordset

Or is there something better than what I mentioned ?
thank you

 

[Uncle Gizmo]

Hi Expert..
What is the best way to insert data from unbound form into a table?

• By Run SQL
• By DAO.Recordset

Or is there something better than what I mentioned ?

Use a Bound Form...

 

[FahadTiger]

Use a Bound Form...

I Know that Sir ..but what the difference to the compiler

 

[Mike Krailo]

Generally speaking, CurrentDB.Execute(StrSQL) runs faster and is preferred over recordset but it all depends on what you are doing or needs to be done..

 

[The_Doc_Man]

For an insert, in order fastest to slowest

1. Bound table + save record as a form operation
2. SQL "INSERT INTO" based on dynamically creating an SQL string for a .Execute or a .RunSQL with the values placed in the string via concatenation
3. SQL "INSERT INTO" based on using Form("name")!control name references in the SQL to pick up from controls on that form
4. DAO operation where you have to do .AddNew, individually load values to fields in the created record, then do .Update as separate VBA statements

Our friend Isladogs (Colin) has done experiments on the differences between .RunSQL and .Execute, but I don't recall the details at the moment. On the other hand, if you are doing this from a form, it is a single-record case. I'm pretty sure that the difference between the cases will be minuscule for single-record operations. Your "wait" time for completion of ANY of these for the single-record case will be less then the time it takes you to click some button and then move your eyes to the screen. You are looking at a lot of human latency in form context.

You asked "what's the difference to the compiler?" The answer is "Not enough to worry about" (but probably also in the order I named). On the other hand, the run-time environment WILL care slightly more about the differences.

If you chose #2 AND pre-loaded a database object such as "Set MyDB = CurrentDB" then used MyDB.Execute for executing the SQL string, that WOULD be faster than if you directly used CurrentDB.Execute unless the SET statement were inside the code being timed.

 

[Uncle Gizmo]

Our friend Isladogs (Colin) has done experiments on the differences between .RunSQL and .Execute, but I don't recall the details at the moment.

I seem to recall that there was hardly any difference in the speed between the various methods.

 

[Uncle Gizmo]

Regarding the option:- "Insert into SQL" you might find my blog about it useful:-

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

 

[The_Doc_Man]

I seem to recall that there was hardly any difference in the speed between the various methods.

I concur, and my point was that for form-based operations, the difference should not be noticeable. This DOES assume we are not talking about using Access over a painfully slow network to a distantly shared back-end or SQL engine.

 

[Pat Hartman]

@FahadTiger
Access is a Rapid Application Development tool. If you don't want to use it's RAD features, you are far better off with a platform that has a smaller footprint and less overhead. The best feature of Access is bound forms even when the BE is ODBC. You just need a better understanding of the interaction between Access and the ODBC database.

 

[FahadTiger]

th

Regarding the option:- "Insert into SQL" you might find my blog about it useful:-

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

thanks Sir ..I saw it
but video2 not clear..So I think its very important

 

[FahadTiger]

The best feature of Access is bound forms even when the BE is ODBC

But I read in one site ..that the unBound Form is necessary For safe when we are use ODBC

 

[Uncle Gizmo]

Oops! I answered the wrong Question!

 

[The_Doc_Man]

But I read in one site ..that the unBound Form is necessary For safe when we are use ODBC

Like all programming methods, it is a matter of what you do with it. Can you point to the article? We might have criticisms or comments of that site's evaluation. ANYONE can have a bad experience with ANYTHING if they use it incorrectly or don't understand what they are doing.

ODBC was used by the U.S. Navy for one of their big medical scholarship applications, with an Access FE and ODBC to an SQL back end. I don't know the exact details because I usually only assisted with the front-end now and then. But the Navy always imposed security on just about everything they did, and the ODBC link didn't seem to bother them.

 

[Pat Hartman]

But I read in one site ..that the unBound Form is necessary For safe when we are use ODBC

Please post a link to the article so we can rebut it. Sounds like it was written by someone who doesn't know how Access works or even what it is. Most of the articles that pan "Access" are not actually referring to Access at all, they are panning Jet/ACE which are the database engines that Access uses to store its own objects.

Almost all my applications use an ODBC BE - SQL Server, Oracle, DB2, Sybase, and a couple of others -- whatever the client is using for his other applications, I use for his Access applications to keep his operation simple. I always develop with the intention of converting even if I neve have to so for my apps, I can convert them in less than an hour when the time comes.

Access is tightly integrated with Jet (.mdb)/ ACE (.accdb) and so you can do almost anything and get away with it. However when your forms are bound to ODBC databases, they need to be bound to queries rather than tables and the queries need criteria to limit the rows returned. Access forms are typically bound to tables and filtering is used to get the user to the record he is interested in. The object when the BE is ODBC is to get the server to do the heavy lifting so you never bring down an unfiltered recordset to filter locally, you always use criteria so the server returns only the record you want. This can be done by creating forms that offer multiple filters if you want to have a lot of options or if you only have one or two, you can add unbound combos or textboxes to the form's header and use those for filtering.

The important thing about bound forms regardless of whether the BE is Jet/ACE or ODBC is to have an understanding of how form events give you control over whether records get updated. The most important form level event is BeforeUpdate. Think of it as the flapper at the bottom of a funnel. If the flapper is open, the record gets saved. If it is closed, the record does not get saved. There is NO way to avoid this event. It ALWAYS runs whether you or Access decides to save a record and it can NEVER be bypassed. Only people who don't understand this event can't control whether or not bad data gets saved.

 

[isladogs]

For an insert, in order fastest to slowest

1. Bound table + save record as a form operation
2. SQL "INSERT INTO" based on dynamically creating an SQL string for a .Execute or a .RunSQL with the values placed in the string via concatenation
3. SQL "INSERT INTO" based on using Form("name")!control name references in the SQL to pick up from controls on that form
4. DAO operation where you have to do .AddNew, individually load values to fields in the created record, then do .Update as separate VBA statements

Our friend Isladogs (Colin) has done experiments on the differences between .RunSQL and .Execute, but I don't recall the details at the moment. On the other hand, if you are doing this from a form, it is a single-record case. I'm pretty sure that the difference between the cases will be minuscule for single-record operations. Your "wait" time for completion of ANY of these for the single-record case will be less then the time it takes you to click some button and then move your eyes to the screen. You are looking at a lot of human latency in form context.

You asked "what's the difference to the compiler?" The answer is "Not enough to worry about" (but probably also in the order I named). On the other hand, the run-time environment WILL care slightly more about the differences.

If you chose #2 AND pre-loaded a database object such as "Set MyDB = CurrentDB" then used MyDB.Execute for executing the SQL string, that WOULD be faster than if you directly used CurrentDB.Execute unless the SET statement were inside the code being timed.

The results of my tests were discussed here Speed Comparison Tests 6 - Mendip Data Systems
Using CurrentDb.Execute is ALWAYS faster than DoCmd.RunSQL.
Typically .Execute is about 10% faster but it can be up to 20% faster in some cases.
Using Query.Defs is slower than both methods and looping through recordsets can take up to 250 times longer if you have a lot of records to update

 

[The_Doc_Man]

Thanks for the summary, Colin. I think you would agree, though, that for a form where you have single-record context, NONE of those should take a terribly long time unless you have an abysmally bad network.

Thanks for chiming in, Pat. I believe @FahadTiger must have been given bad advice someone. Glad that you supported my contention.

 

[isladogs]

Hi Doc
I agree that it makes little difference which method is used for a small number of records.

Nevertheless, to answer the question in post #1:
1. assuming its an action query, I would always use dB.Execute SQL, dbFailOnError because its both simpler and faster as well as limiting messages to only cases where an error occurs.
2. For select queries use query defs in preference.
3. Only loop through recordsets where no other method will work

 

[The_Doc_Man]

We aren't that far apart in viewpoint, Colin. I think the REAL problem is that the OP has gotten hold of a misleading article on ODBC behavior.

 

[isladogs]

Yes I agree. It was a very strange comment to which no link has yet been provided.