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.