Access VB references changing order spontaneously

[marton]

Hi there, I'm new to posting on the forum, and I've been working with MS Access for a couple of years, but and other than that know nothing about programming.

I have a fairly simple, networked, Access database created with Access 2003. It is accessed from several different computers in my department (I don't know which computers or how often). About 8 people are entering data with an unbound form (which also records computer ID, date and time for new records so they can be tracked) and others just look at the data or edit the data table (which is not recorded in the database).

About once a week I am getting a problem whereby DAO and ADODB swap around in the VB references list. DAO needs to be above ADODB in the list for the unbound form to work, because the coding for the unbound form uses .edit. So when the references swap around, the unbound form no longer saves new records. Also, upon opening the unbound form, this error message appears when DAO and ADODB have swapped around:

"You entered an expression that has an invalid reference to the property AsianLineBreak."

AsianLineBreak doesn't appear to be a DAO or ADODB reference, and I do not use it anywhere in the database, so I don't understand why this particular message appears?

I have a VB sub which tests if the VB reference order is wrong and produces a warning message, which works when I swap the references around and test it, but which in practice, doesn't seem to work when the problem occurs. So unfortunately when the user initially encounters the problem, the first they know is the Access warning message described above, followed by Access closing down.

As soon as I put the references back into the right order, the database works fine again. But obviously I am not always around to fix it, and would rather they didn't change in the first place.

What is my best course of action? Is there a way to stop the references changing order?

Or should I give up and try to find alternative code to avoid using DAO in the first place?!

Advice would be very much appreciated!
Many thanks.

[Scott Price]

Have a look at these two links to Allen Browne's website on Preventing Corruption and Recovering from Corruption

Offhand, there is no reason I know of, other than incipient corruption, for the references to change like you are experiencing.

Regards,
Scott

[marton]

Thanks Scott.

I rebuilt the database in question a week ago and I am pleased to say that the problem of references changing order on the VB reference list hasn't occurred since.

But a couple of times I have found that the database has become decompiled for no reason I can work out. But that doesn't seem to stop anything working (in the short term anyway!), so I am no longer getting panic phone calls which is the main thing!

Cheers,
Marton

[JKing]

If you're having ambiguity problems be sure that you are also referencing the proper library that you want to use when creating objects.

'Right way
Dim rst As DAO.Recordset
'or
Dim rst As ADODB.Recordset
 
'Wrong way
Dim rst As Recordset
 

[marton]

Thanks JKing for your advice - I did find one Dim rst As Recordset in my database. I then had a trawl through and found one instance where I didn't close a recordset after using it.

So I put these right, but the database was still becoming decompiled. I then realised it was happening when the database was opened by a few particular computers with Access 2000. I have asked those users to use a front-end database to access the networked database. And since then... no more problems!

So thanks for your help. It looks like: 1) the database (possibly) needed to be rebuilt; 2) the references to recordset needed to be made unambiguous; and 3) Access 2000 users needed to be discouraged from opening it directly.

Cheers
Marton (much happier now!)

[Scott Price]

Thanks for posting back with what worked for you Marton (much happier now!) :-)

Glad you got it working.

It's interesting that you mention the database decompiling... That is one of the first warning signs of corruption... I wonder what it is about that particular user's Access 2000 installation that was causing this? (or maybe that particular user poking around in the code?)

It's not totally out of the picture that there could be some un-handled errors only showing up on his/her computer caused by missing references, etc...

Regards,
Scott