Friday, December 28, 2007
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
CREATE PROC dbo.procLastRestoreDates
MAX(restore_date) AS LastRestoreDate,
Friday, December 21, 2007
Sunday, December 16, 2007
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
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
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
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.