Access and mail merge

Recently I've been running into some challenges using Access 2003 (2000 format) as the data source for mail merge, when using either Word or Publisher to produce merge documents.

In either of those two programs, you select your Access file as the data source, and normally you're presented with a list of all tables and select queries in the database. But on several occasions, with different Access files and different Word and Publisher documents, not all the items were listed. Not sure why. There is no workgroup security in place; only a single user; none of the items were tagged as hidden in Access. When the first attempt failed, we tried again using a different login that had full administrative permissions; same result.

When using Publisher to generate annual contribution letters for one customer, the Access file had a totals query built with the desired data, but Publisher did not show it on the list. At first I thought it was the totals query that Publisher did not like, but numerous other ordinary select queries did not show either.

When using Word to produce attendance certificates for another customer, again Access contained the desired query but Word could not see it. This was especially perplexing because when the Access & Word merge was set up back in 2004 it ran just fine (before the files were moved to a new server).

In both cases, I had to create an Excel export file and then use that for the merge, which worked as expected.

If I discover the reason those queries did not display, I'll post my findings.

EDIT: 45 minutes later...

Found the answer here (applies to 2003, too):

Re: missing queries in data source list (Word/Access 2007)

...as it turns out, if you are using functions written in VBA (or even a few of the built-in functions) in your query, it will not display in the Word or Publisher list. This behavior was definitely altered by a service pack, hotfix or other MS patch, because they used to work. What a letdown. This means that mail-merge queries need to avoid VBA functions to guarantee they can actually be used for mail merge.

The price of progress, in pursuit of enhanced security? Or a severe boo-boo from Redmond. You decide.

Last Restore Details for SQL

In a previous entry, I posted T-SQL code that will list the last restore dates for all db's on a SQL Server. The original purpose was to verify the dates that production backups were restored to the test environment.

This enhanced script will also display the name of the MDF file and the name & location of the backup file that was used for the restore. This can serve as a double-check that the dev servers are running the latest copies from production.

CREATE PROC [dbo].[procLastRestoreDetails]
(
@database_name AS varchar(50) = NULL
)
AS

SELECT
BS.database_name,
RH.restore_date AS Last_Restored_DateTime,
RF.destination_phys_name AS Restored_To_Database_Location,
BMF.physical_device_name AS Restored_From_Backup_File
FROM
msdb.dbo.restorehistory RH
INNER JOIN msdb.dbo.restorefile RF
ON RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb.dbo.backupset BS
ON RH.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id = BMF.media_set_id
WHERE
RF.destination_phys_name LIKE '%.mdf'
AND RH.backup_set_id =
(
SELECT
MAX(backup_set_id)
FROM
msdb.dbo.restorehistory
WHERE
destination_database_name = BS.database_name
)
AND
(
BS.database_name = @database_name
OR
@database_name IS NULL
)
ORDER BY
BS.database_name

Access File Size

This function will return the size of the current Access database file. This function requires a reference to the Microsoft Scripting Runtime library, usually located at:

C:\WINDOWS\system32\scrrun.dll

An Access mdb file can grow over time, especially if you have a lot of edits, empty / refill temp tables, and so forth. The purpose of this function is to get the current size, which (using other code not shown here) is then compared against some arbitrary value, and then alert the user if Compact and Repair is recommended to improve performance.


Function GetMyFileSize() As Long
Dim fso As FileSystemObject
Dim oFolder As Folder, oFile As File
'
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(CurrentProject.Path)
Set oFile = oFolder.Files(CurrentProject.Name)
GetMyFileSize = oFile.Size
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing
End Function

Access 2007 problems

I have an Access database that connects to SQL Server to provide custom reporting and batch updates of data. The file is 2000 format and was created using Access 2003 SP2.

I'm having quite a few issues using this file in Access 2007. My first attempts at 2007 were calamitous due to code artifacts that would not compile (oops). I found and fixed things that 2003 ignores, and once it compiled I thought things would be stable.

The first issue was when opening the compiled mdb, 2007 reported "an error loading module...continue loading project?" but the Form being complained about had compiled & worked perfectly fine using 2003. I then tried to open the form in design view but could not access the code-behind ("invalid whatever" error message). To fix this, I would simply open the mdb in 2003, copy the code-behind to the clipboard, set the "has module" = No, save the form, close, re-open in design view, and then paste the code back into the form and save it. OK, now the file works with Access 2007.

The second issue, when I added a new report using 2003. I took an existing report, modified it and did a save-as with a new name. When I subsequently opened the revised mdb with Access 2007, it complained about an "error loading..." and this time it named the original (existing) report. I fixed the report using the same method - by deleting and then reinserting the code module.

