Access closes when code is invoked

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

Guest

I have created a database with a number of tables and forms. I also wrote
some vba code to automatically import data into the various tables.
Basically Excel VBA code gathers the particular data and saves it in a
spreadsheet. It then calls some Access code to import the data into a
particular table. Everything was working well, I would run the excel code
once a day to gather the information and import it into the Access db. I
made one change to have a switchboard type form open automatically when the
database opened. I didn't think it through, because when the Excel code
tried to execute the Access code, the whole thing hung up. The only way I
was able to get out of it was to kill the specific MS Access processes. Now
I can open the database and look at the tables, but when I do anything that
touches the Access code, Access dies. This includes even scrolling through
the code with the editor or trying to printout the code. Any thoughts?
 
Hi.

It sounds like you have a damaged database file. First, make a backup of
the file. Next, try compact/repair. When the database reopens, open the VB
Editor by pressing <ALT><F11>. Select the Debug -> Compile <DatabaseName>
menu.

Does this help? If not, then close the database file. Create a new Access
database and import all objects from the original database, one group at a
time (tables, then queries, then forms, et cetera). Compile the code, then
compact/repair.

Does this help? If not, then open the database with the /decompile
command-line option, then compile the code and compact/repair. For example,
you could create a shortcut with the following syntax to open the database in
decompile mode (watch out for word wrap, as this is all one line):

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Data\MyDB.mdb"
/decompile

Does this help? If not, then you may need to export the modules from the
original database, or use SaveAsText, or copy/paste the text in each module
from the original into the new database. If you need help with any of these
methods, please post back, and we'll try to help.

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.
 
Thanks, I have tried a few of those things, but this gives me a much more
organized approach. I will give your suggestions a try and let you know the
results.
 
It then calls some Access code to import the data into a
particular table.
Click to expand...

Why? It's much easier to use the Excel routine to write directly to the
..mdb without involving Access at all. OLE automation has its uses, but only
as a last resort.

Just a thought...

Tim F
 
Thanks for the help Camaro. I was able to import everything into a clean DB
except my VBA code. I recovered that by going to the editor, selecting a
particular function from the menu and then getting a screen snap before
Access died. It was a surreal experience.

As for why use Access, I stumbled my way through this experimenting and
using whatever samples and help I could find and that's what I ended up with.
Could I have done it better/smarter/cleaner? Undoubtably.....
 
As for why use Access, I stumbled my way through this experimenting
and using whatever samples and help I could find and that's what I
ended up with.
Could I have done it better/smarter/cleaner? Undoubtably.....
Click to expand...

The one and only thing that prevents me dumping windows and Office
completely is VBA: Once you have learned VBA scripting in Access, you
already know how to script in Excel, Word, CorelDraw!, powerpoint etc etc
etc. That is it, total interoperability. If only there were such a thing
as embedded PHP in Open Office... but there ain't and there never will
be. And of course the universal availabilty of Jet/.mdb files makes any
kind of complex data storage a breeze.

Now that you know how to read a recordset and create a query in Access,
you can do exactly the same thing in Excel, using the same code. Once you
have a handle on the objects and UI available there there is nothing left
to learn. That's why I suggested doing all the code within the same
application.

All the best


Tim F
 
Back
Top