Access ODBC connection

Part of the funtionality in the app I'm building is connecting to a
remote Access database and retrieving monthly billing data.  In
configuring the ODBC connection, I had to use the 'User DSN' tab rather
than the 'System DSN' tab.  This limits who can login to my app and
successfully get the data from Access.  Only the user the ODBC
connection is configured for is able to do it.  PowerBuilder just didn't
recognize the connection if it wasn't a User DSN.  Is this a limitation
I just have to live with?  Or is there a workaround?  Thanks for any
help in advance,

Bruce Kimura
[email protected]

This may be more than you want to see, but we successfully connect to Access
databases using the ODBC System DSN tab.  My understanding of this evolved
from first using just the User DSN definition, to using both, to finally
just using the System DSN tab.  As I recall, there was basically no
difference whatsoever from the PowerBuilder end of things.

Following is the InstallShield code for creating the registry entries for
our database.  The first three are for our application, the remainder is the
equivalent of what the ODBC Administrator puts in place.  Entries under
HKEY_LOCAL_MACHINE are in the System DSN Tab.  User DSN puts them under
HKEY_CURRENT_USER.  After that, I have the PowerBuilder code where we
actually connect.  (Note: we swap this app back and forth between Access and
MS SQL Server - I hope that I haven' t left anything in here from the SQL
Server connection that may confuse.)  I hope this helps!

Cliff

InstallShield code:
    RegDBSetDefaultRoot(HKEY_LOCAL_MACHINE);

    RegDBSetKeyValueEx("SOFTWARE\\PSC\\WEGSARS Ver 2\\Water_Database",
"Database", REGDB_STRING, "Water_ARS", -1);
    RegDBSetKeyValueEx("SOFTWARE\\PSC\\WEGSARS Ver 2\\Water_Database",
"DBMS", REGDB_STRING, "ODBC", -1);
    RegDBSetKeyValueEx("SOFTWARE\\PSC\\WEGSARS Ver 2\\Water_Database",
"DBParm", REGDB_STRING, "ConnectString='DSN=Water_ARS;'", -1);

    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS", "DBQ",
REGDB_STRING, svDATADir^"WaterARS.mdb", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS", "Driver",
REGDB_STRING, WINSYSDIR^"odbcjt32.dll", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS", "DriverID",
REGDB_NUMBER, "25", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS", "FIL",
REGDB_STRING, "MS Access;", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS", "UID",
REGDB_STRING, "admin", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS",
"SafeTransactions", REGDB_NUMBER, "0", -1);
    RegDBCreateKeyEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines", "");
    RegDBCreateKeyEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines\\Jet",
"");
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines\\Jet",
"ImplicitCommitSync", REGDB_STRING, "Yes", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines\\Jet",
"MaxBufferSize", REGDB_NUMBER, "512", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines\\Jet",
"PageTimeout", REGDB_NUMBER, "5", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines\\Jet",
"Threads", REGDB_NUMBER, "3", -1);
    RegDBSetKeyValueEx("SOFTWARE\\ODBC\\odbc.ini\\Water_ARS\\Engines\\Jet",
"UserCommitSync", REGDB_STRING, "Yes", -1);

******
PowerBuilder code:

string ls_regsection

IF IsValid ( gnv_app ) THEN
 ls_regsection = gnv_app.of_getregkeysection () + "Water_Database"
END IF

 SQLCA = CREATE transaction
 SQLCA.AutoCommit = FALSE

RegistryGet(ls_regsection,"DBMS", SQLCA.DBMS)

IF SQLCA.DBMS = 'ODBC' THEN
 RegistryGet(ls_regsection,"DBParm",SQLCA.DbParm)
 SQLCA.ServerName = "ACCESS"
ELSE
 RegistryGet(ls_regsection,"Database",SQLCA.Database)
 RegistryGet(ls_regsection,"ServerName",SQLCA.ServerName)
 SQLCA.LogID = gs_user
END IF

CONNECT USING SQLCA ;