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.

Working with Relations

Relationships between tables are crucial to preserving data integrity in MS-Access or any other relational database system. In these examples, I show both how to create and delete a relationship. The "create" example creates a one-to-many relationship, enforces referential integrity, uses a single field, without cascades.

You can certainly manage relations in Access using the GUI (Tools ~ Relationships) but one of my "best practices" is to code any table changes in a test environment, and then apply them to the production database.

Note: Lines are wrapped to fit the Blogger window - you can view the formatted examples at Working with Relations.

Sub AddRel_Nov_26_2007()
    ' create one-to-many relationship 
    ' from PrimaryTable to ForeignTable
    Const PrimaryTable = 
        "tlkpScholarshipStatus"
    Const PrimaryField = "Status"
    '
    Const ForeignTable = "tblSCH_Applicant"
    Const ForeignField = "Status"
    '
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    '
    Set db = CurrentDb
    Set rel = db.CreateRelation
    With rel
    .Name = PrimaryTable & ForeignTable
    .Table = PrimaryTable
    .ForeignTable = ForeignTable
    .Fields.Append rel.CreateField(PrimaryField)
    .Fields(PrimaryField).ForeignName = ForeignField
    End With
    db.Relations.Append rel
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
End Sub

Sub DelRel_Nov26_2007()
    ' delete the relationship
    ' from PrimaryTable to ForeignTable
    Const PrimaryTable = "tlkpScholarshipStatus"
    Const ForeignTable = "tblSCH_Applicant"
    ' relation was named 
    ' using concatenated table names
    Const RelationName = 
        "tlkpScholarshipStatustblSCH_Applicant"
    '
    Dim db As DAO.Database
    '
    Set db = CurrentDb
    db.Relations.Delete RelationName
    Set db = Nothing
End Sub

footnote:

This best practice of scripting table changes came in quite handy when working with an out-of-state customer. Instead of having the back-end database emailed for me to apply the changes, I simply coded the changes into a module in a newfront-end file & the customer ran the table-change code. This minimized downtime on their production system.

Saving Access Forms as Reports

Forms are the heart of an Access application, but sometimes a hard-copy is still required. A well-formatted form can be printed simply by using the DoCmd.PrintOut statement, but to print a single record you'll probably need to set a bookmark of some kind, apply a filter, print the form, and then use the bookmark to return to that same record.

Access provides for a "Save As Report" feature, so you can quickly create a Report that matches the form & then use DoCmd.OpenReport using a filter or where clause. Very handy. However, if you have code-behind in your form, Access will also carry that code into the report module which can cause compilation errors.

For example, if you have a Form_Open event, that code will be copied verbatim into the report module but it won't compile due to the class mismatch. If you do not compile your modules, Access 2003 will ignore that error, but Access 2007 is much less tolerant and it will report "an error loading ... continue loading project?" but either way you respond, 2007 will not run any portion of your application until the code is fixed.

Apparently, Access 2007 opens every single module in the database, so obsolete or old versions of objects can prevent an app from running, even if they are never called from the application.

Formatted MsgBox in Access

I wanted to create a formatted message box in Access, with the first line being bold, to duplicate the built-in delete confirmation message. In older versions of Access, you could use

MsgBox "First line is bold@Second line is normal@@"

but when Access moved from 97 to 2000 this feature went away.

I Google'd this & found several versions of a workaround, and here is what worked for me in Access 2003:

EDIT: 06/19/2008 this replaces the original code:

