Switching versions during processing (2000 vs 2002 (Access 2000/2002)

Topic

New Reply

We have Access 2000 and 2002 installed on the same server. I know we’ve had a few issues with jobs running on the server where a 2002 database is being updated and then the server “thinks” the next job should also be in 2002, so opens the 2000 database and then acts up because of VBA code that works in 2000 and not 2002.

This week, however, during the execution of a macro that executed some VBA code, it appears that the server switched versions in the middle of the process. The VBA code is designed to loop through a table and send out emails embedded with data for each entity. It successfully processed two, then switched over to 2002 and errored out on :
DoCmd.SendObject acSendReport, “rpt_TblFCSTRpt”, acFormatRTF, Recipient, Recipient1, Recipient2, MailAbout, MailBody, 0

It then sat there until I went in and killed the process. The only reason why I knew that it was now opening with 2002 is that when I opened the database, I saw the “2000 Version” at the top.

We can’t upgrade all the databases to 2002 and can’t “downgrade” the 2002 database to 2000. Any suggestions?

Reply | Quote

Replies

I don’t see how it would be possible to switch from Access 2000 to Access 2002 halfway through a loop. Or do you mean that the loop is executed from another application, and that Access is started again for each iteration through the loop?

Also, why would the line of code you posted cause problems in Access 2002? It looks perfectly acceptable to me.

Reply | Quote

Okay, since I’m confusing everyone, let me elaborate.

1. we run a job from another piece of software (Launchpad).

2. Launchpad opens the Access db, finds the macro and runs it.

3. The macro calls code in a module to loop through the table, gather date based on the user, and then embeds this data into an email to be sent to the user and a manager. Here’s the code:

Function SendCustRpt()
Dim tempFilePath As String
Dim FileContents As String
Dim Recipient As String
Dim Recipient1 As String
Dim Recipient2 As String
Dim MailAbout As String
Dim MailBody As String
Dim Mailatt As String
Dim fnum As Variant
Dim bpurge As Boolean
Dim bpreview As Boolean
Dim fOKToExit As Boolean
Dim tmpItem1 As String
Dim tmpLastItem As String
Dim lastcustid As String

DoCmd.OpenQuery “qryFirstCust” – this creates a temporary table for the first customer in the table that is available
DoCmd.OpenQuery “qryfirstMgrMail” – this gets the email of the person getting the data
Do While RecCountCust 0 – this counts the number of records in the temporary table created above
Recipient = “name2@company.com”
Recipient1 = DLookup(“mailname”, “qryTBLFCSTRpt”) & “@company.com”
Recipient2 = “name1@company.com”
MailAbout = “Changes for ” & DLookup(“CUST_ID”, “qryTBLFCSTRpt”) & ” – ” & DLookup(“CUST_NAME”, “qryTBLFCSTRpt”)
MailBody = “Attached please find the report for ” & Format(Now(), “mm/dd/yy”)
DoCmd.SendObject acSendReport, “rpt_TblFCSTRpt”, acFormatRTF, Recipient, Recipient1, Recipient2, MailAbout, MailBody, 0
DoCmd.OpenQuery “qupdtblFCSTRpt” – this updates that the data has been processed
DoCmd.OpenQuery “qryFirstCust” – this selects the next customer
DoCmd.OpenQuery “qryfirstMgrMail”
Loop

End Function

4. Usually, around 9:30pm, I log remotely onto the server to check to make sure things are running smoothly. All I saw was an error message “unable to process….”(sorry, I was tired both nights and didn’t screen-capture the message), but when I clicked ok to debug, it pointed to the line on the code that was sending the email.

5. On Wednesday night (the first time this happened) I didn’t think much of it, as I saw in the server’s Outlook 2000 sent box, that there were two “yep, you got data” emails. I wasn’t at work on yesterday so I couldn’t really look into this further. Last night, however, it stopped at the same place but there were no outgoing emails.

6. When I came into work today, I opened the database on the server and this is when I saw the 2002. I then checked the table and there should have been 4 emails going out on Wednesday night. I can only surmise that something happened between loop 2 and loop 3 of the process.

I can see that there would be an advantage to splitting up the hard drive for the different versions of Access, but this isn’t a option here. And, fortunately, this is the first time this application has acted up since we put it on the server a few years ago. I was just wondering if anyone else ever had this problem because it’s new to me.

Reply | Quote

It is absolutely impossible to switch applications halfway through a loop. When Launchpad opens the database, it is opened in either Access 2000 or Access 2002, and all code will be executed in that version of Access, until the database is closed again. Switching in the middle of the code is technically impossible. So the database must have been opened in Access 2002 from the start.

I still don’t see why the code would cause problems in Access 2002.

I don’t know how the database is opened from Launchpad (I’m not familiar with this piece of software), but can’t you specify that the database should be opened in Access 2000?

Reply | Quote

Hans,

All Launchpad does is perform the automatron version of what you would do if you had nothing better to do at night but to open the database, hit F11 to clear the main screen, go to the macro tab and double-click on the macro. You can’t specify what Access version you are using since you’re merely double-clicking on the database.

So I’ll keep an eye on this and see if it happens again.

Reply | Quote

Again, I don’t know how Launchpad works, but if you specify a command line to open the database, you could include the path to the correct version of the Access application with it, e.g.

“C:Program FilesMicrosoft OfficeOfficeMSAccess.exe” “C:MyFilesMyDatabase.mdb”

Reply | Quote

Hans,

I’ll try this and let you know!

Reply | Quote

It works! Or at least, I got the database to open with the correct version. I can’t wait to get this into production.

Thanks Hans! Once again, you have come to my rescue.

Reply | Quote

I’m a little bit confused – happens regularly by the way – I think what you implied is that there is some automated process that opens a database on the server and runs a series of macros and VBA. But in general Access 2002 will run anything that you can run with 2000 – there are a few VBA things that were tightened up but in general a 2000 database can be run using 2002. In addition, the code you have should work in either version. There is an issue with Access 2000, as it can’t run databases that are in 2002 format, but we never use the 2002 format if we can avoid it. It tends to bloat like crazy and in general seems a little less stable, even though it was supposed to be better. Have I missed something important here?

Reply | Quote

I realise you are talking about a server, just just to add my ….We often run verious versions on the same PC’s for training purposes and it works well when the various versions are installed to different partitions of the harddrive. IE, Access 97 on the C:, Access 2000 on the E: and XP on the F: drive partitions.
It will also help if at installation time you specify different folder names to prevent mix ups.

Reply | Quote