Reserve Error from Excel Link

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

Guest

Background: Access 2002 w/ linked table to Excel 2002 file. Excel file is
password protected against editing - anyone can still read-only. I use the
linked table to update another table within the database using a delete query
and an update query via a macro.

Sometimes the macro fails with the error message "Reserve Error", but no
other information. After some trial and error, I figured out a worrk around:
open the Excel file, enter the password to allow changes (edits), save the
now open file, and exit again. Once I do this, I can then run the macro
successfully - at least for an hour or two.

I would blame it on the password protection, but I only get the error about
1/2 the time. No one else is in the Excel file when I run the macro
initially, but people are in and out of it the rest of the day.

The Excel file does contain a macro, but it is triggered by a button click
on the worksheet, not on opening.

Any ideas?
 
Is the workbook identified as sharable? Could be that the time you have the
error, someone else has it open. Run a test by having someone open the file,
then try to run your macro and see what happens. Also, how is your table
sharing set up in your mdb?
 
Hi, Jeff.
Sometimes the macro fails with the error message "Reserve Error", but no
other information.
Click to expand...

That's one of the downsides to using macros instead of VBA code: no error
handling. And if you weren't using such a stable operating system, Access
would crash and probably require a reboot whenever this unhandled error was
encountered.

My guess is that the data in the Excel file can't be used in the update
query or delete query, which subsequently fails. Perhaps this is from a file
lock or read-lock which your work-around fixes. We can only guess until you
convert the macro to VBA code with a proper error handler that lists the
error number and a description of the error. (And it might still be a
"Reserved Error" if the error you are receiving is so uncommon that Access
developers haven't yet gotten around to coding for identifying and recovering
from it -- and they might never get around to it.)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:
56
 
No, the workbook is not set to share. I'm the first one in the office - no
one else is here yet. Table sharing: the DB is saved on my machine - not the
network.
 
That's one of the downsides to using macros instead of VBA code: no error
handling.
Click to expand...

I will try to move it into VBA - but addmittedly, i'm a novice.
My guess is that the data in the Excel file can't be used in the update
query or delete query, which subsequently fails.
Click to expand...

The data does work. The error accurs about 1/2 the time (which means the
other half everything imports smoothly w/o my intervention).
 
Hi, Jeff.
I will try to move it into VBA - but addmittedly, i'm a novice.
Click to expand...

You can start learning by selecting the name of the macro in the Database
Window, then selecting the Tools -> Macro -> Convert Macro to Visual Basic
menu. A new module will be created, and you'll be able to see the
equivalent VBA commands for the macro's actions and arguments, and you'll
realize it's not so difficult after all. If you have a lot of macros to
convert, this could take like a minute -- maybe more. Just make sure that
you save the new modules and compile the code when finished converting.
The data does work. The error accurs about 1/2 the time (which means the
other half everything imports smoothly w/o my intervention).
Click to expand...

The error messages in the VBA error handlers should give you something to
troubleshoot whenever the task fails.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Back
Top