Solved

3349 (Numeric Overflow) error when importing to split database table

Posted on 2009-06-30
17
407 Views
Last Modified: 2013-11-27
I have a database that pulls in .txt files and appends them all to a table.  I used an import specification, called by a module, to accomplish this.  The database works perfectly until I split it.  Once split, I am getting a "3349 - Numeric Overflow" error.  

I understand this has to do with the data types.  Originally, I was pulling the data in as text, date/time, and currency.  I've since tried a few different combinations (replacing currency with long, replacing text wtih memo, etc) with no luck.  What is most frustrating is that this functions correctly until I split the DB.  

I have confirmed that the data types are the same between the import spec and the destination table.  What else should I look for?  I've attached an image that shows the text file, most of the import spec, and the design view of the destination table.
access-import-error.jpg
0
Comment
Question by:rjproulx
  • 7
  • 5
  • 5
17 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24747766
You might try (1) delete and relink the table and (2) recreate the import specification on a COPY of your database.
0
 

Author Comment

by:rjproulx
ID: 24748395
Thanks cwood.  Can you explain more about deleting and relinking the table?  You want me to delete the table on the back-end, then re-run the import?  That'll give me a local (front-end) table...can I then import that into the back-end again?  Or am I confused here?

And I'll go ahead and try rebuilding the import spec.

And yes, I'm doing ALL of this on a copy of my db :)
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24748737
You can delete the link in the front-end (FE) by selecting the linked table and press Delete.
You can relink the table by (in the FE) selecting Tables => New => Link Tables => navigate to and select the back-end (BE) database => select the table in the BE => click OK.

Are you importing the exact same text file in the combined and split databases?
0
 

Author Comment

by:rjproulx
ID: 24748814
To answer your last question, yes, it is the same exact file in both instances.

Wanted to give everyone a bit of a breakthrough I just had, maybe this will help.  Knowing it was a data type issue, I went ahead and changed every field in the import spec to "text".  Then I changed every field in the table to "text".  It worked!  My plan was then to change the fields back to their original data type, one my one.  Instantly I got the error as soon as I changed "Dep Date" to 'date/time'.  From here, though, I'm stuck.  I kinda need that to come in as a date, or at least be able to change from text to date somewhere early in the process.  Would it help if I posted the text file I'm importing, so you can see that the first 9 digits from the left are trying to import as 'date/time'?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24748825
Is there any reason you are using Memo fields instead of Text?

A Text field can hold up to 255 characters.
0
 

Author Comment

by:rjproulx
ID: 24748843
Sorry jimpen, using memo was just me testing new datatypes.  I should have re-done the screenshot with my original attempt, which was having everything as 'text'.  
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24748846
It looks from the graphic as though the first *eight* digits from the left is the date/time and the ninth digit is a space. Could that be the problem?
0
 

Author Comment

by:rjproulx
ID: 24748912
Cwood: To test that, I edited the import script to put a field called 'blank1' that starts at position 9 and continues for 1 space.  I then changed the 'Date Dep' field to reflect positions 1 through 8.  This resulted in getting the same error.  I got the error whether or not I checked "skip" on 'blank1'.

Good idea though, I thought maybe you had it.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 38

Expert Comment

by:Jim P.
ID: 24748926
Take off the Four digit Years check box.
0
 

Author Comment

by:rjproulx
ID: 24748932
Also just tried re-linking the tables between front and back ends with no resolution.

Taking a step back here...if I can import everything successfully as 'text', is there a way to perhaps do an update query that then changes the data types?  I need to be able to identify the currency (to be able to sum them) and the dates (to do some before/after logic later in the process), so I can't keep everything as text.  But if I can import as text and then somehow run a query to change the field types, that might work.  Just trying to come at this from another angle...
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24748949
I think jimpen has the answer.
0
 

Author Comment

by:rjproulx
ID: 24748970
jimpen: That had no effect.  I appreciate the help thus far everyone.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24749025
Take off the four digit. Do the fields as text.

Is there a place to say the first line is column names?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24749154
You know what: I've never been a fan of import specs anyway.

Take a look at the following code, and see if it makes any sense.
Public Function Import_Fixed_Width_Q_24534037()
 

Dim FileNum As Integer

Dim InputFile As String

Dim InputString As String

Dim I As Integer
 

Dim StringArray() As String
 

Dim DB As Database

Dim RS As Recordset

Dim SQL As String
 

'Opening the table to write to

Set DB = CurrentDb()

Set RS = DB.OpenRecordset("MyTableName")    '<-- Change to your tablename
 

'Opening the file to read from

FileNum = FreeFile()

InputFile = "C:\MyFolder\MyTextFile"        '<-- Change to your folder and path

Open InputFile For Input Access Read Shared As #FileNum
 

I = 0

'Dump the header lines

Line Input #FileNum, InputString

Line Input #FileNum, InputString            'Read the data in
 
 

Do Until EOF(FileNum) = True

Line Input #FileNum, InputString            'Dump the header lines

Line Input #FileNum, InputString            'Read the data in
 
 

Do Until EOF(FileNum) = True

    With RS                                 'the input is an empty string write it

        .AddNew

        ![Dep Date] = Mid(InputString, 1, 8)

        ![Policy Number] = Mid(InputString, 10, 19)

        '..........

        .Update

    End With

    Line Input #FileNum, InputString

Loop
 

Close FileNum

Set RS = Nothing

Set DB = Nothing
 

End Function

Open in new window

0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 250 total points
ID: 24749506
This is a strange problem. Here is what I suggest:
1. Import the table from the BE into the FE and rename it (tblTemp?).
2. Change your code to:
    a. Clear the temp table (delete all).
    b. Import the text file into the temp table.
    c. Run an Append query to append the temp table to the linked BE table.
    d. Clear the temp table.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24749643
I had another asker run into a very similar problem just last week:

Error 3349 'Numeric field overflow' when importing text file to linked table
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24522066.html
0
 

Author Closing Comment

by:rjproulx
ID: 31598882
Thanks for the info cwood.  I was hoping to use my existing queries and figure out what was behind this error, but in the end, the extra three tables and six queries required to go this route weren't very painful at all.  And to my users, it's seamless.  Best of all, it works!  Thanks Jim for the ideas, they were just a bit over my head.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

19 Experts available now in Live!

Get 1:1 Help Now