ACCESS EXPERTS
Tuesday, July 13, 2010
Part 3: Why I grew to love Microsoft Access Data Projects
Why I learned to love Microsoft Access Data Projects
As a seasoned Access programmer, it seams all I do lately is Access with SQL Server, in part because of the great combination of SQL Server Express with Access and the zero cost of using Express for my clients. For the greater part of my career I've been using regular MDB's for all of my work, but that all changed with a recent project of mine's.
Why I had to use a Microsoft Access Data Project
My client, a nationally recognized home cleaning firm with franchisees across the USA, needed to consolidate each franchisee's data into one national database for reporting of income. Up until then each franchisee was using a local MDB and mailing in there numbers, going national would avoid the hassle and provide real time intelligence to corporate headquarters on the health of the business. I decided on Microsoft Access 2007 runtime with SQL Server Express 2008 R2, but I rant into the following issues with a regular MDB:
•Record Locking: Test users were getting the dreaded message "Another user has modified the data..." when running data modification routines in code. Mind you this was after I had optimized the code to use one connection throughout the project.
•Speed: The regular Access MDB can open up multiple connections to the SQL Server backend, users were noticing a significant delay in downloading data off the Internet.
Both issues were resolved when I switched to using an ADP for the frontend. Not only did the record locking issue disappear, the speed of the application was better when SQL Server on the web then with a local Access file!
Microsoft Data Projects work well...if you avoid certain pitfalls
I've written a series of articles on the following issues with ADP:
•DoCmd.OpenForm where and filter clauses will not work.
•It's not easy to supply your own login form when using SQL Server security.
•You're going to have to learn SQL Server tools in order to maximize your Access user experience
I encourage you to learn how to use ADPs and gain from their unique relationship with SQL Server, and I hope you too will learn to love them!
Launch Email Window from Form
If your database contains an email field, you can easily modify your form so that users can single click to create an email in their default email program.
First, modify the control's Display as Hyperlink property to Always, second, modify the control's on click event with the following code:
If Not IsNull(Me.EMail) Then
Application.FollowHyperlink "mailto:" & Me.EMail
End If
Note: Change EMail to the name of your email control on your form
That's it! Users will see the familiar web hand when they hover over the email address, and when they click access will launch a new email window for them to further edit before sending off.
First, modify the control's Display as Hyperlink property to Always, second, modify the control's on click event with the following code:
If Not IsNull(Me.EMail) Then
Application.FollowHyperlink "mailto:" & Me.EMail
End If
Note: Change EMail to the name of your email control on your form
That's it! Users will see the familiar web hand when they hover over the email address, and when they click access will launch a new email window for them to further edit before sending off.
Part 2 Access ADP Tip : Get rid of the login box on startup
Note: This is part two of a series of articles on Access Data Projects, to see the complete list and other articles please click here.
Access ADPs can provide you with the flexibility and power only a direct connection to SQL Server can provide, but a major drawback to a developer is the login box that will always show up when you distribute your application, Access will stubbornly hold on to the last account used to connect with the server, unless you store the password with the file, and that may not be ideal.
Use a low level SQL Server account and reset the project connection
I came across a partial solution to the problem using this support article. It describes how you can reset the connection to the user and password of your choosing, but it does not address how to get rid of the login box on startup.
The solution I developed is twofold: Use a low level account on startup, show a custom login form and then reset the project connection.
Start out by creating a new SQL Server login, (or a new Windows login if your ADP is on a Windows network and your using Windows authentication on your SQL Server database), that only has rights to the login stored procedure you've created for your ADP. (If you don't have a login stored procedure you can find one soon in the same section where all are articles are located). This will allow you to save the username and password with your ADP project, but not provide rights to any table, view or stored procedure if the account info is compromised. Whatever you do, never use a high level account and password as your startup account with the ADP.
Now that you've saved the low level login and password with the ADP, the dreaded login form will no longer display on startup, allowing you to instead display your own login form for the users of your application. Have them enter their login name and password, have them click on a button labeled Login and then proceed to break the connection and restore it with their connections to SQL Server.
Here is the sample code to validate their login, use it on your login form:
If Not ChangeADPConnection(Me.txtUserID, Me.txtPassword) Then
Exit Sub
End If
txtUserID and txtPassword are two fields on the form where users enter their user name and password, substitute them with your own control names if you wish.
The ChangeADPConnection procedure will use the user name and password from the login form to validate the user and establish a connection with the server using their rights and not the rights of the low level login:
Public Function ChangeADPConnection(strUN As String, strPW As String) As Boolean
Dim strConnect As String
Dim strServerName As String
Dim strDBName As String
strServerName = "YourServerIPAddress"
strDBName = "YourDatabase"
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
strConnect = strConnect & ";user id=" & strUN
If strPW <> "" Then
strConnect = strConnect & ";password=" & strPW
End If
Else 'Try to use integrated security if no username is supplied.
strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False
End Function
Conclusion
Access ADPs have a unique advantage over regular MDB's when using SQL Server, I encourage you to use them if you can, along with SQL Server stored procedures, and create great solutions for your clients.
Access ADPs can provide you with the flexibility and power only a direct connection to SQL Server can provide, but a major drawback to a developer is the login box that will always show up when you distribute your application, Access will stubbornly hold on to the last account used to connect with the server, unless you store the password with the file, and that may not be ideal.
Use a low level SQL Server account and reset the project connection
I came across a partial solution to the problem using this support article. It describes how you can reset the connection to the user and password of your choosing, but it does not address how to get rid of the login box on startup.
The solution I developed is twofold: Use a low level account on startup, show a custom login form and then reset the project connection.
Start out by creating a new SQL Server login, (or a new Windows login if your ADP is on a Windows network and your using Windows authentication on your SQL Server database), that only has rights to the login stored procedure you've created for your ADP. (If you don't have a login stored procedure you can find one soon in the same section where all are articles are located). This will allow you to save the username and password with your ADP project, but not provide rights to any table, view or stored procedure if the account info is compromised. Whatever you do, never use a high level account and password as your startup account with the ADP.
Now that you've saved the low level login and password with the ADP, the dreaded login form will no longer display on startup, allowing you to instead display your own login form for the users of your application. Have them enter their login name and password, have them click on a button labeled Login and then proceed to break the connection and restore it with their connections to SQL Server.
Here is the sample code to validate their login, use it on your login form:
If Not ChangeADPConnection(Me.txtUserID, Me.txtPassword) Then
Exit Sub
End If
txtUserID and txtPassword are two fields on the form where users enter their user name and password, substitute them with your own control names if you wish.
The ChangeADPConnection procedure will use the user name and password from the login form to validate the user and establish a connection with the server using their rights and not the rights of the low level login:
Public Function ChangeADPConnection(strUN As String, strPW As String) As Boolean
Dim strConnect As String
Dim strServerName As String
Dim strDBName As String
strServerName = "YourServerIPAddress"
strDBName = "YourDatabase"
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
strConnect = strConnect & ";user id=" & strUN
If strPW <> "" Then
strConnect = strConnect & ";password=" & strPW
End If
Else 'Try to use integrated security if no username is supplied.
strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False
End Function
Conclusion
Access ADPs have a unique advantage over regular MDB's when using SQL Server, I encourage you to use them if you can, along with SQL Server stored procedures, and create great solutions for your clients.
Part 1 Access ADP Tip: Using ADP ServerFilter property
Note: This is part one of a series of articles on Access ADP projects, to see the complete list and other articles please click here.
Where and Filter Clause do not work when using DoCmd.OpenForm Statement
One of the issues you will find when working with an Access Data Project, (ADP), is the lack of functionality in the Where and Filter clause of the DoCmd.OpenForm statement, they don't work with ADPs. There are two techniques you can use to overcome this limitation:
Set The Form's Record Source Property
On startup or during the course of using the form, you can set the form's record source property using the OpenArgs argument:
Me.RecordSource = "Select * from MyTable Where " & Me.OpenArgs
Use ServerFilter Property
Another method is to use the ServerFilter property of the form. On the open event you will need to use the following statements
Me.ServerFilter = ""
Me.ServerFilter = Me.OpenArgs
The first line is needed to reset the property before you set it to something else.
Where and Filter Clause do not work when using DoCmd.OpenForm Statement
One of the issues you will find when working with an Access Data Project, (ADP), is the lack of functionality in the Where and Filter clause of the DoCmd.OpenForm statement, they don't work with ADPs. There are two techniques you can use to overcome this limitation:
Set The Form's Record Source Property
On startup or during the course of using the form, you can set the form's record source property using the OpenArgs argument:
Me.RecordSource = "Select * from MyTable Where " & Me.OpenArgs
Use ServerFilter Property
Another method is to use the ServerFilter property of the form. On the open event you will need to use the following statements
Me.ServerFilter = ""
Me.ServerFilter = Me.OpenArgs
The first line is needed to reset the property before you set it to something else.
How to create PDFs in Word using Access VBA
Office 2007 will allow you to easily create PDFs from any Word Document using the output menu on the Office button, (Access does too with Reports), but recently I had to create a procedure for a client that wanted to convert Word docs to PDF, email them and do it all from Access.
Note: To make this code work, you will need to add a reference to Word 2007 to your Access database.
Here is the code:
Private Sub CreatePDF(strSourceFile As String, strDestFile As String)
Dim objWord As Word.Application
Dim objWordDoc As Word.Document
On Error GoTo ErrorHandler
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objWordDoc = objWord.Documents.Open(strSourceFile)
If Not objWord Is Nothing Then
objWordDoc.ExportAsFixedFormat strDestFile, wdExportFormatPDF, False, wdExportOptimizeForPrint, wdExportAllDocument
End If
ExitProcedure:
objWordDoc.Close False
objWord.Quit
Set objWordDoc = Nothing
Set objWord = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbInformation, "Error Creating PDF"
End Sub
Note how I display Word at the begining of the process, not doing so may leave Word "hanging" in memory if your code comes back with an error.
Note: To make this code work, you will need to add a reference to Word 2007 to your Access database.
Here is the code:
Private Sub CreatePDF(strSourceFile As String, strDestFile As String)
Dim objWord As Word.Application
Dim objWordDoc As Word.Document
On Error GoTo ErrorHandler
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objWordDoc = objWord.Documents.Open(strSourceFile)
If Not objWord Is Nothing Then
objWordDoc.ExportAsFixedFormat strDestFile, wdExportFormatPDF, False, wdExportOptimizeForPrint, wdExportAllDocument
End If
ExitProcedure:
objWordDoc.Close False
objWord.Quit
Set objWordDoc = Nothing
Set objWord = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbInformation, "Error Creating PDF"
End Sub
Note how I display Word at the begining of the process, not doing so may leave Word "hanging" in memory if your code comes back with an error.
Using a SQL Server stored procedure with a insert query in Access
If you're using a SQL Server database with an Access frontend, than this article is for you. Leverging the power of Stored Procedures from Access has numerous benefits: your server can perform transactions that would take far longer in Access. Recently I came accross the following issue when working on a client's database.
Stored Procedures with Action Queries can be a problem
I created a stored procedure that does multiple updates using action queries, and I wanted the results of the procedure returned back to Access so that the user can now how many records were updated. I wanted the results inserted into a local Access table for a historical reference, and I had decided on using a pass-through query with another insert query in Access to accomplish the task. The insert query would kick off the pass-through query that would in turn kick off the stored procedure on the server.
Pass-Through Query + Insert: A dangerous combo!
I used this Microsoft article to create the pass through query in my code. (If your stored procedure does not take arguments then you don't have to this, rather, just create the pass-throgh query once with dummy code, then do your insert query, then finalyze your pass-through). Then I tried using the QBE window in Access, but in doing so the system wanted to execute my pass-through query to get the column names from my stored procedure. Not good, since it would kick-off my action queries on the server and cause havoc to my data! Instead I wrote my insert insert query using SQL view in Access, when I went to save the query it still wanted to execute my pass-through, but I had not saved the connection string, so there was no harm done.
Summary Steps
* Create a dummy pass-through query in Access that will NOT execute the stored procedure, don't bother with a connection string at this point.
* Create an insert query using the SQL mode in access, not the Query by Example grid.
* Modify your code to create the pass-through query on the fly if needed. (You will need to do so if your stored procedure requires parameters)
* Kick off the whole process in your code by first creating the pass-through and then executing the insert query:
CreateSPT "qrySQLCompleteImport", "Exec sp_CompleteImport " & Me.txtFranchiseID, strSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCompleteImport"
Stored Procedures with Action Queries can be a problem
I created a stored procedure that does multiple updates using action queries, and I wanted the results of the procedure returned back to Access so that the user can now how many records were updated. I wanted the results inserted into a local Access table for a historical reference, and I had decided on using a pass-through query with another insert query in Access to accomplish the task. The insert query would kick off the pass-through query that would in turn kick off the stored procedure on the server.
Pass-Through Query + Insert: A dangerous combo!
I used this Microsoft article to create the pass through query in my code. (If your stored procedure does not take arguments then you don't have to this, rather, just create the pass-throgh query once with dummy code, then do your insert query, then finalyze your pass-through). Then I tried using the QBE window in Access, but in doing so the system wanted to execute my pass-through query to get the column names from my stored procedure. Not good, since it would kick-off my action queries on the server and cause havoc to my data! Instead I wrote my insert insert query using SQL view in Access, when I went to save the query it still wanted to execute my pass-through, but I had not saved the connection string, so there was no harm done.
Summary Steps
* Create a dummy pass-through query in Access that will NOT execute the stored procedure, don't bother with a connection string at this point.
* Create an insert query using the SQL mode in access, not the Query by Example grid.
* Modify your code to create the pass-through query on the fly if needed. (You will need to do so if your stored procedure requires parameters)
* Kick off the whole process in your code by first creating the pass-through and then executing the insert query:
CreateSPT "qrySQLCompleteImport", "Exec sp_CompleteImport " & Me.txtFranchiseID, strSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCompleteImport"
Using SQL Server Views with Access: Index needed for editing data.
SQL Server views are one of the best tools a Microsoft Access programmer can use to limit exposure to data and improve Access performance.
Unfortunately, you can't modify data in a view unless it has a unique index, read on if that is the case for you. (You can create indexed views in SQL Server, this article applies to non-indexed views)
After you have linked your view to your Access application, create a new query or execute the following SQL statement in your code to create the index:
Create Index ix_name On view_name(fields) With Primary
ix_name: The name you assign to your primary index.
view_name: The name of your view in Access.
fields: One or more fields seperated by commas that will compose your primary key.
That's all there is to it. We hope you can "Discover the Power of Your Data!" with this tip.
Unfortunately, you can't modify data in a view unless it has a unique index, read on if that is the case for you. (You can create indexed views in SQL Server, this article applies to non-indexed views)
After you have linked your view to your Access application, create a new query or execute the following SQL statement in your code to create the index:
Create Index ix_name On view_name(fields) With Primary
ix_name: The name you assign to your primary index.
view_name: The name of your view in Access.
fields: One or more fields seperated by commas that will compose your primary key.
That's all there is to it. We hope you can "Discover the Power of Your Data!" with this tip.
Subscribe to:
Posts (Atom)