[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Microsoft Office Access 3163 Error

Posted on 2011-02-14
17
Medium Priority
?
952 Views
Last Modified: 2012-05-11
Everyone,

I'm running into a Microsoft Office Access 3161 Error which states:

The Field is too small to accept the data you attempted to add. Try inserting or pasting less data.

Basically i'm trying to add a manifest (an excel spreadsheet) to a form in Access. The form asks me to point to my hardrive for the spreadsheet and then it asks for a particular date. After I submit it comes back with this error.

I'm very new to Access so any information you provide you will have to tell me what to do in baby steps. For instance, if you tell me I have to "increase my table size in properties window" I need to know how to get there.

This is an Access front end with a SQL server backend (not sure if that info helps).

0
Comment
Question by:nellafurtado
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +1
17 Comments
 

Author Comment

by:nellafurtado
ID: 34890308
Sorry, it's a 3163 Error
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890385
This error says that the column in table is not having sufficient size to accommodate the data that you are trying to save.

I assume it is a column that some sort of string data.
Increase the size of the datatype.

Raj
0
 

Author Comment

by:nellafurtado
ID: 34890433
How do I increase the size of the datatype?

I'm new to Access.

Thanks!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890442
First find out which column is causing this error.

Then go to sql server management studio tool from start menu.
Login - Expand your database - Tables - Right-click modify - increase data size of that column
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34890443
It sounds like your app is either trying to
A) store a spreadsheet into the database  (in which case the control must be an OLE control)
B) trying to store the path to the spreadsheet (in which case your path could be too long requiring you to make the field larger)

Without additional information about what your application is doing, it would be difficult to understand the cause of your problem

It is sort of telling a doctor over the phone....   Doc it hurts here.....   (what is here?)

the Error 3163 is an "Application / Object Defined error"  meaning that it does not appear to be a microsoft error...  meaning that without knowing alot more about your app....  

Sorry  
0
 

Author Comment

by:nellafurtado
ID: 34890526
Raj,

How do I determine which column is causing the error?
0
 

Author Comment

by:nellafurtado
ID: 34890561
RqGray3,

I'm pulling the spreadsheet off my desktop so the file path isn't long at all, I thought that might have been it at first as well. Also, I tried renaming the spreadsheet to a simple one word title.  That still didn't help.

From what I understand, the information gets pulled off the spreadsheet and put in the right spot on the SQL server database, all via a front end access form.  I hope that helps define the problem better?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890595
Not easy to assist regarding that!
Open Access and press and hold Shift key. This open access in design mode. Try to find out the form that caused this issue.
I think, double click on the control will go the code - Or right click 'View Code'
If you have some basic coding knowledge, you can find it out
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890620
>> I'm pulling the spreadsheet off my desktop so the file path isn't long at al

Desktop folder location is inside 'Documents and Settings' in c drive by default - which could be high length
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 34890869
Again without knowing what the program is trying to do and what field and type of control is being used....  and what is being "transfered (the path, the object or a range of data)  

I am at a loss
0
 

Author Comment

by:nellafurtado
ID: 34891046
Working through solutions provided...
0
 

Author Comment

by:nellafurtado
ID: 34901374
Raj you said "Not easy to assist regarding that!
Open Access and press and hold Shift key. This open access in design mode. Try to find out the form that caused this issue.
I think, double click on the control will go the code - Or right click 'View Code'
If you have some basic coding knowledge, you can find it out"


This is the code for the browseExcel button that I think is causing the error. It looks like it's okay to me, but I don't know completely what I need to be looking for.
Option Compare Database
Private Sub btn_cancel_Click()

Dim db As Database

Dim strORD As String
Dim strORN As String

Set db = CurrentDb

If Me.OpenArgs = "UNIT" Then
    strORD = Forms!frm_OrdersCreation.OrderDt
    strORN = Forms!frm_OrdersCreation.OrderNbr
    
    db.Execute ("DELETE * FROM tbl_OrderLog WHERE OrderDt = #" & strORD & "# and OrderNbr = '" & strORN & "'; ")
End If

DoCmd.Close acForm, "Frm_BrowseExcel"

End Sub

Private Sub CmdXLBrowse_Click()

Dim strMSN As String

Dim objXLS As Object    'MS EXCEL

