Automation - running code in one Access DB from another Access DB

[Phred Bear]

I want to run some code in a db from another DB. The code seems to run OK
but I am left with an invisible instance of Access in my Windows Task
Manager. I don't seem to be tidying up when I release my object. I am then
left with a ***.ldb file which I have to delete manualy after I have closed
the instance of Access.in the Task Manager.

When I started, it worked fine but I have done something which stops it from
closing and I can't figure out what it is.

Here is a representative routine which causes the problem.

Private Sub RunAuto()
Dim RemoteDB As Object

Set RemoteDB = CreateObject("Access.application")

Set RemoteDB = GetObject("d:\Investigation.mdb")

RemoteDB.Run EmptyTables ' EmptyTables is the name of a procedure in the
target DB. It works fine in it's own DB


Set RemoteDB = Nothing

Application.Quit


End Sub

 

[Brendan Reynolds]

Set RemoteDB = CreateObject("Access.application")

Set RemoteDB = GetObject("d:\Investigation.mdb")

You now have no reference to the Application object that was created in the
first line above - the variable 'RemoteDB' no longer points to that object.

Just comment out or delete the first line above - the code never does
anything with that Application object anyway.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dave Patrick]

Maybe something like this.

Const strConPathToDB = "d:\Investigation.mdb"
Dim appAccess As Object
Set appAccess = CreateObject("Access.Application." & AccessVersion & "")
appAccess.OpenCurrentDatabase strConPathToDB
appAccess.Visible = False
appAccess.Run EmptyTables
appAccess.CloseCurrentDatabase
appAccess.Quit

Public Function AccessVersion()
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
Select Case WshShell.RegRead("HKCR\Access.Application\CurVer\")
Case "Access.Application.8"
AccessVersion = 8
Case "Access.Application.9"
AccessVersion = 9
Case "Access.Application.10"
AccessVersion = 10
Case "Access.Application.11"
AccessVersion = 11
End Select
End Function

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I want to run some code in a db from another DB. The code seems to run OK
| but I am left with an invisible instance of Access in my Windows Task
| Manager. I don't seem to be tidying up when I release my object. I am then
| left with a ***.ldb file which I have to delete manualy after I have
closed
| the instance of Access.in the Task Manager.
|
| When I started, it worked fine but I have done something which stops it
from
| closing and I can't figure out what it is.
|
| Here is a representative routine which causes the problem.
|
| Private Sub RunAuto()
| Dim RemoteDB As Object
|
| Set RemoteDB = CreateObject("Access.application")
|
| Set RemoteDB = GetObject("d:\Investigation.mdb")
|
| RemoteDB.Run EmptyTables ' EmptyTables is the name of a procedure in the
| target DB. It works fine in it's own DB
|
|
| Set RemoteDB = Nothing
|
| Application.Quit
|
|
| End Sub
|
|

 

[Phred Bear]

Thanks Dave but I can't do what I want to do using dao to open the database
because the object of my little routine is to empty the tables, compact the
database and replenish the tables from another DB - the back end of my main
application. I am doing it this cack handed way because you can't compact an
open db using code in Access 2000. I think you could in Access 97 and I
believe you can in Access 2003 but I'm stuck with Access 2000. I had already
done something similar to what you suggest which wouldn't work when I ran
the compact procedure.

doing it this way, here is the code I use:

Sub CompactTables()
On Error GoTo Err_CompactTables

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

Exit_CompactTables:
Exit Sub

Err_CompactTables:
MsgBox Err.Description
Resume Exit_CompactTables

End Sub

This was going to be my work around, except it won't work.

Thanks anyway.

Maybe something like this.

Const strConPathToDB = "d:\Investigation.mdb"
Dim appAccess As Object
Set appAccess = CreateObject("Access.Application." & AccessVersion &
"")
appAccess.OpenCurrentDatabase strConPathToDB
appAccess.Visible = False
appAccess.Run EmptyTables
appAccess.CloseCurrentDatabase
appAccess.Quit

Public Function AccessVersion()
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
Select Case WshShell.RegRead("HKCR\Access.Application\CurVer\")
Case "Access.Application.8"
AccessVersion = 8
Case "Access.Application.9"
AccessVersion = 9
Case "Access.Application.10"
AccessVersion = 10
Case "Access.Application.11"
AccessVersion = 11
End Select
End Function

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I want to run some code in a db from another DB. The code seems to run OK
| but I am left with an invisible instance of Access in my Windows Task
| Manager. I don't seem to be tidying up when I release my object. I am
then
| left with a ***.ldb file which I have to delete manualy after I have
closed
| the instance of Access.in the Task Manager.
|
| When I started, it worked fine but I have done something which stops it
from
| closing and I can't figure out what it is.
|
| Here is a representative routine which causes the problem.
|
| Private Sub RunAuto()
| Dim RemoteDB As Object
|
| Set RemoteDB = CreateObject("Access.application")
|
| Set RemoteDB = GetObject("d:\Investigation.mdb")
|
| RemoteDB.Run EmptyTables ' EmptyTables is the name of a procedure in the
| target DB. It works fine in it's own DB
|
|
| Set RemoteDB = Nothing
|
| Application.Quit
|
|
| End Sub
|
|

 

[Phred Bear]

Thanks to both of you. You started me of on a train of thought. I have made
a little routine to cycle through and close any open instances of Access
after the code completes and all is now well.

 

[Dave Patrick]

OK, I didn't see any mention of compacting. I think I have a VB app at work
I created to do this.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks Dave but I can't do what I want to do using dao to open the
database
| because the object of my little routine is to empty the tables, compact
the
| database and replenish the tables from another DB - the back end of my
main
| application. I am doing it this cack handed way because you can't compact
an
| open db using code in Access 2000. I think you could in Access 97 and I
| believe you can in Access 2003 but I'm stuck with Access 2000. I had
already
| done something similar to what you suggest which wouldn't work when I ran
| the compact procedure.
|
| doing it this way, here is the code I use:
|
| Sub CompactTables()
| On Error GoTo Err_CompactTables
|
| CommandBars("Menu Bar"). _
| Controls("Tools"). _
| Controls("Database utilities"). _
| Controls("Compact and repair database..."). _
| accDoDefaultAction
|
| Exit_CompactTables:
| Exit Sub
|
| Err_CompactTables:
| MsgBox Err.Description
| Resume Exit_CompactTables
|
| End Sub
|
| This was going to be my work around, except it won't work.
|
| Thanks anyway.

 

[Phred Bear]

OK, I didn't see any mention of compacting. I think I have a VB app at work
I created to do this.>>


I would be grateful if you would make it available.

Many thanks,

Ian Millward
Edinburgh

 

[Dave Patrick]

OK I'll see if I can find it tomorrow.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| I would be grateful if you would make it available.
|
| Many thanks,
|
| Ian Millward
| Edinburgh
|
|