Error updating database entry with text longer than 255 characters

[thisisnotadream]

Rocking LV2012 with an MS Access 2000 database that was created and setup in another environment.  Trying to access it and manipulate the data using a LV GUI because the one they built for us sucks.

Some of the columns are of "Memo" type and LV reports the column information for these columns with a defined size of 1073741823.  The problem is that if I try to update the data in these columns with the DB Tools Update Data.vi and the string is > 255 or so characters long I get:

ERROR -2147217887

Possible reason(s):
ADO Error: 0x80040E21
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver]Invalid precision value  in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Update Data.vi->Update DB.vi->GTS.vi

I realize that 255 is the limit for some applications.  Is there some setup that I need to do to have the limit set by the data source and not by whatever provider is limiting the data length?

[mikeporter]

Don't use the ODBC drivers. What does your connection string look like?

Mike...

[thisisnotadream]

Never used 'em.  I have a data source set up that points to the database (.mdb) file.  Is there a resource I can use to craft a string for connection?  All I've found is stuff for .udl files, which I have had trouble with due to the 32/64-bit install on my machine.

[mikeporter]

You are using connection strings! Change the file extension of your UDL to TXT and open it.

http://www.connectionstrings.com

Mike...

[thisisnotadream]

Sure, yeah.  But I'm not using .udl files that I know of.  I have set up a User DSN in 32-bit ODBC Data Source Administrator.  Does that generate a connection string that I can view somewhere?

[thisisnotadream]

The DSN file that I am using contains the following:

---

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:\Users\marlowd\Desktop
DBQ=C:\Users\marlowd\Desktop\CommonChassis.mdb

[thisisnotadream]

Have also tried setting up UDL unsuccessfully using KnowledgeBase 2KFB4NOU.  The resultant connection string is:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\marlowd\Desktop\CommonChassis.mdb;Persist Security Info=False

Since I have a 32-bit install of LV on a 64-bit Windows OS, I had to do the fancy backdoor into the UDL setup.  I end up with an ADO error 0x80004005 for all connection attempts that aren't a DSN connection....but that DSN connection limits my strings apparently.

[mikeporter · Accepted solution]

That should work. It looks like you are using the database connectivity toolkit. That could also be a problem. Check here for alternative drivers.

http://www.notatamelion.com/2015/01/05/managing-data-the-easy-way/

Mike...

[ijustlovemath]

Mike,

If I recall correctly, the Datbase Connectivity toolkit is built on the exact same methods as the one on your blog, just with a little more data validation and a some non-viewable core VIs.

[mikeporter]

Yes, but the "data validation" and non-viewable VIs are where the problems arise.

Mike...