473,395 Members | 1,742 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

stumped - 'field too small to accept....' (err number 3163) error only with MDE on production server

Anyone have suggestions on where to troubleshoot this error?

Background:
-Access 2k v. 9.0.6926 sp3
- front and backend on production server (wiindows 2k)
-accessed via Citrix
-front-end is mde, backend is mdb
-only occurs on the production server
-only occurs with mde verson - i've tested the problem with the mdb version
of the front-end and it loads/generates the document fine on the production
server
-i've zipped and loaded the app on my work PC and the mde version
loads/generates the document fine

Feedback greatly appreciated.

Thanks.
Nov 13 '05 #1
7 7222
astro wrote:
Anyone have suggestions on where to troubleshoot this error?

Background:
-Access 2k v. 9.0.6926 sp3
- front and backend on production server (wiindows 2k)
-accessed via Citrix
-front-end is mde, backend is mdb
-only occurs on the production server
-only occurs with mde verson - i've tested the problem with the mdb version
of the front-end and it loads/generates the document fine on the production
server
-i've zipped and loaded the app on my work PC and the mde version
loads/generates the document fine

Feedback greatly appreciated.

Thanks.

Do you have any idea which field is attempted to be filled? If so,
compare its type and size in the table.

Look for Table Specification in help. Are you attempting to stuff a
memo with more than 65,535 characters? Are you attempting to stuff a
text field with more than 255 characters?
Nov 13 '05 #2
"astro" <as*********@mn.rr.com> wrote in
news:Jp*****************@tornado.rdc-kc.rr.com:
Anyone have suggestions on where to troubleshoot this error?

Background:
-Access 2k v. 9.0.6926 sp3
- front and backend on production server (wiindows 2k)
-accessed via Citrix
-front-end is mde, backend is mdb
-only occurs on the production server
-only occurs with mde verson - i've tested the problem with the
mdb version of the front-end and it loads/generates the document
fine on the production server
-i've zipped and loaded the app on my work PC and the mde version
loads/generates the document fine

Feedback greatly appreciated.


Well, you've described the environment and the error that results,
but not what is being done that produces the error.

Is it a person editing a field?

A person editing a field/record that causes VBA events to fire that
is then updating fields?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #3
This is VBA code that runs after a user selects an Order from a combobox.
It moves data from one pair of Access tables (based on unix legacy data) to
another set of Access tables.

It iterates through a recordset (the source data) and copies data - field by
field to another recordset (the document record).

I selected the same order this morning and it generated the document without
any error messages....I did do another mde generation last night from work
on unrelated issues. The unix based data is also regenerated each evening
(downloaded into text files), and subsequently reimported into the Access
system at 3 am. So the source data is regenerated each morning.

Here is the code:
============================
============================
Private Sub loadCustomerInfo(ByRef arsTarget As DAO.Recordset)
Dim strSQL As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim str As String
Dim vlookup As Variant

On Error GoTo errhdl

'=================================
'passed rs is rs we are adding to (arsTarget)
'rs in this routine is the data we want to create a new Doc record with
'=================================

'generate Doc rec from order or invoice record
strSQL = objInsert.SQLCustomer
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.EOF And rs.BOF Then
Err.Raise gclngBlankCustomerData
End If

'loop thru all fields in this rs - they should have the same name as the Doc
fields (on purpose)
'update each match between ars and rs

For Each fld In rs.Fields
Debug.Print "source rs field name: " & fld.name
Debug.Print "source rs field value: " & fld.value
Debug.Print "target field name: " & arsTarget.Fields(fld.name).name

If Not IsNull(fld.value) Then
If arsTarget.Fields(fld.name).Type = dbDate And fld.Type = dbText
And Len(fld.value) = 8 Then
'8 digit date
arsTarget.Fields(fld.name) = CDate(Left((fld), 4) & "-" &
mID(fld, 5, 2) & "-" & Right(fld, 2))
ElseIf arsTarget.Fields(fld.name).Type = dbDate And fld.Type =
dbText And Len(fld.value) = 6 Then
'6 digit date
'which date format?
If Left((fld), 2) = "19" Or Left((fld), 2) = "20" Then
'format: yyyymm
arsTarget.Fields(fld.name) = CDate(Right(fld, 2) & "-" &
"01" & "-" & Left((fld), 4))
Else
'format: mmddyy
arsTarget.Fields(fld.name) = CDate(Left((fld), 2) & "-" &
mID(fld, 3, 2) & "-" & Right(fld, 2))
End If

