Odd occurance (Access 2000)

Topic

New Reply

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).

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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. 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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. 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote