Numeric Field Overflow linking Excel to Access

[cmills]

Hi,
I am getting this error message when I link an excel spreadsheet to Access table. I can run queries once and then when I run them again, I get this message. I go back into the spreadsheet and make small or no changes but save it again and then I can run Access queries one or two times then I get this error again. I have tried the suggestions on Microsoft. I am filtering query by using Between Date() And DateAdd ("M",1,"Date()) to get a report for date fields that are one month from today's date. This is what appears to be the problem. If I take that off I can run the query many times with no problems. If someone has a fix for this, that would be great. Or if someone knows how to report on Dates, that would be great too.
thanks
CMills

 

[Blorf]

This is a truely annoying problem that has plauged me for eons, and I can't find any real fix.

The only solution I have come up with is to link the spreadsheet as a table, and use a function to open the table as an open recordset, and read one record at a time, and write it to a local table.

At least this way, if one row in the sheet causes the problem, I still get all the others, unlike a query, where if there is a problem, you get nothing.

ChaZ

 

[cmills]

Thanks I'll try it. Or I am going to drop the Excel and load it all permanently into access. I have given up. It's very frustrating.

 

[Hap007]

Dear cmils,

I just struggled through with the same thing. It must be a bug with Access and linking to Excel.

In my case, I setup the link to an excel spreadsheet and had a query append all data into a table. Well, it works, then it does not. Very frustrating. I had a tough time putting my finger on the problem, but I needed to solve it.

Problem I need to have a link to the excel spread sheet for my client. And it always needed to be present.

So, I finally figured out a work around that works everytime.

The problem I had was that an append query from excel to access table would work once.
Second time, it failed.

Here is how I solved it.

1) I defined a link to the excel spread sheet.
2) I wrote SQL to select all records and create a temp table and add all data. Note: It still failed until I started changing the temp table name each time.
3) I then run the query which creates a temp table.
4) I wrote a second SQL Query to read all data from my newly created temp table and append to the final table
5) I deleted the temp table.

This works every time.
In my case, I always wanted new data so I used a delete all records that I ran between step 4 and step 5 that deleted all records in my final table.

I used a random number and date stamp as the temp table name to give me a new unique name each time.

It takes a little work to setup, but it will solve your problem with Excel and Access.

Good Luck,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121