Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    Carpy Diem, it's .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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?
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    I realise you are talking about a server, just just to add my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>....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.
    Regards,
    Rudi

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Carpy Diem, it&#39;s .

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Carpy Diem, it&#39;s .

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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"

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hans,

    I'll try this and let you know!
    Carpy Diem, it&#39;s .

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> 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. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Carpy Diem, it&#39;s .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •