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.