Solved

Microsoft Office Access 3163 Error

Posted on 2011-02-14
17
884 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 100

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now