Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-06-30
17
Medium Priority
?
419 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
[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
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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
 
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 1000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 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