Paradox Community

Items in pnews.paradox-3rdparty

Subject:Re: Opening MS Access files in Paradox 11
Date:Sat, 19 Oct 2019 00:08:54 -0600
From:Liz McGuire <liz@paradoxcommunity.com>
Newsgroups:pnews.paradox-3rdparty
On 27 Sep 2019 04:13, Andy wrote:
 > Hi Liz
 >
 > Can you take me through this step by step?  (I presume it's the 
64-bit, not
 > the 32-bit, version I need to use.)
 >
 > Cheers,
 > Andy
 >


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

Notes

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

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

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


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 
this)
         you may also wish to fill in the LANGDRIVER and/or SQLQRYMODE 
fields
         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.


Notes

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



Copyright © 2004 thedbcommunity.com