topic access database connectivity in LabWindows/CVI

access database connectivity

Joncombat — Mon, 13 Jul 2015 13:05:34 GMT

I am trying to connect to an access database (office 365). I downloaded the SQL toolkit (trial version) and would like to connect to a simple database I created on my local C drive. I am having trouble with the connection string. The examples in CVI are very poor and only connect to the sample cvi database. the example that does connect to the microsoft access database crashes. any suggestions?

Rif.: access database connectivity

RobertoBozzolo — Mon, 13 Jul 2015 14:13:47 GMT

Connecting to a database using the SQL toolkit is easy if you can provide a DSN for it.

Consider that the SQL toolkit is a 32-bit tool: you can only use it while creating 32-bit executable even in 64-bit environment. Nevertheless, in such a case you must create the DSN using the 32-bit version of the ODBC administrator, which you can find in C:\Windows\SysWOW64\odbcad32.exe (note that even the 64-bit program is called odbcad32! )

Once created the appropriate DSN that points to your database (let's call it "MyDSN" for clarity), you should be able to connect to it by using dbH = DBConnect ("DSN=MyDSN");

Rif.: access database connectivity

Joncombat — Mon, 13 Jul 2015 14:38:06 GMT

I think a created the dsn correctly. when I try to connect it returns a -10.

Rif.: access database connectivity

RobertoBozzolo — Mon, 13 Jul 2015 15:16:18 GMT

Error -10 is a system error raised from the OS: try calling DBNativeError and see if some error code is returned.

Rif.: access database connectivity

Joncombat — Mon, 13 Jul 2015 17:38:04 GMT

error code 0x800004005 when I try to connect to my db

error code 0x80040E4E when trying to connect to the default microsoft database in CVI sql example browser2

Rif.: access database connectivity

RobertoBozzolo — Tue, 14 Jul 2015 07:45:50 GMT

Have you read this document?

Rif.: access database connectivity

Joncombat — Tue, 14 Jul 2015 10:51:51 GMT

I read the document. I tried adding a new connection. it shows up in the browser2 window. when I select it it still gives me the -10 error message. Is there a different way to make a connection to a database? I have a simple table with 5 entries I would like to use to store and retrieve data from multiple pc's.

Rif.: access database connectivity

RobertoBozzolo — Tue, 14 Jul 2015 11:23:07 GMT

I have no problems in connecting to a MS Access database provided that:

The DSN is configured in the 32-bit version of the ODBC administrator
The DSN correctly points to a file

Is your file on the local machine or on a network shared resource?

Rif.: access database connectivity

Joncombat — Tue, 14 Jul 2015 12:57:51 GMT

I found an issue with my a corrupted driver in my administrator tools. I deleted it and recreated it. Still no luck. My databse is on my local machine. I am using windows 7 and configured for 32 bit ODBC.

Rif.: access database connectivity

Joncombat — Tue, 14 Jul 2015 16:39:10 GMT

I deleted everything and restarted my pc. I followed the procedures again and it is now working. Thanks for you help! Kudos!!!!

Rif.: access database connectivity

RobertoBozzolo — Tue, 14 Jul 2015 22:18:54 GMT

I'm happy for that! I was wondering what could be happening but didn't know where to get my hands on.

PS If you want to reward somebody with kudos you can click on the yellow star near the user name

Rif.: access database connectivity

Joncombat — Wed, 15 Jul 2015 10:15:46 GMT

I blame windows!!! Now that I have the connection running I am sure I will have more posts!

Rif.: access database connectivity

Joncombat — Wed, 15 Jul 2015 14:47:56 GMT

I can create a new record, update an existing record. how do I determine if a record exists? I have an ID (key) of lets say 12345. what cvi sql function would I use?

Rif.: access database connectivity

RobertoBozzolo — Wed, 15 Jul 2015 15:13:24 GMT

I'm not so fond on SQL to know a direct command, but I would try with a DBIMmediateSQL (Select * From MyTable WHERE key = '123456') followed by a DBNumberOfRecords.

Rif.: access database connectivity

Joncombat — Wed, 15 Jul 2015 16:12:16 GMT

ok, last question. I am trying to delete a record with ID = 12345. the example given in CVI is not that clear.

my statement is DELETE FROM TABLEX WHERE ID = 12345

Rif.: access database connectivity

RobertoBozzolo — Thu, 16 Jul 2015 07:32:51 GMT

Unless this is a typo, the correct command should be enclosed in single quotation marks: DELETE FROM TABLEX WHERE ID = '12345'

Besides it, you could look at del_rec example that gets installed together with the sql toolkit, that shows both this method and an alternative one using tollkit commands instead.

Rif.: access database connectivity

Joncombat — Thu, 16 Jul 2015 10:50:16 GMT

I'm up and running. My issue was a typo in the sql statements. I am using variables and a Fmt() function to create the string and introduced an error. To quote the gremlin from bugs bunny, " These blockbuster bombs don't go off unless you hit them "just" right!"

Rif.: access database connectivity

RobertoBozzolo — Thu, 16 Jul 2015 12:01:13 GMT

Oh yeah! SQL is particularly oversensitive to wrong semantics!