Dim db As Database      'Current database
Dim tb1, tb2 As Recordset

Set db = CurrentDb

Set tb1 = db.OpenRecordset("EOC_Manifest")
Set tb2 = db.OpenRecordset("REINTEGRATION_LIST", dbOpenDynaset, dbSeeChanges)

Me.txtXLFIle.Value = FindFile(Environ("UserProfile") & "\", "Please Select an Excel File", "Excel Files", "*.xl*")

If Me.txtXLFIle = "" Or IsNull(Me.txtXLFIle) Then
    MsgBox ("File was not chosen.  Process has been cancelled.")
    DoCmd.Close acForm, "Frm_BrowseExcel"
    Exit Sub
End If

If Me.OpenArgs = "MANIFEST" Or IsNull(Me.OpenArgs) Then
    'Deletes ALL information in Table
    db.Execute "DELETE * FROM [EOC_Manifest]; "
    
    'IMPORTS EXCEL file
    On Error GoTo XLSerror
    If Right(Me.txtXLFIle, 4) = ".xls" Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "EOC_Manifest", txtXLFIle, False, "!A1:H9999"
    Else
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "EOC_Manifest", txtXLFIle, False, "!A1:H9999"
    End If
    On Error GoTo err_normal
    
    'Deletes EXTRA Records in Table
    db.Execute "DELETE * FROM [EOC_Manifest] WHERE [F1] is null AND [F2] IS NULL; "
    db.Execute "DELETE * FROM [EOC_Manifest] WHERE [F1] is null AND [F2] = ''; "

    'Cleans data in Table
    db.Execute "UPDATE EOC_Manifest SET EOC_Manifest.F1 = Replace([F1],' ',''); "
    db.Execute "UPDATE EOC_Manifest SET EOC_Manifest.F1 = Replace([F1],'-',''); "
    db.Execute "UPDATE EOC_Manifest SET EOC_Manifest.F1 = '0' + [F1] WHERE LEN([F1]) = 8; "
    db.Execute "UPDATE EOC_Manifest SET EOC_Manifest.F1 = '00' + [F1] WHERE LEN([F1]) = 7; "
    strMSN = Mid(DLookup("F1", "EOC_Manifest", "LEFT(F1, 3) = 'msn'"), 4, 5)
    db.Execute "DELETE * FROM [EOC_Manifest] WHERE LEN([F1]) <> 9 AND [F2] IS NULL; "
    db.Execute "DELETE * FROM [EOC_Manifest] WHERE LEN([F1]) = 3; "
    db.Execute "UPDATE (SPF INNER JOIN EOC_Manifest ON SPF.SSN = EOC_Manifest.F1) INNER JOIN LOCAL_UIC_INFO ON SPF.ASG_UIC = LOCAL_UIC_INFO.UIC " & _
               "SET EOC_Manifest.F2 = [SPF]![NAME_SOLDR], EOC_Manifest.F3 = [SPF]![GENDER], EOC_Manifest.F4 = [SPF]![RANK], EOC_Manifest.F5 = [SPF]![ASG_UIC], EOC_Manifest.F6 = [LOCAL_UIC_INFO]![UNIT_CO], EOC_Manifest.F7 = [SPF]![PMOS]; "
    
    DoCmd.Close acForm, "frm_BrowseEXCEL"
    DoCmd.OpenForm "frm_Reintegration_MASS", acNormal, , , , , strMSN
    Exit Sub
End If

Exit Sub

err_normal:
    MsgBox Err.Number & Err.Description
    Resume Next

err_email:
    MsgBox ("Email was not sent!")
    Exit Sub
    Resume Next

XLSerror:
    'On Error Resume Next
    MsgBox (Err.Number & " - " & Err.Description)
    MsgBox ("File was not in correct format.  Process has been cancelled.")
    DoCmd.Close acForm, "Frm_browseExcel"
    Exit Sub
    
End Sub

Open in new window

0
 

Author Comment

by:nellafurtado
ID: 34901381
Or it could be this bit....
Option Compare Database

Private Sub btn_cancel_Click()

DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

Private Sub btn_SUBMIT_Click()
On Error GoTo err_handler

Dim strMSN As String
Dim strQueryName As String

Dim qryDEF As QueryDef

Dim db As Database      'Current database
Dim tb1, tb2 As Recordset

Set db = CurrentDb

Set tb1 = db.OpenRecordset("EOC_Manifest")
Set tb2 = db.OpenRecordset("REINTEGRATION_LIST", dbOpenDynaset, dbSeeChanges)

If Me.txt_DT <> "" Then
    With tb1
        .MoveFirst
        Do Until .EOF
            With tb2
                .AddNew
                    If tb1!F1 <> "" Then !SSN = tb1!F1
                    If tb1!F2 <> "" Then !NAME_SOLDR = tb1!F2
                    If tb1!F4 <> "" Then !RANK = tb1!F4
                    If tb1!F5 <> "" Then !UIC = tb1!F5
                    If tb1!F6 <> "" Then !UNIT_NAME = tb1!F6
                    If tb1!F7 <> "" Then !PMOS = tb1!F7
                    If Me.OpenArgs <> "" Then !MSN_NBR = Me.OpenArgs
                    !REINT_START_DT = Me.txt_DT
                .Update
            End With
            .MoveNext
        Loop
    End With
    Set tb1 = Nothing
    Set tb2 = Nothing
    strMSN = Me.OpenArgs
    DoCmd.Close acForm, Me.Name, acSaveYes

    '**********************************************************************************************************************************************************************************
    'Determines Query Name
    strQueryName = "Finance List - MSN " & strMSN
    '**********************************************************************************************************************************************************************************
    '**********************************************************************************************************************************************************************************
    'Deletes Query if already Exists
    On Error Resume Next
    db.QueryDefs.Delete strQueryName
    On Error GoTo err_handler
    '**********************************************************************************************************************************************************************************
    '**********************************************************************************************************************************************************************************
    'Determines SQL Statement to use for EXPORT
    strSQL = "SELECT MSN_NBR AS MSN, REINT_START_DT, RANK, NAME_SOLDR, REINTEGRATION_LIST.SSN, UNIT_NAME, UIC, DPL_DT " & _
             "FROM REINTEGRATION_LIST LEFT JOIN DEPLOY_DT_V ON REINTEGRATION_LIST.SSN = DEPLOY_DT_V.SSN " & _
             "WHERE MSN_NBR = '" & strMSN & "'"
    '**********************************************************************************************************************************************************************************
    '**********************************************************************************************************************************************************************************
    'Creates Query
    Set qryDEF = db.CreateQueryDef(strQueryName, strSQL)
    '**********************************************************************************************************************************************************************************
    '**********************************************************************************************************************************************************************************
    'Exports Levy List in Excel Format
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQueryName, Environ("UserProfile") & "\Desktop\Finance List (MSN " & strMSN & ").xlsx"
    '**********************************************************************************************************************************************************************************
    '**********************************************************************************************************************************************************************************
    'Deletes Query
    On Error Resume Next
    db.QueryDefs.Delete strQueryName
    On Error GoTo err_handler
    '**********************************************************************************************************************************************************************************
    Set db = Nothing
    MsgBox DCount("F2", "EOC_Manifest", "[F2] IS NOT NULL") & " Soldiers from Mission # " & strMSN & " have successfully been added into the database.  A Finance Report for this manifest has been placed on your desktop.", vbOKOnly, "SYSTEM INFORMATION"
    Forms!frm_main_menu.Form.Requery
    Forms!frm_main_menu.Form.Refresh
    Exit Sub
Else
    MsgBox "Reintegration Start Date is required.", vbOKOnly, "SYSTEM VALIDATION"
    Exit Sub
End If

err_handler:
    If Err.Number = 3265 Then
        Resume Next
    Else
        MsgBox ("Error Number: " & Err.Number & vbCrLf & "Error Descrioption: " & Err.Description)
        Set db = Nothing
        Exit Sub
    End If

End Sub

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34990657
@nellafurtado:
I am not highly experienced in MS Access Coding.
Please click on the 'Request Attention' button below to the question and request Moderator for inviting attention of more MS Access Experts.

Raj
0
 

Accepted Solution

by:
nellafurtado earned 0 total points
ID: 35020586
Well, I found out guys that if I took off several data rows of names out of the excel spreadsheet and then submitted it, it worked just fine. It used to be able to handle a lot more that 15 names though. I don't know what could have possibly changed in the code for it to only accept 15 names now?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35399306
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question