×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

USING DAO/ODBC FAILS TO UPDATE STRING

USING DAO/ODBC FAILS TO UPDATE STRING

USING DAO/ODBC FAILS TO UPDATE STRING

(OP)
Using DAO recordsets (from Access97), I am attempting to update a text field (in PostgreSQL) with a string.   When DAO attempts to update the string, it produces the following error...

"3163: The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data."

The problem with this is that the string is not very large and well within the declared limits in the PostgreSQL database column.  I have tried declaring the column as 'text', 'varchar(125)', and because I was desparate, "bytea".   

Any string that is shorter in length seems to update fine.   Here is the string...

"MARINA DEL SOL STILL BLDG NEW SALENJH CHKD MAP"

The length of this string is 46 characters.  

I checked the ODBC driver configuration, but no clues.  

Can you help?  Thanks, I will keep working on it, but if you could provide any hint, I would appreciate it.

Gary
gwinn7
A+, Network+




RE: USING DAO/ODBC FAILS TO UPDATE STRING

(OP)
More information...

Any string over 30 characters seems to be triggering the error.

Gary
gwinn7
A+, Network+

RE: USING DAO/ODBC FAILS TO UPDATE STRING

Hmmm... You get the error message "Try inserting or pasting less data."

What exactly are you doing when this happens? Are you simply entering the text in an Access form field, and the error pops up? In other words, does this error occur during user interaction with the Access front end? Are you sure this is an ODBC/PostgreSQL problem, or could it possibly be related to your form field configuration?

-------------------------------------------

"Now, this might cause some discomfort..."
(http://www.wired.com/news/politics/0,1283,51274,00.html)

RE: USING DAO/ODBC FAILS TO UPDATE STRING

(OP)
I am simply assigning a DAO field value. This is a simplified example:

For each fld in rcTarget.Fields
   fld = rcSource(rcTarget.Name)
Next fld

I actually have more going on inside of the "for each" that really isn't relevant.  What makes this even more bizarre is that later in the code, I have a much larger string populating a field in a separate table in PostgreSQL with no problems.  The length of that string is near 107 characters.  Go figure!  Sigh!

I have dropped and reconstructed the table several times in hopes of alleviating this problem, but no success.  Out of mere troubleshooting, I even restarted the PostgreSQL engine after performing the table reconstruction. Plus, I checked the buffer size in the ODBC DSN and how it handles strings.

Round two is today.  If you have any more ideas, it would be grand.   

Thank you for responding,

Gary
gwinn7
A+, Network+

RE: USING DAO/ODBC FAILS TO UPDATE STRING

(OP)
To answer your question further, its a data transfer between an Access table and a PostgreSQL table.  I don't remember why I simply didn't use an Append Query, but I will probably give that a try today too.  

Thanks again,
Gary
gwinn7
A+, Network+

RE: USING DAO/ODBC FAILS TO UPDATE STRING

(OP)
RESOLVED!

CAUSE:

The field size was actually recorded in Access as "30".   Even though PostgreSQL displays the field as the proper size, say "254", Access apparently still believes the field is of size 30.   If you make a schema change to the database structure, Access needs to be updated manually. Apparently, DAO and the JET/ODBC Database Engine uses the Access schema definition of the table rather than pulling it from the live database.  Therefore...

RESOLUTION:

#1  In your code or a debug window, use the "RefreshLink" method on the TableDef object. Example...

Currentdb.Tabledefs("MyPostgresTable").RefreshLink

#2  Delete the table from Access and re-link it.

Gary
gwinn7
A+, Network+

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close