Opening a new instance of Access (1 Viewer)

[doulostheou]

Does anyone know why the following code would crash Microsoft Access? In order to rule out issues with the databases themselves (the calling database adn the database being opened via code), I created two new databases both containing a single module and a single function. The code below is the function from the calling database. The database being opened just runs a one line function that returns true. Access still crashes.

I am running Windows Vista and Microsoft Access 2003.

    Dim appAccess As New Access.Application
    
    appAccess.OpenCurrentDatabase "C:\test.mdb"
    appAccess.Run "updateTables"
    appAccess.Quit

 

[LPurvis]

Upon which line does it crash? (Such things are always very important to know).
Sounds to me, like you'll be re-installing Office in your near future.

As a purely interim solution - you could test to see if a different implementation would work.
For example, depending upon where the crash occurs, there's a reaonsbale chance that the following would be more robust:

Dim appAccess As Access.Application

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\test.mdb"
appAccess.Run "updateTables"
appAccess.Quit

 

[doulostheou]

Upon which line does it crash? (Such things are always very important to know).

Sorry, I should have mentioned that. It crashes when I call at this line:

appAccess.OpenCurrentDatabase "C:\test.mdb"

Your code presents the same problem at the same line.

I did figure out an alternate method to do what I need to do after struggling with this for a few hours last night. I import the module from the database that I need to run and then use eval() to run the function from it that I need to run. As this code is simply updating databases with changes to the front end of the database and table structure, this is sufficient for my needs.

But it still drives me crazy that I can't open that second instance. It would seem that I may have a bad sector on my hard drive or something of that nature, as from everything I can see there is absolutely nothing wrong with the code itself and countless people do things similar in their databases.

 

[LPurvis]

Yes - with that line presenting the problem I'd expect it to fail just the same.
Have you re-installed Office (specifically Access)?

You say you've tested with various MDB files - and it always fails.
Without knowing any details of what has been executed in launched MDBs there, I'd imagine that this is an installation problem with Access as a COM server, given that, as you say, this code is very typical.

Out of interest - do you still get problems without the distinct launch?

Dim appAccess As Access.Application

Set appAccess = GetObject("C:\test.mdb", "Access.Application")
appAccess.Run "updateTables"
appAccess.Quit


Incidentally - do you absolutely have to launch a new Access instance?
This can't be performed at the data level?

Cheers.

 

[doulostheou]

Sorry for the delay in response. I got pulled away from this project and am just now getting back to it. I have what I call field databases running for users on their laptops. They do not connect to the office where the central database is stored very often, when they do it is to syncronize their data over a VPN connection. What I am trying to build is a way to streamline updates to these field database.

I have an update database that they can run which will compare their version against the current version and if they do not have the newest version, it will pull the new version down. This is all good and fine for query/form/report/module changes. However, if there is a change to the table structure that has to be handled much more carefully. For another client, I send them a database that runs code on autoexec which runs sql that updates their table structure appropriately (I test and retest these changes before I send them out). However, I don't like sending these files and having them sit out there as it could be very bad if some of them were ran multiple times. So I wanted to integrate it into the update database.

My original thought was to have the update database loop through the table that stores updates and if it finds updates to the table structure, it would download and open the specified database which would then update table defs. This was not working because Access kept crashing on me.

I found a workaround that I think will actually work better. The update database will import the module from the specified database, use eval() to run it (so I compile correctly) and then delete the module. Not only do I not need to open a separate instance of Access this gives me tighter control over how the code runs as before I was having to test to see if the ldb file was open before moving on. I had built in a timeout, but this always made me nervous as I have often witnessed the ldb file not being properly destroyed when the user closes a database.

So it sounds like a reinstall would be needed to fix the problem I was expereincing; but for now, I'm moving on with the import and eval(). Thanks for taking the time to look at this for me and verify that I wasn't insane and the code that I was writing SHOULD work.