Here's the situation:

I play a shareware game that stores its data in an Access database.
I've created an Excel file that queries the database and pulls useful
information for analysis. Many users in the this game's community
have expressed an interest in the tool and I've shared it freely.

I wrote an initialization routine so the end user can point Excel to
the database path and (internally) determine which of two passwords
the database has (after a major upgrade, the shareware game changed
password, but the legacy password stayed for many who had the game
before and after this transition). The password itself is simply to
prevent curious gamers from accidentally messing up a table (very easy
to do). The code below attempts to retrieve a small table from the
database using passwords stored in A11:A12. If it fails, then the
password must be wrong and it moves on to the second. When Excel
succeeds the table is stored in column G and saves the successful
password.

The problem I'm running into is that while many people have run the
Initialization macro without incident, some users report a 1004 Error
failure to find database. Are there any differences between XL2000,
XL2002, and XL2003 that could be making this even more complicated?

I'm just learning how to do queries, so if there are more elegant
methods of performing this task I'd love to learn how.

Thanks in advance
Terry


Sub Initialize()
Dim Msg As String
Application.Calculation = xlCalculationManual
Msg = "Begin Macro"

'Find Database
Application.DefaultFilePath = ThisWorkbook.Path
DbFile = Application.GetOpenFilename _
(filefilter:="Access Files (*.mdb; *.db),*.mdb;*.db,All Files
(*.*),*.*", _
Title:="Find Magic Card Database")
Range("DbFile").Value = DbFile
Msg = "Found Database"

'Try passwords
On Error Resume Next
Application.DisplayAlerts = False

Set WSD = Worksheets("Setup")
WSD.Columns(7).Delete
For iCtr = 1 To 2
PassWd = WSD.Cells(iCtr + 10, 1).Value
With WSD.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & DbFile &
";DefaultDir=" & DbFile & ";DriverId=281;FIL=MS
Access;MaxBufferSize=20" _
), Array("48;PageTimeout=5;PWD=" & PassWd &
";UID=admin;")), Destination:=Range("Setup!G1" _
))
.CommandText = Array( _
"SELECT tlkpTournamentRules.RulesName" & Chr(13) & ""
& Chr(10) & "FROM `" & DbFile & "`.tlkpTournamentRules
tlkpTournamentRules" _
)
.Name = "Query from MS Access Database"
.Refresh BackgroundQuery:=False
If WSD.Cells(1, 7).Value < Empty Then
Err.Clear
Exit For
Msg = "Password #" & iCtr & " works!"
End If
End With
Msg = "Password Loop #" & iCtr & " (Failed)"
Next iCtr


If Err < 0 Then
Msg = Msg & Chr(13) & "Excel generated Error #" & Err.Number &
" while running code." _
& Chr(13) & "Please report error to programmer"
MsgBox (Msg)
Else
' Password successful. Open up the rest of the file
Range("Passwd").Value = PassWd
Call ShowAll
End If

Application.Calculation = xlCalculationAutomatic
End Sub