If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Numeric Field Overflow from Excel File to new Table in Access
I have a linked Table in Access "02TBL" which is linked to an Excel
spreadsheet. Then I have a make table query "02TBLmtq" which takes the entire linked spreadsheet and makes a new table in another Access Database, removing the link to Excel. Even though it is a new table, with no predefined field properties that I know of, I am getting an error message (sometimes) that is numeric field overflow. The first thing I did was remove some errors from Excel #N/A, which helped that field, but for the other fields I can't find any problem areas. Also, the query has no problem displaying the data in the datasheet view section, but when it comes time to perform the make table query, that error is coming up. I have not been able to isolate the error to a single field, because it seems like when I remove one field, I end up getting an error in another field. Any ideas? -- Sincerely, Beverly76 |
#2
|
|||
|
|||
Numeric Field Overflow from Excel File to new Table in Access
If you have field datatypes set wrong access will think you are trying to
delete files and give you a message. Go into design view of the table and see if maybe you have a date field set to text or a text field set to date, or some other datatype error. "Beverly76" wrote: I have a linked Table in Access "02TBL" which is linked to an Excel spreadsheet. Then I have a make table query "02TBLmtq" which takes the entire linked spreadsheet and makes a new table in another Access Database, removing the link to Excel. Even though it is a new table, with no predefined field properties that I know of, I am getting an error message (sometimes) that is numeric field overflow. The first thing I did was remove some errors from Excel #N/A, which helped that field, but for the other fields I can't find any problem areas. Also, the query has no problem displaying the data in the datasheet view section, but when it comes time to perform the make table query, that error is coming up. I have not been able to isolate the error to a single field, because it seems like when I remove one field, I end up getting an error in another field. Any ideas? -- Sincerely, Beverly76 |
#3
|
|||
|
|||
Numeric Field Overflow from Excel File to new Table in Access
Since it is a linked table from Excel, where do the datatypes come from? All
the numbers say they are double, but I can't change that property in the Access table. "This property cannot be modified in linked tables" -- Sincerely, Beverly76 "Golfinray" wrote: If you have field datatypes set wrong access will think you are trying to delete files and give you a message. Go into design view of the table and see if maybe you have a date field set to text or a text field set to date, or some other datatype error. "Beverly76" wrote: I have a linked Table in Access "02TBL" which is linked to an Excel spreadsheet. Then I have a make table query "02TBLmtq" which takes the entire linked spreadsheet and makes a new table in another Access Database, removing the link to Excel. Even though it is a new table, with no predefined field properties that I know of, I am getting an error message (sometimes) that is numeric field overflow. The first thing I did was remove some errors from Excel #N/A, which helped that field, but for the other fields I can't find any problem areas. Also, the query has no problem displaying the data in the datasheet view section, but when it comes time to perform the make table query, that error is coming up. I have not been able to isolate the error to a single field, because it seems like when I remove one field, I end up getting an error in another field. Any ideas? -- Sincerely, Beverly76 |
#4
|
|||
|
|||
Numeric Field Overflow from Excel File to new Table in Access
I also have encountered this error message and it did not always
happen. It was frustrating. I did not have any control over the creation of the spreadsheet. It was being automatically created from a mainframe so I had to take what I got, like it or not. What I ended up doing is: Using Automation, I opened the excell spreadsheet, went to row 2 and inserted a row, then I stepped across all of the cells and placed in them a "X" for alphabetic, a "0" for numeric and date() for a date field. then saved/closed the spreadsheet and performed the import. Then ran a query to delete the Xed record. Never had a problem after that. Messy and not the ideal, but it solved the problem. Ron |
#5
|
|||
|
|||
Numeric Field Overflow from Excel File to new Table in Access
I would not use a make table query unless absolutely necessary.
If at all possible I would create the target table and then append records to it. One method that I have used is to define the target table structure as a master table and then copy the master table. Then I populate the copy using an append query. You can use DoCmd.CopyObject to copy the master table to the second database and then use an append query instead of the make table query. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Beverly76 wrote: I have a linked Table in Access "02TBL" which is linked to an Excel spreadsheet. Then I have a make table query "02TBLmtq" which takes the entire linked spreadsheet and makes a new table in another Access Database, removing the link to Excel. Even though it is a new table, with no predefined field properties that I know of, I am getting an error message (sometimes) that is numeric field overflow. The first thing I did was remove some errors from Excel #N/A, which helped that field, but for the other fields I can't find any problem areas. Also, the query has no problem displaying the data in the datasheet view section, but when it comes time to perform the make table query, that error is coming up. I have not been able to isolate the error to a single field, because it seems like when I remove one field, I end up getting an error in another field. Any ideas? |
Thread Tools | |
Display Modes | |
|
|