RE: VBA, SQL, and multiple versions of Excel

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
>
.

Relevant Pages

  • Re: which approach? (cofused with tables)
    ... >> The HireDetail table exists in the hope that sometimes customers will ... >> $2.95 for the next game the customer wants and gives change, ... >> For an example of that concept, open the Northwind sample database. ...
    (comp.databases.ms-access)
  • Re: File Formats - Relational Databases
    ... savefiles and perhaps even the data for a roguelike game? ... For game data like the edit/*.txt files in Angband, ... There are many different small relational database drivers that are ... you can store all the monsters in your ...
    (rec.games.roguelike.development)
  • Re: www.CeBeans.com - new program listings - Oct 22 2007
    ... This program is a massive database of 520 Casserole recipes. ... Miles or Nautical Miles. ... The object of this game is to hop on the poles and use your paint gun to ... knock down the birds. ...
    (microsoft.public.pocketpc)
  • Re: File Formats - Relational Databases
    ... I investigated using SQL for the data for POWDER at one point. ... option, even if you don't use in game, is to generate your game ... The killer for me was that I'm not familiar with any database editing ... you can store all the monsters in your ...
    (rec.games.roguelike.development)
  • Re: Dangers of pushing copyright law.
    ... > So you are finally beginning getting aware of the substantial effort it ... > takes to make a high quality collection (database) of games. ... They can't monopolize uncopyrightable game records. ...
    (rec.games.go)