Using SQL Stored Procedures in MS Access (1 Viewer)

1DMF

Registered User.
Local time
Today, 02:10
Joined
Oct 20, 2011
Messages
14
Hi,

I moved to a SQL backend many years ago, and all has worked well, however, I have been made to feel like an idiot and a total novice, because I know nothing of stored procedures/ triggers / transactions etc.(well I never got any training, so it's been a seat of the pants job working here!)

If stored procedures and transactions will improve my Access apps and user experience then i want to know more.

I have asked in the same forum but just got told to google SQL BOL or stored procedures, which isn't very helpful as I am lookng for specific guidance in a specific environment, I am currently studying a diploma course via the OU and have enough reading material currently to get through when not a work!

I was hoping someone might be able to advise the best way to start replacing some of the queries in MS access with stored procedures and provide examples.

A form is normally bound to a data source via a query, how do i replace that with a stored procedure without breaking the 'bound' fields on the form.

And how much performance benefit will this give over the standard way of binding tables / querries.

all help is very much appreciated.

regards,
1DMF
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:10
Joined
Aug 30, 2003
Messages
36,118
Stored procedures can certainly provide a benefit, but they won't always. Access (JET/ACE) is actually pretty good at passing the SQL of a query back to SQL Server (SS) to let it do the work. There are some functions that SS doesn't understand (IIf() for example), so if a query has one of them in it the client will be forced to do some or all of the work, requiring more of the data to be sent over the wire, thus slowing everything down.

I work with Access/SS most of the time. I generally use linked tables and most of my queries are regular Access queries based on them. I typically use either the ADO command object or a pass-through query when performance becomes a problem (sometimes in anticipation of it). As a rule, I use ADO to execute a stored procedure that is adding/updating data, a pass-through query to return records for a form or report.
 

1DMF

Registered User.
Local time
Today, 02:10
Joined
Oct 20, 2011
Messages
14
Thanks for the reply.

I changed one of my apps yesterday to bind a form to a passthrough query and executed a stored procedure and it ground to a halt!

So no i need to know when using an SP is better and when it isn't.

A little investigation says when you are returing many records in a record set SP's are not the right thing to use, and I have found as you say that the MS ACCESS Jet driver is pretty good at working out the most efficient way of performing the query.

The question now is, if you are simply performing minor updates via
Code:
currentDB.execute ("UPDATE myTable SET myCol = myVal")
, would it be better to perform those via a parameterised SP or carry on as I am executing SQL commands against linked tables?

Though I guess i would need to create an object in VBA that creates a connection to the DB in order to execute the SP, which seems like a lot of hassle just to perfom a basic update?

What's your thoughts on this?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:10
Joined
Aug 30, 2003
Messages
36,118
As long as the update runs in a reasonable time, I'd leave it like that. A simple update like that should run virtually instantaneously even against linked tables.
 

bparkinson

Registered User.
Local time
Yesterday, 20:10
Joined
Nov 13, 2010
Messages
158
Hi,

I moved to a SQL backend many years ago, and all has worked well, however, I have been made to feel like an idiot and a total novice, because I know nothing of stored procedures/ triggers / transactions etc.(well I never got any training, so it's been a seat of the pants job working here!)

If stored procedures and transactions will improve my Access apps and user experience then i want to know more.

I have asked in the same forum but just got told to google SQL BOL or stored procedures, which isn't very helpful as I am lookng for specific guidance in a specific environment, I am currently studying a diploma course via the OU and have enough reading material currently to get through when not a work!

I was hoping someone might be able to advise the best way to start replacing some of the queries in MS access with stored procedures and provide examples.

A form is normally bound to a data source via a query, how do i replace that with a stored procedure without breaking the 'bound' fields on the form.

And how much performance benefit will this give over the standard way of binding tables / querries.

all help is very much appreciated.

regards,
1DMF

Coming to SQL Server with no knowldedge of it as I also did, it took me about two months to get up to speed. I had large database experience (Progress), so I had all the concepts of how to design databases with millions of rows.

The benefits of moving data processing off to SQL Server are myriad. As to user experience, when you use SQL Server performance improves, you get asynchronous execution of UPDATE and INSERT operations, so your UI is faster. You get conditional processing in your SQL statements, you offload data work from crappy client machines to the beefy database server where it belongs. You get brilliant ETL capabilities. You get to write server-side procedures, and then have SQL Server execute them, unattended, when you need them to be executed, again with conditional logic.

Performance in bound forms for linked SQL Server tables in Access is fine. I have an Access DB with 100+ linked tables, associated forms, etc., and performance is great. That is how you do that. Linked SQL Server tables.

It goes on and on. I recommend any of the decent books on Access/SQL Server development. William Vaughn's books on VB and SQL Server are particularly lucid.
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 22:10
Joined
Jun 23, 2011
Messages
2,631
If stored procedures and transactions will improve my Access apps and user experience then i want to know more.

I have coded all INSERT and UPDATE transactions to the SQL Server back end to be done via Stored Procedures in my app.

They execute extremely snappy, and have the added benefit of working with Paramaters objects to pass in the data to be stored.

I shared an example of Access VBA ADO code to execute a SP here:
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

For queries I have designed a double DAO.Querydef: The inner querydef as a pass-through so that the SQL I want to be executed is sent directly to the SQL Server back-end. The outer wrapper Querydef simply selects all fields from the inner querydef and loads the data into a FE temptable.

I was not able to find an ADO solution to do the same.

Forms should never be directly bound to the SQL backend.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:10
Joined
Aug 30, 2003
Messages
36,118
Forms should never be directly bound to the SQL backend.

This is based on what? I do it all the time (via linked tables), and I believe it's the MS recommended method, once they moved away from ADP.
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 22:10
Joined
Jun 23, 2011
Messages
2,631
This is based on what?

Doing so leaves cursors open and what not.

To be truly transactional and client/server, everything should be updated via SQL and not UI controls directly attached to the BE DB.

I have eliminated two users changing the same record by always doing the update via the record's unique ID and the lastsave timestamp. If the SP can not find a matching record, then either someone else changed it, or deleted it if the app allows deletes. Gone is multi-user locking problems.
 

1DMF

Registered User.
Local time
Today, 02:10
Joined
Oct 20, 2011
Messages
14
Well so far using an SP is proving fruitless, the whle app grinds to a halt.

The old 'bound' method via a normal query against a linked table view takes 1-2 seconds to perform a filter, via SP it takes 58 seconds :eek:

I'm not sure also I want to dive straight into a dicconected model type environment anyhow. I touched it breifly when doing my OU VB.NET course and I don't get it, how you keep the data on the client up-to-date and how you don't overwrite each others changes and re-insert deleted records etc.. I did question these issues, but was told "that isn't in the scope of this course".

The apps are running fine with linked tables even to a remote SQL server via VPN, as soon as I start using SP's with pass-through queries they die a slow and painful death!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:10
Joined
Aug 30, 2003
Messages
36,118
@mdlueck: I would certainly agree that some situations demand an unbound structure like you've described, and I've done it myself, but saying forms should never be bound is in my opinion incorrect. Lots of applications will work perfectly well that way.
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 22:10
Joined
Jun 23, 2011
Messages
2,631
The old 'bound' method via a normal query against a linked table view takes 1-2 seconds to perform a filter, via SP it takes 58 seconds :eek:

That is just utter maddness... however one can not argue with success. (shrug)

I did question these issues, but was told "that isn't in the scope of this course".

(sigh) gggrrr..... :confused:

As for not overwriting other's changes... all of my tables which have general data have a lastsave timestamp column. When I select (bring into edit mode) a record, I retrieve and save in client memory the entire record (all columns). When I attempt to save the record back to the DB, I pass back the same timestamp I was given when I selected the record. Also the ID number is system generated and uneditable. The UPDATE attempts to find a matching record based on the ID and timestamp. If it can not find one, the UPDATE SP fails. On the client side I put up an information message that "perhaps someone else edited the same record" which very well could be the case as that updates the lastsave timestamp. I have the SP grab / store CURRENT_TIMESTAMP as the new value of that lastsave timestamp column, which grabs the current time the server thinks it is when it is saving the record. That way I do not need to have two separate timestamp fields in Access, and Access does not need to provide back what it thinks the timestamp is.
 

1DMF

Registered User.
Local time
Today, 02:10
Joined
Oct 20, 2011
Messages
14
But your saying that an entire update fails?

what if the previous change was against one specific field, but the new changed change more than one field, so we want to keep the first change to the one field while applying all other changes except the change to that specific field changed originally.

Isn't that why bound forms to bound tables is good?, Access deals with all these problems and allows changes to individual fields for the same SQL record to be performed at the same time doesn't it?
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 22:10
Joined
Jun 23, 2011
Messages
2,631
But your saying that an entire update fails?

what if the previous change was against one specific field, but the new changed change more than one field, so we want to keep the first change to the one field while applying all other changes except the change to that specific field changed originally.

Relational Databases deal in terms of entire records, not individual fields within the record. Relational Databases are not "group spreadsheets" keeping track of cell locks. Relational Database lock the entire row, so that is the same level I perform checks at.

Isn't that why bound forms to bound tables is good?, Access deals with all these problems and allows changes to individual fields for the same SQL record to be performed at the same time doesn't it?

That opens the can of worms of having Access locking going on over the network, and and and... I am not interested in the problems related to that. Thus I code how I code.
 

1DMF

Registered User.
Local time
Today, 02:10
Joined
Oct 20, 2011
Messages
14
I can see I have a lot to learn :confused: Thanks for the info.
 

Users who are viewing this thread

Top Bottom