How can i force Access to refresh it's Object references when the database is opened (1 Viewer)

[AlexDoran]

Hi all, I have a really annoying problem with a users workstation. There is a development version of the access database that is distributed to workstations using a .bat file. The database is working perfectly on all workstations other than one.

The particular workstation will not let the user run specific Query's, giving a 3075 Error. I have been across the web and realized that this is a problem with the references. However there are no MISSING references, but if i force access to refresh its references, by checking an un-selected reference, then again removing the reference, the database works perfectly. It works perfectly until the user again runs the .bat and receives the latest version of the DB.

I have un-registered and re-registered the references using Regsvr32.exe but still the problem persists.

I have performed a COMPLETELY fresh install of office but still the problem persists.

Is there a way to force access to refresh its references via VBA on open?

Failing that i will be forced to do a clean install of the machine.

The machine developed this problem within a matter of 30 minutes and has been working perfectly for 2-3 years before hand.

Any help is greatly appreciated. :banghead::banghead::banghead:

Thanks

Alex.

 

[AlexDoran]

User can't run query's, strange problem with references?

Hi all, I have a really annoying problem with a users workstation. There is a development version of the access database that is distributed to workstations using a .bat file. The database is working perfectly on all workstations other than one.

The particular workstation will not let the user run specific Query's, giving a 3075 Error. I have been across the web and realized that this is a problem with the references. However there are no MISSING references, but if i force access to refresh its references, by checking an un-selected reference, then again removing the reference, the database works perfectly. It works perfectly until the user again runs the .bat and receives the latest version of the DB.

I have un-registered and re-registered the references using Regsvr32.exe but still the problem persists.

I have performed a COMPLETELY fresh install of office but still the problem persists.

Is there a way to force access to refresh its references via VBA on open?

Failing that i will be forced to do a clean install of the machine.

The machine developed this problem within a matter of 30 minutes and has been working perfectly for 2-3 years before hand.

Any help is greatly appreciated. :banghead::banghead::banghead:

Thanks

Alex.

 

[CJ_London]

Re: User can't run query's, strange problem with references?

depends on what references are creating the issue, but have you considered late binding? Also, is this user on a different bit version of access to everyone else?

If you have API calls then investigate PtrSafe

And are you supply an accdb, accde or accdr?

 

[AlexDoran]

Re: User can't run query's, strange problem with references?

Hi there and thanks for the reply.

I was under the impression from internet research that no particular reference is missing or broken. The exact error message that is received is:

"Funtion is not available in expressions in query expression".

Only particular queries give this error when run (I believe queries that are using Date criteria).

However once i have forced access to refresh the references list, the problem is fixed.


The user is on a 32Bit operating system, there are a mixture of 64Bit and 32Bit operating systems within the building.

Excuse me but i am a mere novice, i am aware of Early & Late bindings are but have never used them myself so my understanding of them is not great.

Thanks

Alex

 

[CJ_London]

Re: User can't run query's, strange problem with references?

The user is on a 32Bit operating system, there are a mixture of 64Bit and 32Bit operating systems within the building

I was referring to Access

Only particular queries give this error when run (I believe queries that are using Date criteria).

Have you tried simplifying the query to see if it is the Date function? or perhaps you are formatting a date in the criteria?

Sometimes queries can become corrupted, although not visible so. This can happen if queries have been changed from being select to update/delete/insert/crosstab and then back again several times. The solution is to either a) rebuild in a new query from scratch or b) to copy the sql into notepad then create a new query and copy and paste the sql from notepad (don't just paste the code from the query)

Have you tried just compiling the code and supplying an accde?

Do you have Option Explicit in your modules?

 

[pbaldy]

I think Doug discusses that here:

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

 

[gemma-the-husky]

is the problem user using a different access version to the others? it's hard to understand the error otherwise

I am sure you realise but you cannot fix reference issues in a .de database.

 

[CJ_London]

responders please note that the OP already has this thread going

http://www.access-programmers.co.uk/forums/showthread.php?t=274672

 

[pbaldy]

Threads merged. In the future, please don't post the same question twice.

 

[AlexDoran]

Sorry about the double thread, I thought my first attempt failed to post so tried again in another section.

All users have been upgraded to Office 2010 32Bit September last year.

The database is also developed in 2010 32Bit, the file is distributed as .mdb (the database is 15+ Years old). The .mdb is distributed locally to each machine, and connected to a backend DB.

It really is baffling me, all machines are running the same Library Versions of DAO, ADO etc.

Visually everything appears normal with the references, no missing references, compiles with no errors.

h**p://i1381.photobucket.com/albums/ah213/alexdoran/Screen2_zps33273eac.png

But if i try to run the report it throws this error, regarding the query that feeds it:

h**p://i1381.photobucket.com/albums/ah213/alexdoran/Screen1_zps7e353b83.png

Then if i Check a current no Un-Checked reference, exit the database, re-enter the database, and Un-Check the reference (forcing access to refresh its reference list) then the report runs fine and no errors are given:

h**p://i1381.photobucket.com/albums/ah213/alexdoran/Screen3_zpsb2bcc770.png

Its REALLY baffling me, what makes it worse is that it is Managing Directors Machine


PS im not allowed to link images yet so i have to code tag the URL.

 

[AlexDoran]

Re: User can't run query's, strange problem with references?

I was referring to Access

Have you tried simplifying the query to see if it is the Date function? or perhaps you are formatting a date in the criteria?

Sometimes queries can become corrupted, although not visible so. This can happen if queries have been changed from being select to update/delete/insert/crosstab and then back again several times. The solution is to either a) rebuild in a new query from scratch or b) to copy the sql into notepad then create a new query and copy and paste the sql from notepad (don't just paste the code from the query)

Have you tried just compiling the code and supplying an accde?

Do you have Option Explicit in your modules?

Option Explicit is present, i understand your point with certain queries becoming corrupt, however these queries were created a long time ago, and are rarely edited, and have never been modified in their query type.

Im going to try accde method and will report back.

Thanks for the help so far it really is appreciated. Thank you