Else
If fld.name = "ship via code" Then
GoTo nextfld
ElseIf fld.name = "PYMT TERMS CODE" Then
'translate this into a description based on lookup table
value
vlookup = DLookup("[DESCRIPTION]", "tbllookup_PaymentTerms",
"[code] = " & Chr(34) & fld.value & Chr(34))
If IsNull(vlookup) Then
'do not populate this column
Else
arsTarget.Fields(fld.name) = CStr(vlookup)
End If

Else
arsTarget.Fields(fld.name) = fld
End If
End If
nextfld:
Debug.Print fld.name & "=" & fld
End If
Next

Exit Sub

errhdl:
If Err.Number = gclngBlankCustomerData Then
gErr.setErrInfo_text gclngBlankCustomerData, mcstrClassName,
"loadCustomerInfo", "No customer found for: " & mstrAcctID
Err.Raise gclngBlankCustomerData
ElseIf Err.Number = gclngUpdateFieldError Then
gErr.setErrInfo_text gclngUpdateFieldError, mcstrClassName,
"loadCustomerInfo", "problem trying to insert new record(s) for: " &
mstrAcctID
Err.Raise gclngUpdateFieldError
ElseIf Err.Number = 3421 Then
'data conversion error - let user know
gErr.setErrInfo_text Err.Number, mcstrClassName, "loadCustomerInfo",
"data conversion error trying to import value: " & "'" & CStr(fld.value) &
"' into field [" & fld.name & "]"
Err.Raise Err.Number
Else
gErr.seterrinfo Err, mcstrClassName, "loadCustomerInfo"
Err.Raise gclngExDocInsertFail
End If

End Sub

============================
============================
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...
"astro" <as*********@mn.rr.com> wrote in
news:Jp*****************@tornado.rdc-kc.rr.com:
Anyone have suggestions on where to troubleshoot this error?

Background:
-Access 2k v. 9.0.6926 sp3
- front and backend on production server (wiindows 2k)
-accessed via Citrix
-front-end is mde, backend is mdb
-only occurs on the production server
-only occurs with mde verson - i've tested the problem with the
mdb version of the front-end and it loads/generates the document
fine on the production server
-i've zipped and loaded the app on my work PC and the mde version
loads/generates the document fine

Feedback greatly appreciated.


Well, you've described the environment and the error that results,
but not what is being done that produces the error.

Is it a person editing a field?

A person editing a field/record that causes VBA events to fire that
is then updating fields?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #4
astro wrote:
This is VBA code that runs after a user selects an Order from a combobox.
It moves data from one pair of Access tables (based on unix legacy data) to
another set of Access tables.

It iterates through a recordset (the source data) and copies data - field by
field to another recordset (the document record).

I selected the same order this morning and it generated the document without
any error messages....I did do another mde generation last night from work
on unrelated issues. The unix based data is also regenerated each evening
(downloaded into text files), and subsequently reimported into the Access
system at 3 am. So the source data is regenerated each morning.

Here is the code:
============================
============================
Private Sub loadCustomerInfo(ByRef arsTarget As DAO.Recordset)
Dim strSQL As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim str As String
Dim vlookup As Variant

On Error GoTo errhdl

'=================================
'passed rs is rs we are adding to (arsTarget)
'rs in this routine is the data we want to create a new Doc record with
'=================================

'generate Doc rec from order or invoice record
strSQL = objInsert.SQLCustomer
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.EOF And rs.BOF Then
Err.Raise gclngBlankCustomerData
End If

'loop thru all fields in this rs - they should have the same name as the Doc
fields (on purpose)
'update each match between ars and rs

For Each fld In rs.Fields
Debug.Print "source rs field name: " & fld.name
Debug.Print "source rs field value: " & fld.value
Debug.Print "target field name: " & arsTarget.Fields(fld.name).name