At this point, I have no explanation for this behavior. There aren't any users with Access older than 2003, so I can try converting the mdb to 2002 format to see if that stabilizes. Another option might be to convert the file to the new 2007 format as an accdb but that would require maintaing two versions of the same file, not a good idea. A third idea would be to create a new blank 2000 or 2002 database and then import all the objects into that; used to work in the past when an Access 97 file went bad.

Last Restore Date in MSSQL

I regularly take backups of three databases from a production SQL Server 2005, and restore to as many as four different development servers, for testing with different OS's and hardware configurations. I created this procedure in the master database on each dev server that will display the last restore dates for all the databases:

CREATE PROC dbo.procLastRestoreDates
AS
SELECT
  destination_database_name,
  MAX(restore_date) AS LastRestoreDate,
  restore_type
FROM
  msdb.dbo.restorehistory
GROUP BY
  destination_database_name,
  restore_type

Change Tracking in Access - complete

This previous blog post has now been compiled into a single Web page, you can view the full article at my "Tips and Downloads" at this location:

http://www.wvmitchell.com/tips/Change_Tracking_in_Access.html

Linking Access tables to SQL Server - III

This is the final chapter on creating a linked server from SQL 2005 to Access 97. I posted this issue at the MS Newsgroups, and the bottom line is that since Access 97 is no longer supported by MS, there are few resources to resolve this problem - The MSSQL gurus don't have an explanation why it would or would not work, and they had no suggestions as to server configuration that might help. During the course of testing, we have verified that it works on some installations of SQL Server 2005 Standard and Developer, but not in all cases.

Therefore, the only 100% solution would be to install a copy of SQL 2000 or MSDE and create a linked server to Access 97, since we know that always works, and then create another linked server from SQL 2005 to 2000, since that also works. Not an elegant solution.

If the Access 97 file could be upgraded to at least the 2000 version, then the linked server would work all the time.

Printing from Vista

In a previous post, I described how Office 2003 , and especially Access, requires updated print drivers for Windows Vista. In some cases Access will not allow design or printing of any reports unless new Vista drivers are installed.

I recently discovered that even Vista-compatible drivers can have issues - for example, an HP 1320 using the PCL 5 driver "works" with most apps, but Access reports have problems with graphics - for example, text will render correctly but horizontal lines will be bunched up near the top of the page, or missing altogether. The quick fix is to install and use the PCL 5e driver. According to HP's web site, the 5e and 6 drivers are currently being shipped with that printer.

Last Modified Date

I was looking for a way to display the last modified date on a form or report, but the two methods I had tried did not give the correct results.

In this example, I was using a form named frmSwitchboard...

Method 1
SELECT DateUpdate
FROM MSysObjects
WHERE Name="frmSwitchboard" AND Type=-32768

Method 2
CurrentDb.Containers("Forms").Documents("frmSwitchboard").LastUpdated

..both of these returned the creation date, not the true last modified date. I also tried looping through all the object properties, but there weren't any date properties to be found.

I Google'd around and found this thread:

Utter Access Discussion Forums - Date an Object was last modified

and then adapted what I found to create these two simple functions that you can reference on your form or report that will indeed display the same Modified date that shows in the Database Window:

Function LastModified_Form() As Date
LastModified_Form =
CurrentProject.AllForms(Application.CurrentObjectName).DateModified
End Function

Function LastModified_Report() As Date
LastModified_Report =
CurrentProject.AllReports(Application.CurrentObjectName).DateModified
End Function

Note that line breaks were added to better fit the Blogger window; within each function the statement should be all on one line.

Simply add an unbound text box and set the ControlSource as follows:

on a form, use =LastModified_Form()

on a report, use =LastModified_Report()

Linking Access tables to SQL Server - II

My previous post on this topic described creating a linked server to allow SQL Server to access data from a MS-Access database. A few more details are in order...

SQL Server 2000 can link to either Access 97 or 2000 formats using the Jet 4.0 OLEDB provider, and can use either of these types of statements against your linked server, in this example AccessDB:

SELECT * FROM AccessDB...TableName

or

SELECT * FROM OPENQUERY(AccessDB,'SELECT * FROM TableName')

Incidentally, you don't need to have any version of Access installed on the server, in order for this to work.

EDIT on 12/07/2007 -- if you experience the following issues, there is a config problem on the server -- the posted code does work 100% on some servers, but not others -- when I find the config solution I will post it. Back to the original post...

But SQL Server 2005 has two problems:

1. It can create a linked server to an Access 97 db but cannot access the data (error 7303)
2. The OPENQUERY syntax does not work for the Access 2000 db

I scoured the 'Net for a solution, but apparently very few people are still using 97-format databases. I suppose if you were connecting to a third-party app & you could not upgrade the Access file, you'd need to install SQL 2000 or MSDE and then create two linked servers - one link from SQL 2000 to Access 97, and the other from SQL 2005 to SQL 2000. What a mess. But it just might work.