Conversion of ODBC databases
This topic explains how to modify a Surpac .ddb file to access a Microsoft Access database through the direct Access interface rather than using ODBC.
Firstly, assume that we have an Access database called woody.accdb in the directory c:\data. Previously we accessed this through a .ddb file called woody.ddb. We must now edit this file to access woody.accdb directly.
Copy the file woody.ddb to woody_odbc.ddb. Now you can access the database woody.accdb using ODBC by connecting to the database woody_odbc.
Load the file woody.ddb into a text editor.
The text below shows the first few lines of a sample .ddb file.
DATABASE woody
TYPE "odbc"
DB_SPECIFIC "~connect_string=dsn=woody~;"
Note the line TYPE "odbc". This indicates that we want to use ODBC as our method of access. Change this line to TYPE "access". This will cause Surpac to use the direct interface method.
So we now have
DATABASE woody
TYPE "access"
DB_SPECIFIC "~connect_string=dsn=woody~;"
The DB_SPECIFIC string supplies options specific to the database driver. Each driver has a different set of options and so you should change these options to use the direct Access driver.
Below is the list of options that you should remove:
~connect_string=<ODBC connect string>~;
oracle_set_role=<valid sql statment>
name=<actual database name>
systables=<yes/no>
tables=<yes/no>
views=<yes/no>
synonyms=<yes/no>
table_mask=<mask for tables>
user_mask=<mask for table owners>
prompt_username=<yes/no>
You should now be left with either a blank option string (as with the sample above) or you should only have options in the following format:
sql_extra=<any valid sql statement>
include_tables=<exclusive list of tables>
exclude_tables=<tables to exclude from the database>
Note that the access driver has three new options not available for ODBC. They are:
directory=<directory that the accdb file resides in>
pwd=<optional password>
name=<datasource filename>
interval_tables=<comma-separated list of tables>
point_tables=<comma-separated list of tables>
You should always specify the directory option to indicate the directory of the .accdb, file.
If the database is password-protected you must specify the pwd option. You can use the pwd option in two ways:
pwd=fred
The password to access the database is fred.
pwd=
The database requires a password which the user will be prompted for on every connection to the database.
The name option is used to specify the name of the .accdb, file (not including the file extension). It is not required in this example because the .ddb file is called woody.ddb and the .accdb file is woody.accdb so, apart from the extension, the two files have the same name.
Conversely, if we had a database called large_corporate_database.accdb that we wished to map with a .ddb file called view1.ddb, we could specify name=large_corporate_database. This also means that we could then create a second .ddb file (say view2.ddb) that was connected to the same database. These two .ddb files could then provide two distinct views of the same database.
interval_tables=<comma-separated list of tables>
point_tables=<comma-separated list of tables>
These two options are useful when constructing a .ddb file for a pre-existing Access database. Firstly, note that Surpac interrogates an Access database to determine what tables and fields are present and so you do not have to supply all table or field information in the .ddb file. Typically you use calculatedx_from, y_from, z_from, x_to, y_to, z_to fields for Surpac interval tables and x_to,y_to, z_tocalculated fields for Surpac point tables rather than have these as stored values in the database. In this situation, Surpac does not know to treat the tables it finds in the database as interval or point tables unless they are specifically denoted in the .ddb file as such. Rather than use the usual method of specifying these fields individually in the .ddb file, you can promote a given table to a interval or point table by specifying it in the "interval_tables=.." or "point_tables=.." clauses. As an example, suppose we had a table called assay in the database with the following fields:
hole_iddepth_to
Now, this table has all the necessary fields for a point table apart from the x_to, y_to, z_to fields but by specifying "point_tables=assay" in the DB_SPECIFIC="" clause we can treat this table as a point table inside Surpac. This compares to alternative equivalent .ddb file text:
FIELD y_to TYPE real( 11, 3) CALCULATED y(hole_id,depth_to) FIELD x_to TYPE real( 11, 3) CALCULATED x(hole_id,depth_to) FIELD z_to TYPE real( 11, 3) CALCULATED z(hole_id,depth_to)
For the sample above the first few lines will now be
DATABASE woody
TYPE "access"
DB_SPECIFIC "directory=c:\data"
These are the ONLY two lines that should be changed. After the file has been saved, Surpac will be able to access the database using the direct Access driver simply by connecting to woody at the 'enter database' prompt.