If Not IsNull(fld.value) Then
If arsTarget.Fields(fld.name).Type = dbDate And fld.Type = dbText
And Len(fld.value) = 8 Then
'8 digit date
arsTarget.Fields(fld.name) = CDate(Left((fld), 4) & "-" &
mID(fld, 5, 2) & "-" & Right(fld, 2))
ElseIf arsTarget.Fields(fld.name).Type = dbDate And fld.Type =
dbText And Len(fld.value) = 6 Then
'6 digit date
'which date format?
If Left((fld), 2) = "19" Or Left((fld), 2) = "20" Then
'format: yyyymm
arsTarget.Fields(fld.name) = CDate(Right(fld, 2) & "-" &
"01" & "-" & Left((fld), 4))
Else
'format: mmddyy
arsTarget.Fields(fld.name) = CDate(Left((fld), 2) & "-" &
mID(fld, 3, 2) & "-" & Right(fld, 2))
End If

Else
If fld.name = "ship via code" Then
GoTo nextfld
ElseIf fld.name = "PYMT TERMS CODE" Then
'translate this into a description based on lookup table
value
vlookup = DLookup("[DESCRIPTION]", "tbllookup_PaymentTerms",
"[code] = " & Chr(34) & fld.value & Chr(34))
If IsNull(vlookup) Then
'do not populate this column
Else
arsTarget.Fields(fld.name) = CStr(vlookup)
End If

Else
arsTarget.Fields(fld.name) = fld
End If
End If
nextfld:
Debug.Print fld.name & "=" & fld
End If
Next

Exit Sub

errhdl:
If Err.Number = gclngBlankCustomerData Then
gErr.setErrInfo_text gclngBlankCustomerData, mcstrClassName,
"loadCustomerInfo", "No customer found for: " & mstrAcctID
Err.Raise gclngBlankCustomerData
ElseIf Err.Number = gclngUpdateFieldError Then
gErr.setErrInfo_text gclngUpdateFieldError, mcstrClassName,
"loadCustomerInfo", "problem trying to insert new record(s) for: " &
mstrAcctID
Err.Raise gclngUpdateFieldError
ElseIf Err.Number = 3421 Then
'data conversion error - let user know
gErr.setErrInfo_text Err.Number, mcstrClassName, "loadCustomerInfo",
"data conversion error trying to import value: " & "'" & CStr(fld.value) &
"' into field [" & fld.name & "]"
Err.Raise Err.Number
Else
gErr.seterrinfo Err, mcstrClassName, "loadCustomerInfo"
Err.Raise gclngExDocInsertFail
End If

End Sub

============================
============================


Does this blow up on each record you are copying or specific records?

I would think you should
1) Know the ID of the record it blows up on
2) The fieldname it blows up on
3) The value being updated

It it works on most records and blows up on another, then I would test
for records it blows up on.

I would see if it blows up on only 1 field or all fields. If it's a
specific field, you've narrowed it down.

You should also know what is the value being inserted?

In your error routine you might enter something like
msgbox "ID = " & rs!ID & vbNewLine _
"Field = " & fld.name & vbNewLine & _
"Value = " & fld.Value
at least during this debug.
Nov 13 '05 #5
Salad <oi*@vinegar.com> wrote in
news:9J*****************@newsread2.news.pas.earthl ink.net:
Does this blow up on each record you are copying or specific
records?

I would think you should
1) Know the ID of the record it blows up on
2) The fieldname it blows up on
3) The value being updated

It it works on most records and blows up on another, then I would
test for records it blows up on.

I would see if it blows up on only 1 field or all fields. If it's
a specific field, you've narrowed it down.

You should also know what is the value being inserted?

In your error routine you might enter something like
msgbox "ID = " & rs!ID & vbNewLine _
"Field = " & fld.name & vbNewLine & _
"Value = " & fld.Value
at least during this debug.


Or turn off the error handler to see exactly which line is throwing
the error.

I'd also re-engineer the code to put the date processing in a called
subroutine, even if it's only ever called from this process. It
would make this code look substantially simpler if the actual field
update were handled in a single line of code.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #6
David W. Fenton wrote:
Salad <oi*@vinegar.com> wrote in
news:9J*****************@newsread2.news.pas.earthl ink.net:

Does this blow up on each record you are copying or specific
records?

I would think you should
1) Know the ID of the record it blows up on
2) The fieldname it blows up on
3) The value being updated

It it works on most records and blows up on another, then I would
test for records it blows up on.

I would see if it blows up on only 1 field or all fields. If it's
a specific field, you've narrowed it down.

You should also know what is the value being inserted?

In your error routine you might enter something like
msgbox "ID = " & rs!ID & vbNewLine _
"Field = " & fld.name & vbNewLine & _
"Value = " & fld.Value
at least during this debug.

Or turn off the error handler to see exactly which line is throwing
the error.


The problem is that it works OK with an MDB but not with and MDE. Would
your solution work with an MDE?
I'd also re-engineer the code to put the date processing in a called
subroutine, even if it's only ever called from this process. It
would make this code look substantially simpler if the actual field
update were handled in a single line of code.


The problem, at least by the poster, is that it works OK with an MDB but
not with an MDE. I have no idea of the poster is testing with some test
data and it blows up with production data or if the same records produce
different results.
Nov 13 '05 #7
Salad <oi*@vinegar.com> wrote in
news:ZO*****************@newsread2.news.pas.earthl ink.net:
David W. Fenton wrote:
Salad <oi*@vinegar.com> wrote in
news:9J*****************@newsread2.news.pas.earthl ink.net:

Does this blow up on each record you are copying or specific
records?

I would think you should
1) Know the ID of the record it blows up on
2) The fieldname it blows up on
3) The value being updated

It it works on most records and blows up on another, then I would
test for records it blows up on.

I would see if it blows up on only 1 field or all fields. If
it's a specific field, you've narrowed it down.

You should also know what is the value being inserted?

In your error routine you might enter something like
msgbox "ID = " & rs!ID & vbNewLine _
"Field = " & fld.name & vbNewLine & _
"Value = " & fld.Value
at least during this debug.

Or turn off the error handler to see exactly which line is
throwing the error.


The problem is that it works OK with an MDB but not with and MDE.
Would
your solution work with an MDE?


Yes, but you'd have to recreate the MDE.

But you'd have to do that, anyway, to incorporate your code.

I guess if you want to avoid having a testing-only version, you
could do your version and maybe have a global debug variable that
controls whether or not your code runs.

Not that it really matters if there's unseen output to the Debug
window...
I'd also re-engineer the code to put the date processing in a
called subroutine, even if it's only ever called from this
process. It would make this code look substantially simpler if
the actual field update were handled in a single line of code.


The problem, at least by the poster, is that it works OK with an
MDB but not with an MDE. I have no idea of the poster is testing
with some test data and it blows up with production data or if the
same records produce different results.


I'd suspect a difference in Jet version as the cause of such a
difference, rather than the MDE/MDB, unless, of course, both have
been tried on the same machine.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mark Creelman | last post by:
Hello all: How do I convert this ASP mailer script to accept and mail multiple fields of a form. This will do one field only )iMesg.TextBody = Request.Form("body") and it works great. Seems...
2
by: Doslil | last post by:
I am trying to validate the fields in my database.I have already validated the fields to check for not null.Here is what I have written for Numeric and text field. Private Function EENUM() On...
5
by: F. Barth | last post by:
Hello, I've posted this problem to one other newsgroups, and gotten some diagnostic help, but I still need a solution. The full text of the message box is: "The field is too small to accept the...
8
by: Tony Williams | last post by:
I have a form where the user inputs a series of figures into a number of controls. After they've input the total (they don't want the program to create the total) there is a check to make sure the...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
7
by: Joe Ross | last post by:
I've been working with Microsoft support for over 3 weeks now on an intermittent General Network Error we're seeing in our production environment between our ASP.NET application and SQL Server...
8
by: Chris A via AccessMonster.com | last post by:
I have an interesting problem that I have yet to come accross that I can't change data structure on because it is an export from filemaker I am reformatting for another dept. anyway. I have a table...
16
by: Mark | last post by:
Hello. I am attempting to use AppendChunk() to write binary data to a memo field in Access 2000. My initial call to AppendChunk() results in a data type conversion error. Any suggestions? Here...
2
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so far no luck with field lookup properties. What...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.