adding library references via VBA - worked before but now it doesn't

[gmurnag]

Hello

I work on a database that needs to work on French and English version of Windows and Office - and work on computers with any combination of Windows 2000, Windows XP, Office 2000, Office XP, Office 2003.

The database was transferable between all of the above but to make it work it was necessary to remove some reference libraries - in particular, Microsoft Excel object library, ADOX and ADOR because they have different locations depending on what combination of systems resided on the computer one was using.

The removing of the reference libaries and reinstalling on startup worked very well for the past 4 years, but now Access, will not allow it. It stops the VBA when it finds reference to Excel objects in the code before it has executed the code to add the object libraries.

The code I am using to add the libraries is as follows:

Set fs = CreateObject("scripting.filesystemobject")
 
Set ref = Application.References("adodb")
'if error boolAdd is set to true
If boolAdd Then
    'check to see if it a French system
    If fs.folderexists(strDriveLetter & cstrRoot & cstrComFilFr) Then
        Set ref = Application.References.AddFromFile(strDriveLetter & cstrRoot & cstrComFilFr & cstrSysADO & cstrADO)
    'otherwise set it for English system
    Else
        Set ref = Application.References.AddFromFile(strDriveLetter & cstrRoot & cstrComFil & cstrSysADO & cstrADO)
    End If
    boolAdd = False
End If
 
 

The problem is that VBA now stops the execution of the code because it says excel.worksheets is a user defined type that is not defined.

Any suggestions?

Thanks
George

[dima69]

I had the similar problem ones, and I tried to use the similar code to reconnect to library database at startup.
So I've seen two possible scenarios:
1. Access MDB updates the reference by himself, althogh the location is not the same.
2. Access fails to update the reference and raises an error, before the execution of my code.

In both cases, the above code of updating references becomes useless, not to mention it will not work in mde.

I'd suggest to use late binding when possible, especially with Excell.