Function MsgBoxEx(sBoldText As String, sNormalText As String, iButtons As Integer, iIcon As Integer, sTitle As String) As Integer
Dim s As String
s = sBoldText & "@"
s = s & sNormalText & "@@"
MsgBoxEx = Eval("MsgBox(""" & s & """, " & iButtons + iIcon & ", '" & sTitle & "')")
End Function

This function will accept built-in constants for buttons and icons, and will return a value based on the buttons.

SQL Server Backup / Restore

I routinely take a backup from the production SQL 2005 Server and restore it to my dev system. I had previously published my code for backup and restore, which used backup devices but I found that was not necessary.

I have a folder on the production server named E:\SQL_Bill which is shared for me alone and is the target for my backup. I then zip and copy the file to my thumb drive, then copy and unzip the file to my dev system in the default backup folder.

Here is the backup script:

BACKUP DATABASE [lampsPAR]
TO DISK = N'E:\SQL_Bill\wm_LampsPAR.bak'
WITH
NOFORMAT,
INIT,
NAME = N'lampsPAR-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

and here is the restore script:

RESTORE DATABASE [LampsPAR]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\wm_LampsPAR.bak'
WITH
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

To create these scripts, all I had to do is use SSMS to set up my backup, but then I clicked the script button to generate the script in a new window. I then cancelled the backup & saved the script as a .sql file. I did the same for the restore script. What could be easier than that.

ANSI Warnings and Vista

I have an Access 2003 application that connects to a SQL Server 2005 database using pass-through queries that either contain T-SQL or execute stored procedures.

When some of the user PC's were upgraded to Windows Vista, I noted that sometimes new tables were being created in Access with names like "Admin - 00", "Admin - 01" and so forth. Each table contained one to three records that held ANSI warnings messages being returned from SQL Server. This had never occurred before under SQL 2000 or Windows XP.

For example, a message might be the familiar "Null value is eliminated by an aggregate or other SET operation" preceded by an error number.

The pass-through queries were all set for Log Messages = No, and I experimented with changing the ANSI settings on the ODBC connection for the SQL Native Client, but nothing made these message tables stop.

When executing certain T-SQL statements using either Query Analyzer (2000) or SSMS (2005) it is quite common for the developer to see these ANSI warnings displayed in the messages window, but they are not normally returned to the application. However, the combination of Access 2003, SQL Server 2005 and Windows Vista behaves differently from combinations of the prior products.

The only way I found to prevent these message tables was to step through the T-SQL in SSMS and locate & fix the offending code. For example, there might have been a statement

SUM(Payment) AS TotalPaid

which had to be revised as

SUM(ISNULL(Payment,0)) AS Total Paid

Another example that I found was a statement like

COUNT(DISTINCT InvoiceID)

in which case I had to add a statement

WHERE InvoiceID IS NOT NULL

to correct the problem. Fortunately, only certain users were triggering the creation of these message tables, so I was able to narrow the scope of my troubleshooting to the statements that those users were executing.

In the future, all new code will need to be checked so that ANSI warnings are not being generated, for compatibility with Windows Vista.

Linking Access tables to SQL Server

The question was, how do I link from SQL Server to an Access db and loop through all of the Access tables that end with "Data" ... My example uses a 2000-format file C:\BILL\Test2000.mdb

First, run this one-time to create the linked server:

sp_addlinkedserver
@server = 'Test2000',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = '',
@datasrc = 'C:\BILL\Test2000.mdb'
GO

sp_addlinkedsrvlogin
@rmtsrvname='Test2000',
@useself='False',
@rmtuser='Admin',
@rmtpassword=''
GO

Then, you can use this to build a temp table with the desired table names, and loop thru to view the data using a cursor and dynamic sql (shame on me):

CREATE TABLE #temp
(
TABLE_CAT varchar(MAX),
TABLE_SCHEM varchar(MAX),
TABLE_NAME varchar(MAX),
TABLE_TYPE varchar(MAX),
REMARKS varchar(MAX))

INSERT INTO #temp
EXEC sp_tables_ex 'Test2000','%Data'

DECLARE @tablename varchar(64)

DECLARE curTBL CURSOR FOR
SELECT TABLE_NAME FROM #temp

OPEN curTBL
FETCH NEXT FROM curTBL INTO
@tablename

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('SELECT * FROM Test2000...' + @tablename)
FETCH NEXT FROM curTBL INTO
@tablename
END

CLOSE curTBL
DEALLOCATE curTBL

DROP TABLE #temp

AVG Free and Access

AVG Free automatically scans MDB files when you open them, and this cannot be turned off in the Free edition. Even on a fast PC, it could take 20-30 seconds depending on the size of the database.

This presents itself in two ways:

1. You are using Access 2000-2003, just opened the db moments ago, and you want to do a quick compact & repair. Access tells you either "Cannot open database '(database name)'. It may not be a database that your application recognizes, or the file may be corrupt." or "Cannot open 'database name', file already in use."

If you wait a little while and try it again, the compact & repair succeeds.

2. You are using Access 2007 and wish to convert a 2000-2002 format MDB into the new ACCDB format. Access tells you "You attempted to open a database that is already opened exclusively by user 'Admin' on machine '(machine name)'. Try again when the database is available."

In this case, waiting does not work. You need to close, and then re-open the database while holding the Shift key, to bypass any AutoExec or startup code. Then, you wait for little while and repeat the Convert and it will succeed.

Tracking changes in Access

Here is a method to track edits in MS-Access. The goal is to record the "who what when where" of edits made in an Access database. Since Access does not have triggers like SQL Server, we will be working at the Form level.

NOTE - UPDATED 11/11/2007
added table name to tracker

To keep things readable within the blogger format, you'll need to visit three links that will display details of what is described here.

First, you will add three objects to your database:
1. A VBA module with Windows API calls to identify the user and PC name. This code has been tested with Windows 2000, XP and Vista 32-bit editions.
2. A VBA module to examine the current record & determine what has changed to the record
3. A table to store the results. For text fields, I've indicated the length in the Description. Here is a VBA module that will create the table for you.

After adding these objects, modify each form as follows:
1. Set the Tag property for the form = the name of the underlying table.
2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere.
3. Add the Form_BeforeUpdate event and invoke the tracking code using:

TrackChanges Me

4. If you are using subforms, you'll need to perform these three steps for each subform as well.

UPDATED 09/24/2008 - IMPORTANT NOTE
You can run a Sub in Access by using either

TrackChanges Me

or

Call TrackChanges(Me)

but you cannot use

TrackChanges(Me)

that will result in a compile error.

NOTE - UPDATED 05/23/2010
tracking blank or null fields

There was an important note left out of the original post. In order to properly track when a field is blanked-out, the fields in the tracking table Field_OldValue and Field_NewValue should be set for AllowZeroLength = True. The updated "create table" code is here:

http://www.wvmitchell.com/blogger/Create_tbl__ChangeTracker.htm

if you create the table manually you will need to set that property in the design view.

Error 3146: ODBC Call Failed

When you first create a link to an ODBC data source, Access will memorize the underlying table design, but it will not automatically update itself if the table design in changed; resulting in Error 3146 ODBC Call Failed.

This is in sharp contrast to the behavior when linking to external native Access tables - Access will automatically update the table definition, so no action is required when the design of the linked table is changed.

To update links to ODBC data sources, in Access 2002 or newer you can right-click on any linked table and then click on Linked Table Manager. This displays a list of all linked tables and their locations. Simply check-mark the table(s) to be refreshed and click OK. Access will then update the memorized table designs. (In older Access versions, the Linked Table Manager is found on the Tools menu.)

One caveat - you cannot use this method if you are trying to relink to an ODBC data source that uses a different driver. For example, if you have a DSN that points to a SQL Server 2000 database using the SQL Server provider, and you change the DSN to point to a SQL Server 2005 database using the SQL Native Client; this method will not work - Access will not recognize the new connection. In that case, you will need to actually delete the linked tables and then re-link using your new DSN.

Scripting T-SQL

Sql Server displays the creation date for db objects, but does not show the last modified date. That makes it hard to tell if and when an object like a view, sp, or UDF was last changed.

For that reason, I always write my script and save it to a disk file, using the following general structure:

DROP PROC dbo.MyProcedure
GO

CREATE PROC dbo.MyProcedure
AS
-- actual code goes here
GO
GRANT EXECUTE ON dbo.MyProcedure TO [MyUserName]
GO

This has two benefits:
1. I can run the script on the dev system to verify it works, and make any needed changes
2. When I run the final script on the production system, the creation date will actually represent the date last modified

IMPORTANT: You cannot use this method for tables, since you will lose all the data therein!

Create SQL Server database query with MS Excel

1. Start a new XL workbook
2. Click Data ~ Import External Data ~ New Database Query
3. At the "Choose Data Source" dialog, either select an existing DSN or create a new one, then click OK.
4. At the "SQL Server Login" dialog, enter your credentials and click OK
5. At the "Query Wizard" dialog, click Cancel
6. At the "Do you want to continue editing this query in Microsoft Query?" click Yes.
7. At the "Add Tables" dialog, click Close
8. On the MS Query toolbar, click the SQL button
9. Enter your SQL statement in the "SQL" box and click OK
10. If your SQL statement is a stored procedure, it will say "SQL Query can't be represented graphically. Continue anyway?" - click OK
11. You should now see your data displayed in MS Query
12. Click the Return Data button (looks like an exit door) and MS Query will close
13. Now you're back in XL. At the "Import Data" dialog, select the location for the top left corner of where you want the data. It defaults to cell A1 which is usually where you do want it to start; click OK
14. Excel will now retrieve the data and place it starting from the chosen location

Using BETWEEN with dates

T-SQL has a handy BETWEEN statement that makes it easy to apply date range criteria. The basic usage is

WHERE dtSomeDate BETWEEN '1/1/2007' and '1/31/2007'

...which can replace the cumbersome

WHERE dtSomeDate >= '1/1/2007' AND dtSomeDate <= '1/31/2007'

either expression would (in theory) retrieve any date during January 2007. But SQL Server dates can contain a time component - so if dtSomeDate was actually '1/31/2007 08:00:00' meaning 8:00 AM on the 31st, that date actually would fall outside the desired range.

I picked up a tip from SqlServerCentral that removes the time - you could write
DATEADD(d,DATEDIFF(d,0,dtSomeDate),0)
and I even re-posted that tip at my web site, but every time I need to use it, I have to go look it up. So I wrote the following function that is easy to remember & can be invoked whenever comparing dates:


CREATE FUNCTION dbo.fnNoTime
(@DateTime datetime)
RETURNS datetime
AS
BEGIN
DECLARE @date smalldatetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
RETURN @date
END

...returning to my original example, you would use the function as follows:

WHERE dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' and '1/31/2007'

Works with Vista

There are a surprising numbers of free utilities that work with Windows Vista.

My faves are FoxIt Reader, a fast & free replacement for Adobe Reader; NotePad2, a smart NotePad replacement for programmers that color-codes text based on file extension; and Ad-Aware SE Personal, which scans for spyware & other such nasties.

Thus far, every PDF that I've opened with FoxIt has displayed just fine, without the long loading times (and frequent bloated updates) of Acrobat. The only downside is that sometimes IE doesn't recognize that FoxIt can open a PDF, and presents the dumb "Windows cannot open..." message.

NotePad2 is a must for programmers (like me) who sometimes still print out listings, especially for long or complex programs. It uses the file extension to apply the appropriate color scheme for that file type. The source code is available for the curious (or compulsive tweaker) and I've even had the program running under Linux using the wine Windows emulator.

Next is the excellent Ad-Aware which protects from spyware, much more effectively than the native Windows Defender - even after this morning's Defender definition update, Ad-Aware found five tracking cookies & allowed for their easy quarantine & subsequent removal.

My final fave is a VB6 program that I wrote to print the screen, and what do you know? It also works with Vista. Imagine that.