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

Here is my problem:

At my office we multiple computers running different versions o
Windows (98, 2000, XP). I have an Excel 97 spreadsheet that uses VBA t
create a form populated with data from an Access 97 Database and fil
out Excel Templates from the same database. The Excel file is locate
on the network where everyone can run it. After someones computer died
I reinstalled Windows 2000. Now this same Excel file gives me an erro
when trying to run one of the macros. Upon investigation I found tha
this file works on some computers and not others. It worked o
computers running Windows 98, 2000 and xp and yet did not work on othe
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.Val ue
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 update
everything and reinstalled office. I started looking at some of th
settings in Excel and the Visual Basic Editor. I had read something o
another forum about the 'Refereces' in the Visual Basic Editor. On
that came to my attention was 'Microsft DAO 3.51 Object Library', bu
it was already selected. I turned this setting off, and saved the fil
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 o
and saved the file.
When I tried it again, it worked...on everybody's computer! I wa
please because I thought I had solved the problem, but after a fe
days, everyone was complaining that though it did work on everyone
computer now, it was horribly slow when importing and exporting data t
the Access Database, before it was a few seconds, now it was severa
minutes. I had saved the old file, so we tried both files on differen
computers. On computers that the file worked before, the old file stil
worked and was fast, and the new file worked, but was slow. On computer
that the file did not work before, the old file did not work, and th
new file worked, but was slow. I am very confused as to why it works o
some, but not others.

What confuses me even more is that all I did to 'fix' the problem wa
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 export
the data without opening Access in a window. The new file opens a
access window and requires the user to log using the name and passwor
specified in 'SYSTEM.MDW'???

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

--
levileonard
-----------------------------------------------------------------------
levileonards's Profile: http://www.excelforum.com/member.php...fo&userid=2702
View this thread: http://www.excelforum.com/showthread.php?threadid=46681