Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, SQL, and multiple versions of Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, SQL, and multiple versions of Excel
Does the game require users to have MSAccess installed, or does it use a
runtime version? The only thing I can think that might be causing this is if some users do not have MS Access Database as a recognized ODBC DSN; you could perhaps have them check to see if this is so. Are there any recognized similarities between the users who have this problem? Particular versions of Win, or Excel, or Access? -- - K Dales "Terry Detrie" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, SQL, and multiple versions of Excel
On Thu, 1 Dec 2005 05:17:05 -0800, "K Dales"
wrote: Does the game require users to have MSAccess installed, or does it use a runtime version? The only thing I can think that might be causing this is if some users do not have MS Access Database as a recognized ODBC DSN; you could perhaps have them check to see if this is so. Are there any recognized similarities between the users who have this problem? Particular versions of Win, or Excel, or Access? The game does not require MS Access, although it does require MDAC 2.8. I've gotten positive feedback from users without MSAccess on their machines, so I'm assuming the MDAC is enough. So far I've only received 2 reports of failed Initialization. One was XL2000/Win98, and the other was XL2002/WinXP (both German). FWIW, my computer is XL2002/WinXP. I have personally tested my file on machines running XL2000 and XL2003 and it worked fine. I also tested it on XL97 and it bombed horribly, but gamers tend to gravitate towards new software so I'm not worried about that scenario. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Language Versions of Excel | Excel Discussion (Misc queries) | |||
How to you open multiple versions of Excel | Excel Discussion (Misc queries) | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
VB6 COM Add-In for multiple Excel and Word versions | Excel Programming | |||
VB6 COM Add-In for multiple Excel and Word versions | Excel Programming |