Error when using Access to edit Excel data

[Skeletal]

Hi all

I am putting together a quick and dirty db to hold just a few thousand bits of data for some tests I’m about to start.

For various reasons it is better to hold some of these in Excel, and to link Access to Excel.

The problem I have is that when I use Access to edit a “record” in Excel, it will work for the first one or two records, but then Access crashes and I get the very familiar “Sorry for the inconvenience, please send this error file to uSoft blah blah blah” (which I always do, but wonder if anyone ever takes any notice of them!).

The problem occurs whether the spreadsheet is open in the background, or is just sitting closed in its directory with Excel not running.

At the moment, I only have a tiny amount of data as I’m still putting it together, so it’s not a “size” issue; I first noticed the problem with only about ten records!

I expect I’ll move everything to Access (not ideal but hopefully it wont crash quite so often), but I’m curious to know if any of you have actually made a successful link between Access and Excel (and used it for editing data, not just looking, which is fine).

My system: Office 2003 pro/developer, Win XP pro.

Thanks for any thoughts

Skeletal

 

[ghudson]

I suggest that you import the data into Access. Create a rountine to import [transferspreadsheet method via VBA] the data on demand if the source data comes from an external file.

 

[Skeletal]

Yes that's a possibility. Interestingly, I have just been working on the db and thought I would try what I was doing yesterday. I edited about 10 records and it still hasn't crashed!!!

Given that nothing has changed since yesterday (that I know about), perhaps I should also say this problem occurs on Mondays, but not Tuesdays!!

I would still be interested to hear if this has happened to anyone else.

Skeletal

 

[ghudson]

Have you tried importing all of your db objects into a NEW access db?

 

[Skeletal]

In short no; but all I did before I got the error was: create simple spreadsheet (about 6 cols, 10 rows); open Access/create new db; link to spreadsheet; create simple form based on linked spreadsheet; start to edit spreadsheet data in form...crash. So I can't get much more basic than that!

Skeletal