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