A forum created 01/17/2005 to facilitate communication between database developers and users.
Friday, December 28, 2007
Access 2007 problems
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.
Saturday, December 22, 2007
Last Restore Date in MSSQL
CREATE PROC dbo.procLastRestoreDates
MAX(restore_date) AS LastRestoreDate,
Friday, December 21, 2007
Change Tracking in Access - complete
Sunday, December 16, 2007
Linking Access tables to SQL Server - III
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.
Wednesday, December 12, 2007
Printing from Vista
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.
Saturday, December 08, 2007
Last Modified Date
In this example, I was using a form named frmSwitchboard...
WHERE Name="frmSwitchboard" AND Type=-32768
..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
Function LastModified_Report() As Date
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()
Wednesday, December 05, 2007
Linking Access tables to SQL Server - II
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
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:
- It can create a linked server to an Access 97 db but cannot access the data (error 7303)
- 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.
Monday, November 26, 2007
Working with Relations
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.
' create one-to-many relationship
' from PrimaryTable to ForeignTable
Const PrimaryTable =
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
.Name = PrimaryTable & ForeignTable
.Table = PrimaryTable
.ForeignTable = ForeignTable
.Fields(PrimaryField).ForeignName = ForeignField
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
' delete the relationship
' from PrimaryTable to ForeignTable
Const PrimaryTable = "tlkpScholarshipStatus"
Const ForeignTable = "tblSCH_Applicant"
' relation was named
' using concatenated table names
Const RelationName =
Dim db As DAO.Database
Set db = CurrentDb
Set db = Nothing
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.
Wednesday, November 21, 2007
Saving Access Forms as Reports
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.
Saturday, November 10, 2007
Formatted MsgBox in Access
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 & "')")
This function will accept built-in constants for buttons and icons, and will return a value based on the buttons.
Sunday, November 04, 2007
SQL Server Backup / Restore
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'
NAME = N'lampsPAR-Full Database Backup',
STATS = 10
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'
FILE = 1,
STATS = 10
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.
Wednesday, October 17, 2007
ANSI Warnings and Vista
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
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.
Wednesday, August 08, 2007
Linking Access tables to SQL Server
First, run this one-time to create the linked server:
@server = 'Test2000',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = '',
@datasrc = 'C:\BILL\Test2000.mdb'
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
INSERT INTO #temp
EXEC sp_tables_ex 'Test2000','%Data'
DECLARE @tablename varchar(64)
DECLARE curTBL CURSOR FOR
SELECT TABLE_NAME FROM #temp
FETCH NEXT FROM curTBL INTO
WHILE @@FETCH_STATUS = 0
EXEC ('SELECT * FROM Test2000...' + @tablename)
FETCH NEXT FROM curTBL INTO
DROP TABLE #temp
Thursday, July 26, 2007
AVG Free and Access
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.
Sunday, July 15, 2007
Tracking changes in Access
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:
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
but you cannot use
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:
if you create the table manually you will need to set that property in the design view.
Saturday, June 23, 2007
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.
Sunday, May 27, 2007
For that reason, I always write my script and save it to a disk file, using the following general structure:
DROP PROC dbo.MyProcedure
CREATE PROC dbo.MyProcedure
-- actual code goes here
GRANT EXECUTE ON dbo.MyProcedure TO [MyUserName]
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!
Saturday, May 12, 2007
Create SQL Server database query with MS Excel
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
Tuesday, April 24, 2007
Using BETWEEN with dates
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
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
DECLARE @date smalldatetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
...returning to my original example, you would use the function as follows:
WHERE dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' and '1/31/2007'
Saturday, April 07, 2007
Works with 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.