Stored Procedures 1

[1DMF]

Can someone point me to a good resource on using SQL stored procedures in MS Access instead of using SQL commands and queries.

Thanks,

1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[dhookom]

Search the web on "SQL SERVER BOL" or "Microsoft SQL Server Stored procedures" or the SQL Server Programming forum here at Tek-Tips.

Duane
Hook'D on Access
MS Access MVP

 

[jrbarnett]

With regards to calling them from Access there are two basic ways:

1. From a pass through query
- set the query text to EXEC dbo.MyStoredProc @param1='x', @param2='y'

2. Using VBA via ADO to connect to your SQL server.

You will need to build a connection string for your own server - see

http://www.connectionstrings.com

In a production system obviously you would store this in an external configuration file and read it in, having it scrambled, but its OK for a demo version.

Const DB_CONNECT As String = "Provider=SQLOLEDB;Server=(local);Database=johntest;User ID=xxxx;Password=yyyy;Trusted_Connection=False;"

Public Function ConnectViaADO()

    ' ADO objects for database connection
    ' You will need to add a raference to Microsoft ActiveX DAta Objects version 2.6 or newer (ideally 2.8)

    ' Declare variables
    Dim cn As ADODB.Connection ' represents connection to server
    Dim cmd As ADODB.Command   ' Represents command
    Dim rst As ADODB.Recordset ' Represents recordset
    
    ' Instantiate connection
    Set cn = New ADODB.Connection
    
    With cn
        .ConnectionString = DB_CONNECT
        .CursorLocation = adUseClient
        .Open
    End With

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "uspMyStoredProcName"
        .Parameters("@Parameter1") = "hello"
        .Parameters("@Parameter2") = "goodbye"
    End With

    ' Now execute the procedure
    Set rst = cmd.Execute
    ' and you can do anything with it here that you can do with a recordset

    ' Close the database connection
    cn.Close
End Function

This is a very simple example, far more complicated things are possible.
I'd second dhookom's advice on using SQL Server books online to write, test and debug stored procedures, but there is nothing in there in calling them from Access applications.

John

 

[1DMF]

Thanks John,

I managed to get a passthrough query executing an SP bound to my form and the application ground to a halt.

So SP's aren't always the best way to do things.

Using VBA looks rather long winded to perform a simple update, when with normal linked tables

currentdb.execute ("UPADATE myTabe SET myCol = myVal WHERE Col1='aValue'")

is all you need.

So when is an SP better?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[dhookom]

I would always expect that running a SP from a pass-through would be much more efficient that executing an update query on a linked SQL table.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

So when is an SP better
At least when you can't do all the tasks in pure SQL, I guess.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrbarnett]

Hi 1DMF
1DMF,

I was in a similar situation to you several years ago.

The advantages of stored procedures:
1. It modularises your code - you can set up a set of scripts that returns data based on input parameters. Query execution plans get saved and re used where possible, and you don't have to do anything specific to get this performance advantage.
2. Even without explicit transaction code, a procedure either executes completely or not at all.
3. They are useful for security - you can have applications execute them without direct access to the underlying tables or other objects that they may use.
4. You can take full advantage of TSQL rather than have Access convert the retrieved data to its own format. As a bare minimum, create views to do the table joins, then link to the views rather than tables - it saves SQL server downloading all the table data to the client PC and handling the joins and filtering on the client PC. This saves network bandwidth and helps boost server performance.

If you run SQL Server profiler or activity monitor when accessing/manipulating data via linked tables, you will see that large numbers of table locks get opened and are maintained until the browsing stops - you can do things a lot faster via TSQL rather than via Access linked tables.

To save repeating standard advice, can I suggest you go to

http://www.simple-talk.com/books/

and download:
- SQL Server Tacklebox
- SQL Server Crib Sheet compendium
- Mastering SQL Server Profiler

and have a look at some of the beginners videos at

http://www.asp.net/sql-server/videos

(you may find some of these are too basic for you as you have database/SQL experience).

Happy reading.

John

 

[1DMF]

Thanks John, I check it out.

So far my SP has been worse than my view, for retrieving data, but i also read that SP's should be used for retrieving large record sets?

