Access memo field length

[Steve_Brooks]

I am writing data to an Access database using the database connectivity toolkit. I have a large text field that I need to write so I made the field a memo field instead of text. Text fields can only have 255 characters and memo fields are supposed to support 65,563 characters.

The problem is when I write more than 255 characters to the memo field I get an error. Has anyone been able to write more than 255 characters to a database field?

Thanks,

Steve

[FLX]

Hi Steve,

what error do you get? Does this error come from the Toolkit VIs or maybe from the interface driver of the access database?

alex

[Steve_Brooks]

Hi Steve,

what error do you get? Does this error come from the Toolkit VIs or maybe from the interface driver of the access database?

alex

3563[/snapback]

Here is the exact error:

Error -2147217887

Cmd Execute.vi->DB Tools Insert Data.vi->Section Sub.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC Microsoft Access Driver]Invalid precision value . in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->Section Sub.vi

It looks like the error is coming from the toolkit VIs.

Steve

[warnermf]

Did you ever get a fix to this problem? I am having the exact same problem now...

I appreciate any help!

Thanks,

Matt

QUOTE(Steve_Brooks @ Jan 18 2005, 10:33 AM)

Here is the exact error:

Error -2147217887

Cmd Execute.vi->DB Tools Insert Data.vi->Section Sub.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC Microsoft Access Driver]Invalid precision value . in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->Section Sub.vi

It looks like the error is coming from the toolkit VIs.

Steve

[xtal]

Actually, the error is occurring in the Microsoft OLE DB provider for ODBC. What I do in these situations is to change drivers to use the Jet driver. You'll have to use a UDL file to connect to the database rather than ODBC. This is how most of the shipping examples for the toolkit connect.

[i2dx]

QUOTE(Steve_Brooks @ Jan 18 2005, 05:33 PM)

Here is the exact error:

Error -2147217887

Cmd Execute.vi->DB Tools Insert Data.vi->Section Sub.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC Microsoft Access Driver]Invalid precision value . in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->Section Sub.vi

It looks like the error is coming from the toolkit VIs.

Steve

No. This is an ODBC-Error message and means: You tried to insert a number in the wrong format, e.g. your database definition says that field contains a floating point number and you tried to insert an integer. I'd check the database definition, whether that column is really defined as memo field.

Do not forget to escape the string, you want to insert into a memo field: all tics >>'<< in your string have to be replaced by double-tics, Access also can not handle the 0x00 char under certain circumstances (which I don't know exacly, otherwise I had allready implemented that in my ADO-Toolkit)

To answer the intial question: yes, I have inserted large strings into a memo field, I'm still doing that and I'm doing that again and again

QUOTE(xtaldaz @ Jun 14 2007, 10:38 PM)

What I do in these situations is to change drivers to use the Jet driver. You'll have to use a UDL file to connect to the database rather than ODBC. This is how most of the shipping examples for the toolkit connect.

If he uses the NI Database connectivity toolkit, me makes automatically usage of the Jet database engine. The Software "Microsoft Access" is only a frontent, where you can create/edit/etc your database. Each Windoze since Windows 2000 comes with the Jet Database Engine installed (it's not a driver, it's the database engine!) => you can automatically use *.mdb-files on each windows machine with an OS higher then Win2k. Besides: you can install the MDAC (Microsoft Data Access Components) on machines with an older OS to make usage of the Jet DBE

[Yair]

I have a feeling I remember something about not being able to do that with the toolkit, but if Crystal hasn't mentioned it, I guess it is not a familiar bug.

In any case, I believe you should be able to do it by formulating an SQL Insert query yourself, like this:

http://forums.lavag.org/index.php?act=attach&type=post&id=6120

[i2dx]

QUOTE(yen @ Jun 17 2007, 07:44 PM)

In any case, I believe you should be able to do it by formulating an SQL Insert query yourself, like this:

http://forums.lavag.org/index.php?act=attach&type=post&id=6120''>http://forums.lavag.org/index.php?act=attach&type=post&id=6120'>http://forums.lavag.org/index.php?act=attach&type=post&id=6120

