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.
SQL Server: Easily Grant Select to All Tables and/or Views in a Database
(Note: I loved the code I found on the original posting, but it would not work without a simple change. I'm copying the post here and highlighting my changes, talk about a time saver!)
Granting Select to all tables for a login can be an extremely painful and lengthy process. Utilizing the SQL Server Management Studio to perform this means going table by table and checking the Select checkbox for each individual table, this makes a real nightmare if you are working with hundreds of tables. You can do this in mere seconds versus hours by using the Query window. Here’s how:
1. Open SQL Server Management Studio.
2. Connect to the desired Server.
3. Click the New Query button.
4. Select the desired database from the dropdown in the toolbar.
5. To grant Select to all tables in the database, copy and paste the following into your Query window:
DECLARE @login varchar(50)
SET @login = 'loginname'
DECLARE @ROWID Int --Added by Juan Soto
DECLARE @SQLSTR VARCHAR(Max) --Added by Juan Soto
DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @tables
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'U'
AND NAME NOT LIKE 'SYNC%'
DECLARE @rowid int, @sqlstr varchar(500)
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_tbl_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @tables
ORDER BY ROWID
OPEN grant_tbl_cursor
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_tbl_cursor
DEALLOCATE grant_tbl_cursor
6. In the second line of the pasted query, change loginname to the login that you wish to grant Select to all tables.
7. Execute the query.
To grant Select to all Views in the database, follow the above steps but use the following:
DECLARE @login varchar(50)
SET @login = 'loginname'
DECLARE @ROWID Int --Added by Juan Soto
DECLARE @SQLSTR VARCHAR(Max) --Added by Juan Soto
DECLARE @views TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @views
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'V'
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_vw_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @views
ORDER BY ROWID
OPEN grant_vw_cursor
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_vw_cursor
DEALLOCATE grant_vw_cursor
Granting Select to all tables for a login can be an extremely painful and lengthy process. Utilizing the SQL Server Management Studio to perform this means going table by table and checking the Select checkbox for each individual table, this makes a real nightmare if you are working with hundreds of tables. You can do this in mere seconds versus hours by using the Query window. Here’s how:
1. Open SQL Server Management Studio.
2. Connect to the desired Server.
3. Click the New Query button.
4. Select the desired database from the dropdown in the toolbar.
5. To grant Select to all tables in the database, copy and paste the following into your Query window:
DECLARE @login varchar(50)
SET @login = 'loginname'
DECLARE @ROWID Int --Added by Juan Soto
DECLARE @SQLSTR VARCHAR(Max) --Added by Juan Soto
DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @tables
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'U'
AND NAME NOT LIKE 'SYNC%'
DECLARE @rowid int, @sqlstr varchar(500)
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_tbl_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @tables
ORDER BY ROWID
OPEN grant_tbl_cursor
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_tbl_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_tbl_cursor
DEALLOCATE grant_tbl_cursor
6. In the second line of the pasted query, change loginname to the login that you wish to grant Select to all tables.
7. Execute the query.
To grant Select to all Views in the database, follow the above steps but use the following:
DECLARE @login varchar(50)
SET @login = 'loginname'
DECLARE @ROWID Int --Added by Juan Soto
DECLARE @SQLSTR VARCHAR(Max) --Added by Juan Soto
DECLARE @views TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))
INSERT INTO @views
SELECT 'GRANT SELECT ON ' + NAME + ' TO '+@login
FROM sysobjects
WHERE TYPE = 'V'
SET @rowid = 0
SET @sqlstr = ''
DECLARE grant_vw_cursor CURSOR FOR
SELECT ROWID, SQLSTR
FROM @views
ORDER BY ROWID
OPEN grant_vw_cursor
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@sqlstr)
FETCH NEXT FROM grant_vw_cursor
INTO @rowid,@sqlstr
END
CLOSE grant_vw_cursor
DEALLOCATE grant_vw_cursor
Stuck on how to connect to a data source?
The other day I needed to connect to my local instance of SQL Server Express 2008, and I wanted to do it without exposing my SQL Server via TCP/IP, (why invite the world to my doorstep if the server is for local use only?). I was having trouble doing so until I found the right syntax at ConnectionStrings.com:
Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=
DataDirectory
\mydb.mdf;User Instance=true;
The key for me was using ".\SQLExpress" in my connection string.
The site has a wide range of connection strings for pratically any data source in the planet, head on over and bookmark it, you may never know when it may come in handy!
Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=
DataDirectory
\mydb.mdf;User Instance=true;
The key for me was using ".\SQLExpress" in my connection string.
The site has a wide range of connection strings for pratically any data source in the planet, head on over and bookmark it, you may never know when it may come in handy!
Create DELETE Audit Tables in SQL Server
This is a modification to an earlier tip I wrote, whereas this code will only record delete changes, my earlier article will record delete and update changes to the data.
I found code online by Brett Kaiser and I modified it for my purposes, with a mind to other Access developers I made some changes which I will explain here. Note: This article assumes you're familiar with SQL Server tools and TSQL.
To view the original post from Brett Kaiser, please click here:
Using SQL Server Management Studio express, open a new query window pointing towards your DB and start by creating a new table that will store all of the tables you wish to implement audit triggers for:
-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE tblAudit(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)
GO
-- Now replace the code above with the code below in the query window. You can either type the table names into the new tblAudit table or you can use the following code which will insert a record for each table in the database. CAUTION: Don't add triggers to a table unless they are mission critical, some tables you may not want to add triggers for our look up tables or tables where not everyone has access too. If you do use the code below to populate the tblAudit table then STOP and review the list of tables and delete any unncessary tables you don't wish to track.
INSERT INTO tblAudit(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'tbl%'
-- NOTE: Review the list of tables before going to the next step below
-- Now you're ready to create the audit tables with the code below:
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H ('
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
, @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal' THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION
SELECT @SQL = @SQL + @COLUMN_NAMES + ')'
SELECT @sql = REPLACE(@SQL, '-1', 'MAX')
SELECT @sql = REPLACE(@SQL, ', Default ', ', [Default] ')
SELECT SQL = @sql
EXEC(@SQL)
SELECT @SQL = '', @COLUMN_NAMES = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- The code below was added by me and not part of the original article, in order for users to be able to see changes to the data, they are going to need security rights to the audit tables: Insert and Select. The following code will cycle through all of the tables in the database that end in '_h' and assign the rights to the Public group. (All users)
DECLARE
myCursor99 CURSOR
FOR
SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX)
SELECT @COLUMN_NAMES = ''
OPEN
myCursor99
FETCH
NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA,@TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'GRANT SELECT ON ' + @TABLE_CATALOG + '.' +@TABLE_SCHEMA + '.' + @TABLE_NAME + '_H TO PUBLIC'
SELECT SQL = @sql
EXEC(@SQL)
SELECT @SQL = 'GRANT INSERT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + '_H TO PUBLIC'
EXEC(@SQL)
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG,@TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- Now Lets create the audit TRIGGERS. Note: I've modified the original code so that it will not record changes to the Timestamp field and code that will encapsulate the word DEFAULT correctly when it's encountered in the table. I also don't wish to save the domain name along with the username in the table, so I've added code for that as well.
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME And DATA_TYPE <> 'timestamp'
ORDER BY ORDINAL_POSITION
SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
+ ' FOR DELETE AS '
+ ' DECLARE @HOSTNAME sysname, @DESC varchar(50) '
+ ' SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID '
+ ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') SELECT @DESC = '
+ '''' + '''' + ' ELSE SELECT @DESC = ' + '''' + 'MASS DELETE' + ''''
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC = '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT ''D'', SYSTEM_USER, REPLACE (USER_NAME(),''DOMAINNAME\'','''') , @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC <> '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, REPLACE(USER_NAME(),''DOMAINNAME\'',''''), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
SELECT @sql = REPLACE(@SQL, ', Default ', ', [Default] ')
SELECT @sql = REPLACE(@SQL, ', Default) ', ', [Default]) ')
Print @SQL
Select TRIGGERSQL = @sql
EXEC(@SQL)
SELECT @SQL = '', @COLUMN_NAMES = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- Ok, you're done! But what if you make a mistake? You can easily get rid of ALL triggers created earlier by running the following TSQL and starting the process anew
-- *********** DROP TRIGGER CODE FOLLOWS ************
-- Use this code in case you need to drop any triggers created earlier, in case
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION
--Select @SQL = 'DROP ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
Select @SQL = 'DROP TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR'
Select SQL = @SQL
EXEC(@SQL)
SELECT @SQL = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
I found code online by Brett Kaiser and I modified it for my purposes, with a mind to other Access developers I made some changes which I will explain here. Note: This article assumes you're familiar with SQL Server tools and TSQL.
To view the original post from Brett Kaiser, please click here:
Using SQL Server Management Studio express, open a new query window pointing towards your DB and start by creating a new table that will store all of the tables you wish to implement audit triggers for:
-- CREATE A Driver Table for all Tables in your catalog you wish to audit
CREATE TABLE tblAudit(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname)
GO
-- Now replace the code above with the code below in the query window. You can either type the table names into the new tblAudit table or you can use the following code which will insert a record for each table in the database. CAUTION: Don't add triggers to a table unless they are mission critical, some tables you may not want to add triggers for our look up tables or tables where not everyone has access too. If you do use the code below to populate the tblAudit table then STOP and review the list of tables and delete any unncessary tables you don't wish to track.
INSERT INTO tblAudit(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME LIKE 'tbl%'
-- NOTE: Review the list of tables before going to the next step below
-- Now you're ready to create the audit tables with the code below:
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'CREATE TABLE ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA + '.' + @TABLE_NAME + '_H ('
+ ' HIST_ADD_DT datetime DEFAULT (getDate()), HIST_ADD_TYPE char(1) NOT NULL'
+ ', HIST_ADD_SYSTEM_USER sysname NOT NULL, HIST_ADD_USER_NAME sysname NOT NULL'
+ ', HIST_ADD_HOSTNAME sysname NOT NULL, HIST_ADD_SPID int NOT NULL, HIST_ADD_DESC varchar(50) '
, @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
+ ' ' + DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN DATA_TYPE = 'decimal' THEN '(' + CONVERT(varchar(20),NUMERIC_PRECISION) + ','
+ CONVERT(varchar(20),NUMERIC_SCALE) + ')'
ELSE ''
END
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION
SELECT @SQL = @SQL + @COLUMN_NAMES + ')'
SELECT @sql = REPLACE(@SQL, '-1', 'MAX')
SELECT @sql = REPLACE(@SQL, ', Default ', ', [Default] ')
SELECT SQL = @sql
EXEC(@SQL)
SELECT @SQL = '', @COLUMN_NAMES = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- The code below was added by me and not part of the original article, in order for users to be able to see changes to the data, they are going to need security rights to the audit tables: Insert and Select. The following code will cycle through all of the tables in the database that end in '_h' and assign the rights to the Public group. (All users)
DECLARE
myCursor99 CURSOR
FOR
SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX)
SELECT @COLUMN_NAMES = ''
OPEN
myCursor99
FETCH
NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA,@TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'GRANT SELECT ON ' + @TABLE_CATALOG + '.' +@TABLE_SCHEMA + '.' + @TABLE_NAME + '_H TO PUBLIC'
SELECT SQL = @sql
EXEC(@SQL)
SELECT @SQL = 'GRANT INSERT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + '_H TO PUBLIC'
EXEC(@SQL)
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG,@TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- Now Lets create the audit TRIGGERS. Note: I've modified the original code so that it will not record changes to the Timestamp field and code that will encapsulate the word DEFAULT correctly when it's encountered in the table. I also don't wish to save the domain name along with the username in the table, so I've added code for that as well.
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME And DATA_TYPE <> 'timestamp'
ORDER BY ORDINAL_POSITION
SELECT @SQL = 'CREATE TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
+ ' FOR DELETE AS '
+ ' DECLARE @HOSTNAME sysname, @DESC varchar(50) '
+ ' SELECT @HOSTNAME = hostname from master.dbo.sysprocesses where spid = @@SPID '
+ ' IF EXISTS(SELECT * FROM ' + @TABLE_NAME + ') SELECT @DESC = '
+ '''' + '''' + ' ELSE SELECT @DESC = ' + '''' + 'MASS DELETE' + ''''
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC = '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT ''D'', SYSTEM_USER, REPLACE (USER_NAME(),''DOMAINNAME\'','''') , @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
+ ' If Not Exists (Select * From Inserted) And Exists (Select * From Deleted) And @DESC <> '''' '
+ ' INSERT INTO ' + @TABLE_NAME + '_H ( '
+ ' HIST_ADD_TYPE, HIST_ADD_SYSTEM_USER, HIST_ADD_USER_NAME, HIST_ADD_HOSTNAME, HIST_ADD_SPID, HIST_ADD_DESC'
+ @COLUMN_NAMES + ')'
+ ' SELECT TOP 1 ''D'', SYSTEM_USER, REPLACE(USER_NAME(),''DOMAINNAME\'',''''), @HOSTNAME, @@SPID, @DESC'
+ @COLUMN_NAMES + ' FROM deleted'
SELECT @sql = REPLACE(@SQL, ', Default ', ', [Default] ')
SELECT @sql = REPLACE(@SQL, ', Default) ', ', [Default]) ')
Print @SQL
Select TRIGGERSQL = @sql
EXEC(@SQL)
SELECT @SQL = '', @COLUMN_NAMES = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
-- Ok, you're done! But what if you make a mistake? You can easily get rid of ALL triggers created earlier by running the following TSQL and starting the process anew
-- *********** DROP TRIGGER CODE FOLLOWS ************
-- Use this code in case you need to drop any triggers created earlier, in case
DECLARE myCursor99 CURSOR
FOR
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM tblAudit
DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(8000), @sql varchar(8000)
SELECT @COLUMN_NAMES = ''
OPEN myCursor99
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @COLUMN_NAMES = @COLUMN_NAMES + ', ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = @TABLE_CATALOG AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
ORDER BY ORDINAL_POSITION
--Select @SQL = 'DROP ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR ON ' + @TABLE_SCHEMA + '.' +@TABLE_NAME
Select @SQL = 'DROP TRIGGER ' + @TABLE_SCHEMA + '_' + @TABLE_NAME + '_TR'
Select SQL = @SQL
EXEC(@SQL)
SELECT @SQL = ''
FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE myCursor99
DEALLOCATE myCursor99
GO
Criteria for the current month in a query
The other day I needed to create a query where it returns all records with dates for the current month. I did not want to hard code the beginning and ending dates in the query, so I came up with the following line of code you can paste into your query:
Between CDate(Month(Date()) & "/1/" & Year(Date())) And DateAdd("m",1,CDate(Month(Date()) & "/1/" & Year(Date())))-1
The criteria line above uses the "Between And" operators to calculate the first of the month and the end of the month of the current date. Enjoy!
Between CDate(Month(Date()) & "/1/" & Year(Date())) And DateAdd("m",1,CDate(Month(Date()) & "/1/" & Year(Date())))-1
The criteria line above uses the "Between And" operators to calculate the first of the month and the end of the month of the current date. Enjoy!
Bug will occur when using datasheet view in a sub form.
Here's a bug you will come across when using a subform in datasheet view:
"Property Not Found"
Here is the code that will trigger the criptic message from Access:
Private Sub MyField_BeforeUpdate(Cancel as Integer)
if somecondition = True then
Msgbox "Your message here, usually you can't save the record because of something"
Cancel = True
end if
End Sub
To fix the issue, replace Cancel = True with:
DoCmd.CancelEvent
"Property Not Found"
Here is the code that will trigger the criptic message from Access:
Private Sub MyField_BeforeUpdate(Cancel as Integer)
if somecondition = True then
Msgbox "Your message here, usually you can't save the record because of something"
Cancel = True
end if
End Sub
To fix the issue, replace Cancel = True with:
DoCmd.CancelEvent
Access function to return day of the week
Sometimes you need the actual day name of the date and not the numeric equivalent, the following function will do that for you:
Private Function MyWeekDayName(dteDate As Date) As String
'Will return "Monday", "Tuesday", etc. based on the date supplied
MyWeekDayName = WeekdayName(WeekDay(dteDate), False)
End Function
Example use in debugger window:
?MyWeekDayName(#10/19/2009#)
Monday
Private Function MyWeekDayName(dteDate As Date) As String
'Will return "Monday", "Tuesday", etc. based on the date supplied
MyWeekDayName = WeekdayName(WeekDay(dteDate), False)
End Function
Example use in debugger window:
?MyWeekDayName(#10/19/2009#)
Monday
Subscribe to:
Posts (Atom)