Writing data to MS Access

[Sara_J]

I am trying to work with Access using LabVIEW 6.1. I don't have the SQL toolkit, so I'm trying to use ActiveX. I have found some example code showing how to read data from Access using ActiveX but can't find anything about how to WRITE data. (Whenever I run a search for "Access" and "ActiveX" on NI.com or in LabVIEW help, it just gives me a bunch of Excel stuff.) Can anyone point me to some sample code, a help file, anything about writing data to Access using ActiveX?

Thanks in advance.

[Dennis_Knutson]

Instead of ActiveX, I would recomend that you try Jeffrey Travis's LabSQL. It is open source and uses ADO to communicate with an ODBC database. You'll have to learn SQL in order to use it but I think that's a lot better than trying to learn all of the ActiveX commands. It is also faster. Plus, if you use ActiveX, you'll have to modify your code to work with different versions of Access. the web site is jeffreydavis.com.

[DougD]

I don't think you will get too much help on ActiveX linking to Access
from National Instruments because that would be in direct competition
to the SQL Toolkit.

I tried to do this one time a couple years ago (send data to Access
via Active X from LabVIEW). It is not easy and I was never entirely
successful with it. I could get some information back and forth but
ultimately it was easier just to get the SQL toolkit and send SQL
commands.

The down side to the SQL Toolkit is the licensing costs. NI charges
for run-time licenses for developed SQL applications because SQL
toolkit is licensed from someone else. A couple years ago it was
costing $100/run time license. That is ridiculously high given that
you can buy an entire operating system
for that price.

If your project is a low volume project, i.e. only a few users, then
it may be cheaper to go ahead and buy SQL toolkit. If you are doing a
larger scale project you may want to avoid SQL toolkit and write your
own code or buy a third party solution. You'll have to do the math.

An alternative may be to buy SQL toolkit but create a single network
(TCP/IP socket) based LabVIEW server that in turn is a client to a
relational database via a single license of SQL toolkit. Your
multiple users could send and retrieve information as regular LabVIEW
TCP/IP clients without needing SQL toolkit themselves.

I would be open to learning about this new LabSQL if it is more cost
effective than SQL Toolkit.


Douglas De Clue
LabVIEW developer
ddeclue@bellsouth.net

Dennis Knutson wrote in message news:<50650000000500000093820000-1023576873000@exchange.ni.com>...
> Instead of ActiveX, I would recomend that you try Jeffrey Travis's
> LabSQL. It is open source and uses ADO to communicate with an ODBC
> database. You'll have to learn SQL in order to use it but I think
> that's a lot better than trying to learn all of the ActiveX commands.
> It is also faster. Plus, if you use ActiveX, you'll have to modify
> your code to work with different versions of Access. the web site is
> jeffreydavis.com.

[DougD]

I don't think you will get too much help on ActiveX linking to Access
from National Instruments because that would be in direct competition
to the SQL Toolkit.

I tried to do this one time a couple years ago (send data to Access
via Active X from LabVIEW). It is not easy and I was never entirely
successful with it. I could get some information back and forth but
ultimately it was easier just to get the SQL toolkit and send SQL
commands.

The down side to the SQL Toolkit is the licensing costs. NI charges
for run-time licenses for developed SQL applications because SQL
toolkit is licensed from someone else. A couple years ago it was
costing $100/run time license. That is ridiculously high given that
you can buy an entire operating system
for that price.

If your project is a low volume project, i.e. only a few users, then
it may be cheaper to go ahead and buy SQL toolkit. If you are doing a
larger scale project you may want to avoid SQL toolkit and write your
own code or buy a third party solution. You'll have to do the math.

An alternative may be to buy SQL toolkit but create a single network
(TCP/IP socket) based LabVIEW server that in turn is a client to a
relational database via a single license of SQL toolkit. Your
multiple users could send and retrieve information as regular LabVIEW
TCP/IP clients without needing SQL toolkit themselves.

I would be open to learning about this new LabSQL if it is more cost
effective than SQL Toolkit.


Douglas De Clue
LabVIEW developer
ddeclue@bellsouth.net

Dennis Knutson wrote in message news:<50650000000500000093820000-1023576873000@exchange.ni.com>...
> Instead of ActiveX, I would recomend that you try Jeffrey Travis's
> LabSQL. It is open source and uses ADO to communicate with an ODBC
> database. You'll have to learn SQL in order to use it but I think
> that's a lot better than trying to learn all of the ActiveX commands.
> It is also faster. Plus, if you use ActiveX, you'll have to modify
> your code to work with different versions of Access. the web site is
> jeffreydavis.com.

[Doug Forman]

There is a simple way to "write" data to MS Access without the Database Connectivity Toolkit or using SQL or ActiveX or other commands, if you can write an entire record at one time (and not individual fields of each record). The basis for this is writing an array of data to a text file and using the "Import" or "Link" properties found in Access to get the data. Admittedly, this does not write directly to Access, but if "Link" is used then the Access Table will update in real-time anyway.

Data can be concatenated using "Build Array", separated by Tabs (or other specific separators, but Tabs are good because a Tab-delimited file is also directly readable by Excel). Separate lines with Carriage Returns. (More specifically, put your first field of data to
the 1st input of "Build Array", a Tab to the 2nd, your 2nd field next, another Tab, repeat until after your last field, put a Carriage Return instead of the Tab).

Write this Array to a text file. Each time you write it you'll get a line of data (equivalent to a "record" in Access). Link an Access Table to this file, and the table will update with the text file.

Warning: having the linked table open while LabView is trying to write to the text file will cause an error. However, if you're using Access, you are propably not real interested in the tables themselves but in Queries or Reports based on the data in the table, so there is probably little reason to have the table open anyway.

Again, this technically does not write to Access, but the result should be just as good for many applications.

[KSampat]

This is with regards to Mr. Doug Forman's suggestion.

Do I have to "Import" from File everytime I want to update the MS Access database or can I do it automatically? Please let me know if I can import the new data in my file automatically in the MS Access database.

[zotamus]

Hi Doug,

I set up the program to output a file, and link it to MS Access like you said, and it is working.  The only problem I see is if the user exits the problem before writing a complete record, it will corrupt your data.  If you separate records with a carriage return, and the file doesn't terminate with one, your data will just be all jumbled up and Access will not know where to start and stop reading the record.  I am running my program on a thread, so the user can kill it at any point.  I don't know of a way to get around this other than trying to create an interrupt, and when the interrupt is set, then you can append a carriage return to the record.  Any thoughts?

Zot

[zotamus]

Hi Doug,

I figured out a simple way to get around my problem.  I set up a Struct, and assigned the values to it as the user tests the DUT - then at the end of the program, I output the values to a file.  If the user terminates the program before it reaches the bottom of the code, then the record just won't be written.  I also set the members of the struct to 0 so that if the user only runs a few tests, then it will just write 0's in the non-tested fields.  This way you always write a complete record every time, and won't have any problems with formatting.  I also put a radio button on the interface so that if the user wants to troubleshoot, and not commit the data to the database, he or she can unclick the "Commit to Database" button before testing. 

Cheers,

Zot