Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

USING DAO/ODBC FAILS TO UPDATE STRING

Status
Not open for further replies.
gwinn7

gwinn7

Programmer
Feb 10, 2001
1,004
US
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+




 
Sort by date Sort by votes
More information...

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

Gary
gwinn7
A+, Network+
 
Upvote 0 Downvote
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..."
(
 
Upvote 0 Downvote
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+
 
Upvote 0 Downvote
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+
 
Upvote 0 Downvote
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+
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

rs120p
  • Locked
  • Question
Which way to move pg_xlog why sym link and not Linux Mount point?
Replies
0
Views
115
rs120p
rs120p
MrCBofBCinTX
  • Locked
  • Question
Trigger/Function Update 1st table when update 2nd table?
Replies
2
Views
89
MrCBofBCinTX
MrCBofBCinTX
guaro001
  • Locked
  • Question
remove old odbc driver
Replies
1
Views
93
gwinn7
gwinn7
spperl
  • Locked
  • Question
Like Search using full text index
Replies
1
Views
76
spperl
spperl
TLowder
  • Locked
  • Question
Read ID after .update
Replies
0
Views
59
TLowder
TLowder

Part and Inventory Search

Sponsor

Back
Top