Among my many projects, lately I have been doing a fair amount of database development with SQL server and MS Access. And I’ve written a tutorial on how to leverage the power of SQL server stored procedures through a MS Access database. Here is a brief intro and a link to a full PDF of the article. Comments welcome!
Microsoft Access and Microsoft Server go together like a peanut butter and jelly sandwich. Both offer unique strengths, and used together provide both a robust, secure data environment and a full featured design environment for client applications. In this article I will walk you through the process of running parameterized stored procedures in a SQL database from an Access client application. This allows us to take advantage of the security and speed of running database procedures in SQL server, while feeding local values based upon actions taken in our client Access application.
Inspired by a recent article by Garry Robinson, this tutorial builds an application that maintains a table to log client use of forms and reports in a MS Access database. The routines write to a table in a SQL database using a stored procedure. They pass parameters to this stored procedure (object name, object type, application name) from the client application. So let’s get started!
Comments / 4 COMMENTS
Access Team Blog : Data Points: UI tools, top reasons to use Access and real world stories. on Aug 20 08 at 8:12 pm[...] Access to SQL Tutorial [...]
Tracy Smith added these pithy words on Aug 21 08 at 8:25 amThe majority of my development is MSAccess frontends to SQLServer data. Instead of Stored Procedures, I typically build SQL Strings and then use “CurrentProject.Connection.Execute strSQL” to do appends/updates. I typically have forms bound to only one record and generally have some means of passing the SQL Select * from tblSource Where PrimaryKey=X to the Recordsource property of the form. I have never had a performance complaint and have many times wondered why go to the hassle of stored procedures? Am I missing something? Thanks…
Randall added these pithy words on Aug 21 08 at 8:45 amTracy,
One of the cool things about design work is how different people can arrive at different solutions to the same problems. For simple inserts or updates similar to what I put forward in the article there is probably little difference in the two approaches. But Stored Procedures can employ more complex logic, such as the whole BEGIN TRANSACTION – COMMIT – ROLLBACK type of thing — or conditional statements. There may also be an advantage from a security standpoint to keep the logic off the client, and on the server where it is less likely to be seen.
I’m just beginning to discover the power of stored procedures myself, so I’m sure there are other points to consider as well. Thanks for the comment!
Tracy Smith added these pithy words on Aug 21 08 at 9:07 amThanks for the quick feedback!
I also use the Begin Trans/Commit/Rollback when it applies. For example…
Set cnn = CurrentProject.Connection
cnn.BeginTrans
booInTrans = True
‘ Create Inventory Component for Production Information
strSQL = “Insert Into tblInventory (Fld1,Fld2, …) Values (Val1,Val2,…)
cnn.Execute strSQL
‘ Add Activity Log
strSQL = “Insert Into tblActivityLog (Fld1,Fld2,…) Values (Val1,Val2,…)
cnn.Execute strSQL
cnn.CommitTrans
booInTrans = Falseexit_function:
cnn.Close
Exit Functionerr_function:
If booInTrans Then
cnn.RollbackTrans
Resume exit_function
Else
MsgBox Err.Number & “-” & Err.Description
Resume exit_function
End If
ADD YOUR COMMENT
Comments are moderated.
Randall Rode's online home for thoughts, notes, and experiments with a wide range of technology topics. Visit the about page for info on my recent projects and professional background. I welcome your comments!
New articles are normally posted on Mondays and Wednesdays. Subscribe to the RSS feed or the email update to keep current on the latest posts.
Past listings from 2004 to the present
Need a home (or better home) for your website? I highly recommend HostRocket -- they are the best!
