Accessing an ODBC Data Source from Paradox

Contributed by Allen Mulvey

26 February 2002

Allen gives us the steps needed to set up and access an ODBC data source, including sample code and a .REG file for setting up the DSN without having to open the ODBC utility.Accessing an ODBC Data Source from Paradox
© 2002 Allen Mulvey
NYS University Police, Oswego, NY

Accessing an MS Access, or any other ODBC data source, from Paradox, is a two step process.

You need to make a SystemDSN pointing to the database. You can create this manually in the Datasources (ODBC) Manager or it can be created directly in the registry by an OPAL script. There are two ways to do this by script. The first is to execute Regedit/s to import an external registry file. I prefer this method because the reg file is not compiled into the application and can easily be edited. The second method is to use OPAL to write the registry keys directly. If you choose this method, be sure to cast the dword values as longint.

You need to make a BDE alias to the SystemDSN. Again, this can be done manually in the BDE Administrator or programatically in OPAL.

When the above alias is opened, it looks like a folder containing data tables. The tables may then be opened and edited just like Paradox tables.

Below is an example script and reg file which may be used as templates. (Be sure to edit the file name and path in the reg file.) If you need to add additional alias parameters, look in the BDE Administrator for those available, do not look in the Paradox Alias Manager. The latter sometimes truncates parameter names. (ie. "DATABASE NAME" appears as "DATABASE") If you need more information about the registry keys, just make a SystemDSN manually, then, when it works, export the two registry branches.

Start of Script

; This script will create a SystemDSN and a BDE alias pointing to an MS Access mdb file

var
  aliasInfo  dynarray[] string
  str        string
endvar

; Check for YourDSN in registry
str = getRegistryValue( "SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources",
                        "YourDSN",
                        regKeyLocalMachine)

; If the DSN doesn't exist, create it.
if str = "" then
  str = "regedit /s "+getAliasPath("Work")+"\\YourDSN.reg"
  execute(str,Yes,ExeHidden)
endif

; Just in case the alias already exists...
try
  removeAlias("YourAlias")
onfail
endtry

; Set up the alias
AliasInfo["ODBC DSN"] = "YourDSN"
AliasInfo["USER NAME"] = "Admin"
AliasInfo["OPEN MODE"] = "READ/WRITE"

; Make the alias
addAlias("YourAlias","Microsoft Access Driver (*.mdb)",AliasInfo)

; To eliminate the Password prompt use this command.
; Admin is the default User Name for MS Access databases.
; You can also read the User Name from the registry.
; If the password is blank, the database will open without prompting.
setAliasPassword("YourAlias", "Admin")

End of Script

Start of ODBC reg file

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"YourDSN"="Microsoft Access Driver (*.mdb)"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YourDSN]
"Driver"="C:\\WINNT\\System32\\odbcjt32.dll"
"DBQ"="<Drive>:\\<path>\\Your.mdb"
"Description"="Your Access Database"
"DriverId"=dword:00000019
"FIL"="MS Access;"
"SafeTransactions"=dword:00000000
"UID"="Admin"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YourDSN\Engines]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\YourDSN\Engines\Jet]
"ImplicitCommitSync"=""
"MaxBufferSize"=dword:00000800
"PageTimeout"=dword:00000005
"Threads"=dword:00000003
"UserCommitSync"="Yes"

End of ODBC reg file

[Editor's note: Check out Using ODBC Data Sources in the Interactive Paradox Tips & Tricks section for explicit details on how to set up an ODBC DSN and corresponding BDE Alias manually.]

< Prev		Next >

[ Back ]