Tough Excel/Access/VBA Problem! Please Help!!!

[levileonards]

Here is my problem:

At my office we multiple computers running different versions of Windows (98, 2000, XP). I have an Excel 97 spreadsheet that uses VBA to create a form populated with data from an Access 97 Database and fill out Excel Templates from the same database. The Excel file is located on the network where everyone can run it. After someones computer died, I reinstalled Windows 2000. Now this same Excel file gives me an error when trying to run one of the macros. Upon investigation I found that this file works on some computers and not others. It worked on computers running Windows 98, 2000 and xp and yet did not work on other computers running Windows 2000. It was giving the error:
'(appECDB)Couldn't open C:\ec.mdb.'
Here is the code for appECDB:

Function appECDB() As Database
If ecdb Is Nothing Then
Dim f As String
f = ThisWorkbook.Names("EC_DB_FILE").RefersToRange.Value
On Error GoTo OPEN_ERROR
Set ecdb = DBEngine(0).OpenDatabase(f)
End If
Set appECDB = ecdb
Exit Function

OPEN_ERROR:
appQuit "(appECDB) Couldn't open " & f & "."
End Function

I compared everything that I could and nothing was different. I updated everything and reinstalled office. I started looking at some of the settings in Excel and the Visual Basic Editor. I had read something on another forum about the 'Refereces' in the Visual Basic Editor. One that came to my attention was 'Microsft DAO 3.51 Object Library', but it was already selected. I turned this setting off, and saved the file and tried it again. This time I got an compile error:
User-defined type not defined.
Where 'Private ec as Database' was higlighted as the undefined type.
I turned the reference to 'Microsoft DAO 3.51 Object Library' back on and saved the file.
When I tried it again, it worked...on everybody's computer! I was please because I thought I had solved the problem, but after a few days, everyone was complaining that though it did work on everyones computer now, it was horribly slow when importing and exporting data to the Access Database, before it was a few seconds, now it was several minutes. I had saved the old file, so we tried both files on different computers. On computers that the file worked before, the old file still worked and was fast, and the new file worked, but was slow. On computers that the file did not work before, the old file did not work, and the new file worked, but was slow. I am very confused as to why it works on some, but not others.

What confuses me even more is that all I did to 'fix' the problem was turn the reference to 'Microsoft DAO 3.51 Object Library' off, saved, and back on and saved.

Another thing that we noticed is that the old file imports and exports the data without opening Access in a window. The new file opens an access window and requires the user to log using the name and password specified in 'SYSTEM.MDW'???

Has anyone ever experienced any problem like this before or know where I can look for answers?
Any help is appreciated, thanks.
Reply With Quote

[Simon Murphy]

Have a look at the reference checker in the free products section of www.codematic.net.
run this on the different machines to see if the version of DAO is slightly different (different build number - should give a different guid) if so the tool will point it out.
Also try looking at the properties of the actual dll that DAO 3.51 is pointing at on the different machines.
Are they all patched to the same level?
Same (exact) version of Internet Explorer?
rest assured this sort of thing is a common problem when client PCs are not totally identical.
Post back if none of this helped

cheers
Simon