|Subject:||Re: Opening MS Access files in Paradox 11
|Date:||Sat, 19 Oct 2019 00:08:54 -0600
|From:||Liz McGuire <email@example.com>
On 27 Sep 2019 04:13, Andy wrote:
> Hi Liz
> Can you take me through this step by step? (I presume it's the
> the 32-bit, version I need to use.)
Total fluke that I saw this - don't generally read the groups anymore.
This is a copy & paste from the article that used to be on the Paradox
Community website. It should still be accurate.
No, you do not want 64-bit ODBC, you want the one in the SysWOW
directory (e.g. on my Win7 box, it's in C:\Windows\SysWOW64\odbcad32.exe).
Here's a paste from the HTML file:
Using ODBC Data Sources
© 2001-2002 Liz Woodhouse
Note for those trying to use ODBC for databases other than MS Access:
All of the steps below apply, except that you may not need DAO/MDAC -
though, depending on your database, these packages may have relevant
drivers. Everywhere MS Access or it's driver is mentioned, simply
substitute your database program and its ODBC driver. Drivers may be
installed by your software (sometimes custom install is needed), or you
can check DAO/MDAC, or you can check DataDirect Technologies (formerly
Merant, maker of INTERSOLV ODBC drivers) to see if they make a driver
for your database software. There may be more than one driver for your
database software - if so, you may wish to try each of these as
different versions/capabilities may be supported by different drivers.
Please note that some of the ODBC DSN setup steps may vary slightly from
driver to driver - simply use your best judgment when setting up your
Note about the native BDE drivers: This tip describes setting up and
using ODBC because I found certain things weren't possible using the
native BDE driver for MS Access. If a native BDE driver will do
everything you need, I recommend you use that instead of ODBC.
Pre-Configuring The BDE
The BDE can be configured to show ODBC data sources by default. This can
be done in one of two places (which options are available may depend on
the BDE version you're using).
On the Configuration tab of the BDE, under Configuration > System >
Init is an AUTO ODBC setting. When set to TRUE, the BDE will import ODBC
data sources each time the BDE is initialized. This is apparently an
older setting and should only be used if the "Virtual configuration"
setting is not available.
Selecting Options... from the Object menu will bring up the Options
dialog. Here, under "Select configuration modes to view" you can check
the Virtual box, and this will cause all ODBC data sources to be shown
in the list of aliases on the Databases tab. Data sources shown this way
cannot be deleted or renamed and should be edited in the ODBC
Administrator. If you alter the settings of one of these ODBC data
sources within the BDE, it is changed from a "virtual" data source to a
"persistent" data source and the changes are then stored in the BDE
Please see the BDE Administrator help file for full details on these and
other BDE options.
Getting The Necessary Drivers
In order to access a database through the ODBC, you'll need an ODBC
driver for that database. If you don't already have MS Access ODBC
drivers, or if you need newer drivers, please try one of the following
(I strongly recommend using MDAC rather than the DAO download):
Obtain MDAC 2.5 SP2 from Microsoft's web site.
Obtain DAO.EXE from Corel's FTP site.
If either of the above links becomes invalid, please email me and in the
meantime, search Microsoft's web site for "data access". Note that newer
versions of MDAC reportedly do not contain ODBC drivers for MS Access.
Please note that if you are installing MDAC, at the time this article
was last updated, MDAC 2.5 SP2 was the last (most recent) version of
MDAC to include MS Access drivers. If you are installing this version of
MDAC on Win98SE or a newer version of Windows, it's possible this
install will downgrade your ODBC version. For most folks, this won't
matter, however, some drivers will only run under newer versions of
ODBC. If this is an issue for you, you should also download the most
recent version of MDAC (2.7 as of this writing), install 2.5 SP2 and
then install the most recent. Installing the most recent will upgrade
your ODBC version without removing the MS Access drivers. (Please note
that I have not done extensive testing, and installing MDAC is done at
your own risk. Please be sure you have the software needed to reinstall
all your ODBC drivers and that you have your DSNs documented somewhere
so that should it be needed, you can reinstall drivers and recreate DSNs
(I didn't need to do this, but be warned).)
Update as of 25 Jun 2003: There is now an MDAC 2.5 SP3. I cannot be sure
whether this includes MS Access drivers. As it is associated with Win2K
SP3 and some may have issues with the EULA for that SP, I'll continue to
link to MDAC 2.5 SP2 until it's no longer available, after which, I'll
make the file available directly from the Paradox Community. Please see
MS's Data Access page for notes about issues with MDAC (esp. with MS SQL
Setting Up The ODBC DSN
Be sure to close Paradox before proceeding (or at least restart Paradox
after the ODBC and BDE configurations are complete).
In the Windows Control Panel, start the ODBC applet (applet name varies
depending on version, but always includes the letters ODBC).
In ODBC, make a new DSN (the User, System and File tabs have a little
blurbs about when to use that type of DSN). Then follow these steps:
From the driver list, pick: Microsoft Access Driver (*.mdb).
Put a descriptive name in the Data Source Name field.
In the Database section, click the Select... button, and select
your .MDB file.
Click on OK to close the ODBC Microsoft Access Setup dialog (other
settings can be left default - unless the file requires a user name and
password, in which case, click on the Advanced button and fill in those
fields... I've never tested this part).
Close ODBC and open the BDE Administrator.
Configuring The BDE
The DSN you created in step above will appear in the list of BDE
aliases. In some cases, this BDE alias may be available immediately for
use in Paradox, in other cases, you may be able to simply edit some of
the fields mentioned below (even if you edit them to the same value -
sort of kick-starting the alias), and in other cases, you will have to
follow the instructions below to create a separate alias.
On the Databases tab, right click in white space and select New
from the popup menu.
Select Microsoft Access Driver (*.mdb) in the resulting dialog (New
Database Alias) and click OK.
Give the new alias a name, or keep the default (ODBC1).
On the right, edit the fields as follows:
enter the full path to your .MDB file in the DATABASE NAME field
in the ODBC DSN combo field, select the Data Source Name
entered in step 2 of "Setting Up The ODBC DSN" (you may need to save
changes to the new alias before this list will display names to choose)
if required/desired, fill in the USER NAME field (haven't tried
you may also wish to fill in the LANGDRIVER and/or SQLQRYMODE
couldn't begin to tell you want to do with the SCHEMA CACHE DIR
field or the other fields filled in by default - I just left those as is.
Save all changes and close the BDE.
Using The ODBC Data Source
Open Paradox (if Paradox was open during the above steps, you must
restart Paradox after making the above changes), and you should be able
to use this alias. Note, the alias won't show up in the Project Viewer,
but it will show up in most of the dialogs that let you select an alias
and then select tables from that alias (e.g. Open Table, Select File,
Data Model, etc.).
To open an MS Access table, use the Open Table button on the toolbar or
choose File > Open > Table... from the menu, select the alias created in
the step above, then select the table. Note that certain types of MS
Access queries will also show up in the Open Table dialog. It's possible
that not all displayed files can be opened by Paradox! Note also that
the Open Table dialog changes slightly after opening the alias, and you
can choose types of tables to list from the 'List tables:' drop-down
field. Finally, note that when you select the alias in the Open Table
dialog, you'll get a login screen asking for user name and password - if
these aren't required by the .MDB the alias points to, just leave the
fields blank and press OK.
You can query MS Access tables in much the same way - simply create a
new query or add a table to an existing query and in the Select File
dialog, select the alias which points to your .MDB file, then choose the
table of interest.
MS Access tables can be used for reports and forms and can be written to
directly in Paradox.
MS Access aliases (and other aliases not identified in the BDE as
"STANDARD") are not shown in the Project Viewer.
The max fieldname length in Paradox is 25 characters. Paradox accepts
Access's fieldname lengths up to 31 characters and will automatically
delete the characters above 25. If this deletion results in identical
fieldnames, Paradox will put _1 at the end of the second field name. If
the Access database has fieldnames larger than 31 characters you won't
be able to open or query the Access table in Paradox.
To create a new table in a non-standard alias, you need to first access
this alias somehow (e.g. open a table) to open a connection to the database.
[Editor's note: Check out Accessing an ODBC Data Source from Paradox in
the Paradox Programming Tips & Tricks section for details on how to do
this in code.]