Microsoft Access Code Warrior

Part 3: Why I grew to love Microsoft Access Data Projects

2010-07-13T12:31:00.001-07:00

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

2010-07-13T12:30:00.001-07:00

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.

Part 2 Access ADP Tip : Get rid of the login box on startup

2010-07-13T12:29:00.001-07:00

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.

Part 1 Access ADP Tip: Using ADP ServerFilter property

2010-07-13T12:28:00.003-07:00

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.

How to create PDFs in Word using Access VBA

2010-07-13T12:28:00.001-07:00

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.

Using a SQL Server stored procedure with a insert query in Access

2010-07-13T12:27:00.001-07:00

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"

Using SQL Server Views with Access: Index needed for editing data.

2010-07-13T12:25:00.001-07:00

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.

SQL Server: Easily Grant Select to All Tables and/or Views in a Database

2010-07-13T12:24:00.001-07:00

(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

Stuck on how to connect to a data source?

2010-07-13T12:23:00.003-07:00

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!

Create DELETE Audit Tables in SQL Server

2010-07-13T12:23:00.001-07:00

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

Criteria for the current month in a query

2010-07-13T12:22:00.001-07:00

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!

Bug will occur when using datasheet view in a sub form.

2010-07-13T12:21:00.003-07:00

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

Access function to return day of the week

2010-07-13T12:21:00.001-07:00

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

How to create Outlook message from Access

2009-11-06T08:00:00.000-08:00

Note: If you need to send out a massive amount of emails this code is not for you, instead we recommend FMS's Total Access Emailer.

Note 2: You must add a reference to Outlook in your Access project in order for this code to work, if you need an alternate version that does not require it then look at the next section of this post below:

Dim objOutlook As Outlook.Application

Dim objOutLookMsg As Outlook.MailItem

Dim strBody as String

strBody = "Insert you body message here."

Set objOutlook = New Outlook.Application

Set objOutLookMsg = objOutlook.CreateItem(0)

With objOutLookMsg

.To = "w@what_Ever.com"

.CC = "any@what_ever.com"

.Subject = "Enter Your Subject Here"

.Body = strBody

.Send

End With

Code without a reference to Outlook:

Dim objOutlook As Object

Dim objOutLookMsg As Object

Dim strBody as String

strBody = "Insert you body message here."

Set objOutlook = CreateObject("Outlook.Application")

Set objOutLookMsg = objOutlook.CreateItem(0)

With objOutLookMsg

.To = "w@what_Ever.com"

.CC = "any@what_ever.com"

.Subject = "Enter Your Subject Here"

.Body = strBody

.Send

End With

Criteria for the current month in a query

2009-11-06T07:58:00.000-08:00

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!