Numeric Field Overflow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read the posts on the news groups but they do not cover the same sort
of problem i am having.

1. I have linked an Excel spreadsheet into Access, called tblTrackerABC
2. I have written a 'make table' query to transfer this the linked table to
a static table in the database called tblABC. The sql for this is SELECT
tblTrackerABC.* INTO tblABC FROM tblTrackerABC;
3. I have written a macro which runs this query.

The main reason for transferring into a static table and having a macro run
the query, is that i need to run this 30 times over for 30 different
spreadsheet, this will expand to 60 times in a month.

When i try to run the macro or each query individually i get the numeric
field overflow message. However if i compact and repair after each query run
it works fine, however this is nightmarishly slow, 30/60 make tables plus
30/60 compact and repairs.

I can't imagine is it is anything to do with field lengths etc as detailed
in other posts. If it were, 1. why would a compact and repair fix the
problem. 2. I am making a fresh table everytime based on teh linked table so
this cannot be it, can it?

Thanks for any help.
Emma
 
I still think automation is the way to go. Take a look at my article on this.
Walk through the code download associated with the article and cut & paste
into your app. If/when you hit conversion errors, you can handle them in code.

http://www.databasejournal.com/features/msaccess/article.php/3557541

I use this type of code to massage the data as it comes in, assigning defaults
where missing, testing dates to make sure they are valid, etc. You can even
give feedback to a user input form that says which record you're processing
and apply it toward a progress meter.

In the past, I rarely used TransferSpreadsheet and now that Microsoft has
removed some of the functionality, I'm glad I avoided it. Automation always
works and it gives you more power over the result.
 
Back
Top