InfoPath to Access Database the dreaded memo field (idea!)

[munkee]

Right this is purely an idea,

I have an infopath form which I wish to use to populate my access database.

The database and form require the usage of memo fields, basically long text over 255 characters.

Now I know infopath is unable to submit long fields over 255 characters direct to access unless you use webservices. For me this is not possible as I dont have the access at work to be able to create/upload/mess with such a thing, however I do have an idea.


Would the following be possible:-


I require the field Non conformance description in access to be a memo field as it will hold the description of a non conformance that has occurred whilst someone is at work. This is set within access, all is good.

Now I want them to use a info path form to submit their non conformance report, where the non conformance description will be filled out. Their description is highly likely to be over 255 chars.

Within infopath I create a textbox which is unbound to the database, they type their description in to this box.

I then have a VBA script which when the user clicks submit goes to the description box and copies every 255 characters up until a limit say 1000 characters and pastes each line in to corresponding text boxes that are set to invisible.

So for ease say I have 5 text boxes, set to hidden which the script copies 255 chars from the main description box. These boxes are bound to 5 "holding" fields within my access database.

When the user submits the copy occurs and the form along with the 255chars in each 5 text boxes are appended to the database.

I can then use a query to concatenate these together in to the memo field.

Thoughts?