How do I send datamember points to an ".mdb" MS Access file?

[HJH72]

I have several datamember points from several processes that I would like to put into an MS Access mdb file.  The data is organized as follows:  A set of points from one process will go to a particular table.  Each process' datamember point (in a particular table) will be a field (or column).   These points will logged every 24 hours (once a day).  I believe I have to use the SQLExec object, but I don't anything too much about it.  I look forward to your response.

Thank you.

[Mike@DTSI]

the SQLExec object will work great for this.

Get the values you want into expressions (or any objects that can be used to represent the data you want to log).

Set the  Data source to either a system dsn ("DSN=MyDatabase") or a provider ("DSN=MS Access Database; DBQ=C:\Folder\MyDatabase.mdb;").

For the SQL= expression enter "INSERT INTO MyTable(MyColumn1, MyColumn2) VALUES ('MyString1', MyNumeric1)"

Customize to your database name, columns, etc.

Good luck

Mike

[HJH72]

Mike,

Thank you for your response!  However I do have a few questions.  Do I initially create the mdb file via ODBC, System DSN and use "DSN=MyDatabase.mdb"? 

The other option you provided was: "DSN=MS Access Database; DBQ=C:\Folder\MyDatabase.mdb;"  Does this initially create the mdb file?

I'll be looking forwared to your response.

Hank

[Mike@DTSI]

You must create the MDB in Access or similar program.

DSN is an ODBC pre-configured database.  The other way i showed is  calling all the required parameters, all the stuff thats hidden using DSN.

If you dont have access, look into the Spreadsheet object.

Mike

Message Edited by Mike@DTSI on 03-20-2009 03:39 PM

[HJH72]

I do not have MS Access on the machine.  However I'm using other software that reads mdb files to do some useful data analysis.  In the past I've used the ODBC System DSN to create the mdb file and using other HMI software sends the data into the mdb file.  In Lookout's case, will I need to install Access even though I can create the mdb database via ODBC System DSN?

[HJH72]

Mike,

Does the SQL expression create the table names and column names?  Or do I need to Access to intially define the names?

[Mike@DTSI]

Yes you can use the ODBC Administrator to make the initial access database.  To creat the clumns you must send ALTER commands in sql to the database.

This is all MUCH easier in access.

Mike

[HJH72]

What would the proper expression be when incorporating the ALTER commands for creating columns?

[Mike@DTSI]

there are many variations depending on the type of data.  Suggest google to search for the commands 🙂

Get you started: http://www.blueclaw-db.com/alter_table_ddl.htm

Mike

Message Edited by Mike@DTSI on 03-20-2009 05:19 PM

[HJH72]

Thank you Mike.  I'll take the easy route by utilizing Access to create the tables and column names.

Hank