CODEWITHSUNDEEP
ms-accessreferencevba
Alex Doran
Alex Doran

Reputation: 11

How can I force Access to refresh references when the database is opened

I have a problem with a user's 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.

Upvotes: 0

Views: 1389

Answers (1)

HansUp
HansUp

Reputation: 97131

You indicated you can resolve the problem manually "by checking an un-selected reference, then again removing the reference".

You can use VBA code to add and remove a reference. There are 2 different methods to add a reference: AddFromFile; and AddFromGuid. AddFromFile requires the full path of the file which provides the reference. AddFromGuid requires a GUID, and Major and Minor versions for the reference.

It's easy to gather that information from an existing reference. I added a reference to Excel Type Library, then inspected its properties in the Immediate window.

? References("Excel").FullPath
C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
? References("Excel").Guid
{00020813-0000-0000-C000-000000000046}
? References("Excel").Major
 1 
? References("Excel").Minor
 7

Then I can remove that reference and add it back again with the AddFromFile method.

References.Remove References("Excel")
References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"

Or with the AddFromGuid method ...

References.Remove References("Excel")
References.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 7

Those methods work with my database. However, my database does not have a references problem to start with. If yours does, I don't know if this will work.

Upvotes: 2

Related Questions