If the String in "Memo Field" does not contain any Tics >>'<< that should work. Otherwise you have to replace all Tics with double Tics ...

[xtal]

QUOTE(i2dx @ Jun 15 2007, 01:50 AM)

If he uses the NI Database connectivity toolkit, me makes automatically usage of the Jet database engine. The Software "Microsoft Access" is only a frontent, where you can create/edit/etc your database. Each Windoze since Windows 2000 comes with the Jet Database Engine installed (it's not a driver, it's the database engine!) => you can automatically use *.mdb-files on each windows machine with an OS higher then Win2k. Besides: you can install the MDAC (Microsoft Data Access Components) on machines with an older OS to make usage of the Jet DBE

Actually, there *is* a driver for the Jet engine as shown here:

http://forums.lavag.org/index.php?act=attach&type=post&id=6131''>http://forums.lavag.org/index.php?act=attach&type=post&id=6131'>http://forums.lavag.org/index.php?act=attach&type=post&id=6131

The error he was receiving was for the "Microsoft OLE DB Provider for ODBC Drivers" and I was telling him to not use ODBC and instead use the highlighted driver "Microsoft Jet 4.0 OLE DB Provider". ODBC is a technology that is being phased out and the OLE DB Providers are usually better written and support more data types.

Yen, you are correct. I've used the Memo field types a number of times with the database toolkit and not had a problem. I really think it's a driver issue.

[warnermf]

Thanks for all the great feedback everyone! I'm pleased that it sounds like there is a reasonable solution. I have a few questions though.

First, just to clarify - I am inputing a large text field into an access database using labview 8.0.

I verified that the field in the database is indeed a memo field. However, anytime I insert text that is larger than 255 chars, i get the "Invalid precision error in rec create" error. it works fine if I make the text smaller than 255.

This happens even if I have any >'< 'ticks' in the string or not.

I am using a .dsn file to connect to my database. I don't see any options to change the provider I am using similar to the "Data Link Properties" window you show below. Where do I do this? I tried creating a new .udl file and setting the "Data Link Properties" to the "Jet" Provider but anytime I test the connection to my database i get the "Test connection failed because of an error initializing provider. Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

What am I doing wrong? I really appreciate the help and quick response!

matt

QUOTE(xtaldaz @ Jun 18 2007, 11:19 AM)

Actually, there *is* a driver for the Jet engine as shown here:

http://forums.lavag.org/index.php?act=attach&type=post&id=6131''>http://forums.lavag.org/index.php?act=attach&type=post&id=6131'>http://forums.lavag.org/index.php?act=attach&type=post&id=6131

The error he was receiving was for the "Microsoft OLE DB Provider for ODBC Drivers" and I was telling him to not use ODBC and instead use the highlighted driver "Microsoft Jet 4.0 OLE DB Provider". ODBC is a technology that is being phased out and the OLE DB Providers are usually better written and support more data types.

Yen, you are correct. I've used the Memo field types a number of times with the database toolkit and not had a problem. I really think it's a driver issue.

[xtal]

QUOTE(warnermf @ Jun 20 2007, 11:12 AM)

I am using a .dsn file to connect to my database. I don't see any options to change the provider I am using similar to the "Data Link Properties" window you show below. Where do I do this? I tried creating a new .udl file and setting the "Data Link Properties" to the "Jet" Provider but anytime I test the connection to my database i get the "Test connection failed because of an error initializing provider. Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

The version of LabVIEW doesn't matter. The database toolkit hasn't changed since LV 6.0. However, the version of your OS and Access do matter. And more specifically, the version of ADO (or MDAC) you use.

As a quick check, can you get the examples that ship with the toolkit to work? Try the Logging and Playback ones; I know for a fact they use UDL files and the Jet driver for the connection. If these work, then you know the versions of OS, Access, and ADO are fine.

The error you mention seems to be related to the Security options of Access. You might want to double-check with the person administering the database that you belong to the correct workgroup and have the proper access rights to connect with the database.

Otherwise, I'm not sure what else to suggest.