Import fails and .mdb file balloons up in size!

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

Guest

Every day I export data from a company application to an excel worksheet. I
run an excel macro that cleans up the data for import to an Access db on a
network drive (used by many). On an Access form in the db, I click a button
that truncates the data table and then uses a command to import the new data.
This works for me every single day using the same computer in my cubical.

I just discovered that this automated import does not work when performed on
a different computer (using the same Excel file and same Access db). I tried
manually importing the data to the existing (truncated) table and got the
same generic "import failed" message. Another interesting feature is that
the .mdb file balloons up in size from 30 MB to over 2 GB--even though the
data table is empty (since it was truncated and the import failed). Using
compact and repair brings the file back down to a normal size, but does not
solve the import problem.

I am able to import this same data to a "new" table with no errors. Access
decides that all my fields are text, but I can change the desired fields to
date/time and number. However this is a step backwards from having the
import automated.

I have tried to figure this out on my own and am stumped. The access db is
the same. The version of Access on each computer is the same (2003).
Nothing obvious is different between the successful import and the failed
import. I need to figure this out because I am going on vacation!

I appreciate any help you can give me,
Judy
 
I suppose you have checked that the SP level is the same on both?

Check the detailed version level. - I had a similar situation. No problems
on one machine, but on another, all the dates had their months and days
transposed, and this was solved by upgrading to SP3.

But beware, when you do go to SP3, the text and Excel import wizards no
longer work. There is a hotfix but it is virtually impossible to obtain here
in the third world (UK). The phone call is the most expensive outside of
'premium' numbers, and you just never get through to a person. You're on hold
at high rates till you get fed up.
 
Are both computers running the same OS?
Is Access on both the same version?
Also, check your VBA references to be sure they are the same.
 
I checked both the OS and Access versions on two machines. There is a
difference in detailed version level of Access 2003 SP1. My workstation is
on 11.6355.6360; the other system is on 11.6355.8028. But knowing that, is
there any way to get other systems to import the data in the same way that
mine does? I am guessing that it is erroring on the memo field that I am
trying to import (I'm about to test deleting that field from the import
data--but that still doesn't solve my problem).
 
Judy:
You might try first bringing them both up to SP3. This will get them to
identical levels (and the one which Microsoft may do something about). SP3 of
2003 works a damn site better than SP3 of 2002, and they won't even make the
fix to text import that they broke with SP3 a freely available download)

My logic and experience says that if it doesn't work on one machine, then
the problem has to be with the ACCESS version on that machine. After getting
both to SP3, I would also open up VBA on both machines and recompile the
application if the choice is not greyed out.

I haven't yet found anything that SP3 for 2003 has broken.

David
 
Thank you for your response. I will take your advice.

In the meantime, I discovered that it was a memo filed that I was importing.
My old version of Access had no problem with importing into an existing
table. The newer version expects one of the first 25 rows imported to have
that data type or the import just plain fails! If I look for a row that has
all the right data, move it up to row 2 in my Excel spreadsheet, the import
works as expected. So much for my existing automation!

Thank you,
Judy
 
Thank you for your response. As I kept digging for a solution I found that
it was a difference in the detailed version number of Access (and they way
they handle importing).
 
Back
Top