Is this true and what's large?


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[jrbarnett]

(at a risk of going off topic for an Access forum) Can you run the stored procedure without errors via Management studio (query analyser if SQL 2000 or earlier), connecting via an account with the same access rights to SQL server that your Access application does ?

John

 

[1DMF]

Yes I can connect via management studio and select 'Execute Stored Procedure' i get the returned record set and a results window, it seems to run fine

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[jrbarnett]

Can you post
i) the code for your stored procedure
ii) the code that you have got so far to try and execute it (feel free to change server connection details).

and I will have a look

The main reason stored procedures are preferred over views is that parameters can be used as input to filter content, (for example in SQL WHERE or HAVING clauses) and these can reduce the quantity of data transferred across the network over views; additionally the execution plans for stored procedures are cached in SQL Server, giving it an advantage when the same code is executed multiple times.

However, with views no such cacheing or server side filtering takes place.

As for what is big, it depends on the size of the database(s) and how powerful your server is and if there is anything else running on it other than SQL Server.

John

 

[1DMF]

Here is the info thread183-1664321

The server is old and crappy and also 50 miles away at the end of a VPN connection.

Yet MS Access runs smooth and fast, where as SQL SP's unusable?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[jrbarnett]

I think that indexes have been added to the Access tables, which your application is using (as they are being updated as data inserts/updates take place), but because the data coming straight from SQL server with a pass through query, these are bypassed and so Access has to sort the data without any assistance.

My guess is that if data manipulation were not being done through Access linked tables you would experience similar problems with that.

Try something for me:
In Access, go to the query window, create a new query of type passthrough
Enter the SQL code
EXEC dbo.mystoredprocedure

(obviously substituting your procedure name) and save the query. Use the query properties menu to set an ODBC connection to the server - post back if you need instructions on how to do this.

Once this is done, double click this from the navigation pane (office 2007/10) or database window (earlier than 2007) and see if you get back your data quickly.

If this runs OK then the problem is caused by Access connectivity from your PC to the database server.

If this runs fine, suggest that you try using this as the data source for your list and see if there is any advantage over your local view.

 

[1DMF]

The SP itself runs fine and If I run the SP by running the pass-through query the data is returned real fast.

As soon as I use the pass-through as the datasource to the form, it grinds to a halt.

I keep thinking it is to do with the fact it is a continuos form with conditional formatting, should this make such a difference?

The other thing I notice is once I have the pass-through open and the recordset is displayed, when I click the [x] to close the query results it egg timers for @ 5-10 seconds before closing, yet it opens in a fraction of a second?

I also find once the pass-through query is part of the access DB, the whole design mode IDE runs slow and constantly egg timers?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[jrbarnett]

(with apologies for cross posting) Putting together your postings in both the Access and SQL Server fora I am starting to put together a plan of action.

Do you have any servers in your offices that you could host this database on (whether they host SQL server yet or not)? Advantages to sell this to your boss:
1. improve performance (as data wouldn't need to be transferred across the VPN line) when system uses it.
2. allow you to have a higher spec server than you currently get, to get around the limited resources issue.
3. it gives you full control over the server, but as a negative, responsibility for maintaining the server itself, plus backups etc.
Cost is purchase of the server itself + accessories (Windows operating system license and SQL Server license + CALs, UPS etc), then time in migrating the data and modifying application to use local copy rather than remote, and configuring .

You could use the current VPN site as an offsite backup, or get quotes from them for a more powerful specification server and bigger capacity disks (and bandwidth). One or other will be needed. Quotes from alternative hosts should also be considered at this stage.

Your boss probably won't like this as it involves spending money, but the upshot is that if nothing is done, sooner rather than later, the server, and therefore anything that relies on it will fall over. You may have some sort of idea how quickly extra data gets added, and therefore approximate how quickly the disk will fill up (remembering to account for disk usage of backups etc as well as the database itself).

There are things you can do that may free up disk space on the server (they may not be possible depending how vigilant you are) including:
* delete unnecessary files (eg windows and SQL Server service pack install files, backups of windows updates and service packs that have been installed long ago), old contents of temp folders etc
* archive any historical data you don't need available immediately to a separate database you can host locally with SQL server express edition (free)

John