How to use TCLODBC in Surpac to write to a Microsoft Access database

 

SURPAC TIPS | With TCL, as it is so easy to output data to simple text files, like a CSV, other output modes are often overlooked. In this post, we look at another simple way we can get data out of Surpac by using TCLODBC to write some data to a Microsoft Access database.

 

Preparation

A few things that need to be done before we begin are:

1. Find or create an Access database file. You will need to know the full directory path, as well as the filename.

2. In Access, create a new table, or choose to use an existing table in the database.

3. Create the fields and setup the Data Types you will be inserting data into.

 

For the simple purposes of this example I have an access database saved as: C:/00_projects/GEOVIA_blog/database1.accdb. The database has a table called Table1 with six fields (A, B, C, D, E, and F) all prepared to accept some integer values.

 

 

 

Now that all of the preparation has been completed, let’s take a look at the TCL.


STEP 1.
The first thing we will need to do is specify that we will be using the TCLODBC package.

 

 

STEP 2. There are some options for how we want to input the database and table for the macro. They could be specified directly as part of the command, but my preference is to pass these values as a variable. This could be done with a custom GUIDO form but, to keep this example simple we will simply use the set command to prepare all variables.

 

STEP 3. We will need to have some information to pass into the database. This could come from any number of sources. Here we will take some simple lists of data into the database.

Here the plan is to create a row in the database where order of the the values in the two lists map the fields to their values. The macro will create a row in the with values in the following table. I have purposefully jumbled the order of the fields in the lists to show that the order of the fields is not important. What is important is matching the sequence of values with the sequence of field names.

 

 

 

 

STEP 4. To make the database connection, use the command database connect to create a variable for the database handle, here I am using db, and specify the database connection string by passing the name of the Microsoft Access database file into the connection string.

 

 


STEP 5.
The data in these lists will need to be transformed into a string of text that will be used to insert their values into the database. The strings of text will list the fields and their values in a comma delimited series of values.

A ,B ,C ,F ,E ,D

6 ,23 ,143 ,16 ,55 ,4

 

An easy way to transform these list into the text strings is to use the lindex and append commands and a for loop. Here we will store the values from the lists in two variables, fieldStr and valueStr.

 

 


STEP 6.
Using the database handle (db) we can give instructions to the database. Here we are passing the variables for the table and the text strings for the field names and values.

 

Repeating steps 5 and 6 with a for or while loop make it easy and possible to insert as many new records into the database table as needed.

 

 

STEP 7. Last thing is to disconnect from the database

 

Putting all of these components together, the finished and completed tcl file will look as follows:

 

 

 

Trevor Lukaniuk

Trevor Lukaniuk

Trevor is a Senior Mining Engineer at Dassault Systèmes, based in Vancouver Canada.