InfoPath won't talk to Access after 255 characters. Workaroundplease?

[Rachel Garrett]

As early as this morning, I was calling InfoPath my favorite Microsoft
product. That was before I found out that it disables submission of
information back to the database if anything you're reading from it is
too long:

"Submit status: Not allowed - One or more of the columns in the data
source has a long data type (text, ntext, hyperlink or image). Long
data types are not supported."

The "workaround" I see listed, when I Google this text, is that I
should write a web service. I don't have time to learn how before this
work project is due on Friday, and it doesn't seem to me like that
could be the most efficient way to do it. Here are my thoughts:

1) I could convert all the tables in the Access database to XML, and
write my forms again from scratch using the XML documents as the data
source(s). I haven't yet figured out how to submit changes to an XML
data source, just by filling out an InfoPath form. The "submit" button
properties don't seem to allow this.

2) I could forget InfoPath and write a form in Access instead.

3) I could keep the Access data >> InfoPath forms that exist, and
figure out a way to append the data collected in InfoPath into an
Excel spreadsheet, which I then could append to the existing tables in
Access.

I feel like I must be doing something wrong, because letting users
interact with data (i.e. read and change it) is exactly what InfoPath
is supposed to do, but I can't seem to make it work for something that
is actually pretty basic -- letting users search for information in a
database and make changes to a couple date fields and a comments
field.

Thanks,
Rachel Garrett

 

[Clay Fox]

Unfotunately you have discovered one of limitations in InfoPath's default
database connectivity.

You can accomplish what you want but you need SQL and webservices. You can
purchase the web services you need to dynamically submit and query tables
without limitations on large file types.

If you would like to see a demo of InfoPath and database integration you can
send me an email here.
http://www.infopathdev.com/members/clayfox.aspx

 

[Rachel Garrett]

Unfotunately you have discovered one of limitations inInfoPath'sdefault
database connectivity.

You can accomplish what you want but you need SQL and webservices.  You can
purchase the web services you need to dynamically submit and query tables
without limitations on large file types.

If you would like to see a demo ofInfoPathand database integration you can
send me an email here.http://www.infopathdev.com/members/clayfox.aspx

Thanks for the response, but I need to figure out how to make MS
Office products work as advertised. It's too bad that an
administrative assistant has to turn developer in order to do that.

--Rachel

 

[Gavin McKay]

Hi Rachel,

From the sounds of your requirements, I think your first thoughts are
correct - InfoPath may not be the right fit for what you want to do.

InfoPath is exceptional at quickly collecting data and presenting users with
a standards-based method of data entry. From the Microsoft InfoPath overview
website (http://office.microsoft.com/en-us/infopath/HA101656341033.aspx):

"With Office InfoPath 2007, you can create and deploy electronic forms
solutions to gather information efficiently and reliably."

It is designed to support mutliple devices (tablets, mobile, etc.) and be
web-delivered to a large audience if required. But it's strength IMHO is
*collecting* the data, not necessarily allowing users to search and make
changes as with your requirements. If you need to do this with InfoPath,
then yes, you probably do need to develop web services and make it a
"project". It can definitely be done, but it is not a simple process.

Access was built for small workgroups to be able to collect and manage data.
If you want several users to be able to search, insert, update, delete, and
report on data, then Access is definitely a better fit.

HTH, and best of luck with your project

Gavin.

 

[MikeWal]

Not sure this is what you mean but ... if you are trying to save more than
255 characters into a text field in Access then InfoPath is not the problem
at all ... 255 chracters is he maximum length in Access (or most DBs for that
matter) for Text fields. Sounds like you need a Memo field or better
relational design in the DB.

 

[Rachel Garrett]

Not sure this is what you mean but ... if you are trying to save more than
255 characters into a text field in Access then InfoPath is not the problem
at all ... 255 chracters is he maximum length in Access (or most DBs for that
matter) for Text fields.  Sounds like you need a Memo field or better
relational design in the DB.

Mike,

You're right, the field is stored as Memo, which is unsupported by
InfoPath. It's a comments field, so it can't be fixed by changing the
database design. Allowing the users to enter more than 255 characters
is a requirement.

--Rachel

 

[Rachel Garrett]

Hi Rachel,

From the sounds of your requirements, I think your first thoughts are
correct - InfoPath may not be the right fit for what you want to do.

InfoPath is exceptional at quickly collecting data and presenting users with
a standards-based method of data entry.  From the Microsoft InfoPath overview
website (http://office.microsoft.com/en-us/infopath/HA101656341033.aspx):

"With Office InfoPath 2007, you can create and deploy electronic forms
solutions to gather information efficiently and reliably."

It is designed to support mutliple devices (tablets, mobile, etc.) and be
web-delivered to a large audience if required.  But it's strength IMHO is
*collecting* the data, not necessarily allowing users to search and make
changes as with your requirements.  If you need to do this with InfoPath,
then yes, you probably do need to develop web services and make it a
"project".  It can definitely be done, but it is not a simple process.

Access was built for small workgroups to be able to collect and manage data.
 If you want several users to be able to search, insert, update, delete,and
report on data, then Access is definitely a better fit.

Well, it was going to be 90 people, with only 10 or so of them allowed
to make changes. Because of the limitation that users can only submit
back to the database if no field is over 255 characters, the Access
solution will only help 10 people instead of helping everyone. I still
think it's silly that Microsoft touts InfoPath as this great way to
have powerful interactions with your database. If it's really only
*meant* for gathering data, they ought to say so.

Thanks,
Rachel

 

[BobCh]

Can you add a repeating list of comments. I genrally do this so that
I know when a comment was added (with a date field) and by whom (with
a user name field)

like this
Notes
NoteText
DateEntered
Author

IMO, This type of method allows for better management of notes.