Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Odd occurance (Access 2000)

    I'm doing some contract work for a client. In my copy of the database, I modified the rowsource of a listbox on a form, then compiled the database just to make sure everything was OK. I then imported the from into a transport database that I sent to the client. At client site, the form was imported into the production database, and the database was compiled and compacted.

    When I got a new copy of the production database, my change did not appear to be in there. The listbox performed the way it had before the change. I opened the form and opened the SQL statement that was the rowsource, and it contained the change I had made. I closed and saved the form. When I opened the form again, the listbox worked like it should with my change! I verified this behavior on another machine, even recompiling the database, but the change to the rowsource did not "take effect" until I opened the form in design mode and then saved it.

    I assume this has something to do with a compiled bit of code that Access is using. But how to prevent this from happening again? I didn't try decompiling then recompiling. In this situation, we have 3 programmers who all submit their work and have it imported into the production database (no one works in it directly).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Odd occurance (Access 2000)

    So the rowsource worked properly before you imported it into the transfer database, or did you test that? Access 2000 was notorious for partially compiled mdb files, so that might have crept in somehow. The only cure for that was a decompile first. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Odd occurance (Access 2000)

    I know it worked before I started the transport process.

    We've had other issues also, but at least they are detectable and solvable. For example, often after importing and doing a compile, a compile error will pop-up when referring to a field in the form's underlying recordsource. This requires clicking on the recordsource property and opening the SQL statement (or just reselecting the table or stored query from the dropdown list), then moving off the property. This makes Access aware (again) of what the recordsource fields are. It is annoying, but at least you can deal with it.

    I guess I'll see if decompiling works.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Odd occurance (Access 2000)

    I've seen that compile error in our source safe-managed projects. The odd thing about it is that shared objects in one project will throw that error but not the same shared objects in another project. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I don't have a suggestion because we've never found an answer, but we issue whole new front ends that we know compile and run rather than trying to do piecemeal updates.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Odd occurance (Access 2000)

    That's basically what we do also, the clients get a whole new database. The problem is, how do we know the new database will run correctly? I tried decompiling then recompiling, but the problem I described before persisted until I manually opened the form in design mode and then selected the rowsource for that listbox. I'd report this to Microsoft (as I think it is a bonafide bug), but they no longer have a mechanism for reporting bugs, at least not from ordinary users.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Odd occurance (Access 2000)

    My experience has been that once we go through the objects modifying them in some way so that they compile, the whole project stays compiled. We have had no reports from users of problems, but our users are not allowed into the objects in design mode, so there should be no way for them to uncompile the project.
    Charlotte

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Odd occurance (Access 2000)

    I'm not talking about the users making changes. As a contractor, I make changes to objects and compile my version of the database to make sure there are no problems. I then copy the objects I changed into a transport database and send them to main office which imports them into the full production database (and does compact and compile). This new compiled database is distributed to clients.

    Again, the situation was I made a change to the rowsource of a listbox (it was an SQL statement, not a saved query name), and no other changes to the form were made. Even after compacting then compiling the full production database, the listbox displayed info based on the old SQL statement. Decompiling then compiling didn't help. The only thing that helped was to open the form in design mode and click on the SQL statement and open it up, then close it and save the form. This was the only time I have ever seen this behavior.

    So it seems to me that Access "remembered" how the SQL statement was optimized, and used this optimization scheme to fill the listbox. Only after manually going into the field did Access re-optimize. Or at least that's how I see it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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