VBA - Shellwait() on second instance of access

[Programmer - wannaB]

Please someone help me with this.

I was previously directed to Look at http://www.mvps.org/access for help to
cause access to wait for another program to finnish, and to look there for
the SHELLWAIT code.

I copied the code into a stand-alone module, and everything compiled fine.
I then modified my code to use the SHELLWAIT() sub AS SO
WITH THE NEXT LINE IN THE HEADER
Dim appAccess As Access.Application

Public Sub DuplicateDB_Click()

On Error GoTo Err_DuplicateDB_Click

Const lDbFileName = "D:\Dev\AdhocReporting.ldb"
Const adhocdb = "D:\Dev\AdhocReporting.mdb"

DoCmd.Hourglass True

If Len(Dir(lDbFileName)) > 0 Then
On Error Resume Next
Kill lDbFileName
If Err.Number = 0 Then 'delete was successful, file not in use
Call RefreshData 'CALL THIS PROCEDURE TO repopulate tables
Call CompactDb 'CALL PROCEDURE TO COMPACT UPDATED DB
End If
On Error GoTo Err_DuplicateDB_Click
Else
Call RefreshData 'CALL PROCEDURE TO repopulate tables
Call CompactDb 'CALL PROCEDURE TO COMPACT UPDATED DB
End If

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
ShellWait ("appAccess.OpenCurrentDatabase
""""D:\Dev\AdhocReporting.mdb""")

Exit_DuplicateDB_Click:
DoCmd.Hourglass False
Exit Sub

Err_DuplicateDB_Click:
MsgBox Err.Description
Resume Exit_DuplicateDB_Click
End Sub

OK SO, as I step through this is follows along just fine, however when it
SHELLs out to access it seems to be an empty access, there is no MDB file
openned. and the code continues through to the end. I can go to the DB that
this was called from and I can do that at any time during the running of the
code, i's like it shells out to a seconf instance of access but there is
nothing to prevent me from going back to the first instance while the second
remains open.
I will greatly appreciate any help, The SHELLWAIT code is far more then
anything I can understand, and you can find that at the link provided above.
THANK YOU...

 

[Dirk Goldgar]

In

Please someone help me with this.

I was previously directed to Look at http://www.mvps.org/access for
help to cause access to wait for another program to finnish, and to
look there for the SHELLWAIT code.

I copied the code into a stand-alone module, and everything compiled
fine.
I then modified my code to use the SHELLWAIT() sub AS SO
WITH THE NEXT LINE IN THE HEADER
Dim appAccess As Access.Application

Public Sub DuplicateDB_Click()

On Error GoTo Err_DuplicateDB_Click

Const lDbFileName = "D:\Dev\AdhocReporting.ldb"
Const adhocdb = "D:\Dev\AdhocReporting.mdb"

DoCmd.Hourglass True

If Len(Dir(lDbFileName)) > 0 Then
On Error Resume Next
Kill lDbFileName
If Err.Number = 0 Then 'delete was successful, file not in
use Call RefreshData 'CALL THIS PROCEDURE TO repopulate
tables Call CompactDb 'CALL PROCEDURE TO COMPACT
UPDATED DB End If
On Error GoTo Err_DuplicateDB_Click
Else
Call RefreshData 'CALL PROCEDURE TO repopulate tables
Call CompactDb 'CALL PROCEDURE TO COMPACT UPDATED DB
End If

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
ShellWait ("appAccess.OpenCurrentDatabase
""""D:\Dev\AdhocReporting.mdb""")

Exit_DuplicateDB_Click:
DoCmd.Hourglass False
Exit Sub

Err_DuplicateDB_Click:
MsgBox Err.Description
Resume Exit_DuplicateDB_Click
End Sub

OK SO, as I step through this is follows along just fine, however
when it SHELLs out to access it seems to be an empty access, there is
no MDB file openned. and the code continues through to the end. I
can go to the DB that this was called from and I can do that at any
time during the running of the code, i's like it shells out to a
seconf instance of access but there is nothing to prevent me from
going back to the first instance while the second remains open.
I will greatly appreciate any help, The SHELLWAIT code is far more
then anything I can understand, and you can find that at the link
provided above. THANK YOU...

Could I ask you to describe exactly what it is you're trying to
accomplish? What are the big picture and the larger goal here? From
the code snippet you posted, I don't think you're going about it the
right way -- certainly you can't shell to
"appAccess.OpenCurrentDatabase".

 

[WANNABE]

what do you mean I can't shell to "appAccess.OpenCurrentDatabase".
it does open an instance of access, and I believe I read that code in access
help?


In

Please someone help me with this.

I was previously directed to Look at http://www.mvps.org/access for
help to cause access to wait for another program to finnish, and to
look there for the SHELLWAIT code.

I copied the code into a stand-alone module, and everything compiled
fine.
I then modified my code to use the SHELLWAIT() sub AS SO
WITH THE NEXT LINE IN THE HEADER
Dim appAccess As Access.Application

Public Sub DuplicateDB_Click()

On Error GoTo Err_DuplicateDB_Click

Const lDbFileName = "D:\Dev\AdhocReporting.ldb"
Const adhocdb = "D:\Dev\AdhocReporting.mdb"

DoCmd.Hourglass True

If Len(Dir(lDbFileName)) > 0 Then
On Error Resume Next
Kill lDbFileName
If Err.Number = 0 Then 'delete was successful, file not in
use Call RefreshData 'CALL THIS PROCEDURE TO repopulate
tables Call CompactDb 'CALL PROCEDURE TO COMPACT
UPDATED DB End If
On Error GoTo Err_DuplicateDB_Click
Else
Call RefreshData 'CALL PROCEDURE TO repopulate tables
Call CompactDb 'CALL PROCEDURE TO COMPACT UPDATED DB
End If

Set appAccess = CreateObject("Access.Application")
appAccess.Visible = True
ShellWait ("appAccess.OpenCurrentDatabase
""""D:\Dev\AdhocReporting.mdb""")

Exit_DuplicateDB_Click:
DoCmd.Hourglass False
Exit Sub

Err_DuplicateDB_Click:
MsgBox Err.Description
Resume Exit_DuplicateDB_Click
End Sub

OK SO, as I step through this is follows along just fine, however
when it SHELLs out to access it seems to be an empty access, there is
no MDB file openned. and the code continues through to the end. I
can go to the DB that this was called from and I can do that at any
time during the running of the code, i's like it shells out to a
seconf instance of access but there is nothing to prevent me from
going back to the first instance while the second remains open.
I will greatly appreciate any help, The SHELLWAIT code is far more
then anything I can understand, and you can find that at the link
provided above. THANK YOU...

Could I ask you to describe exactly what it is you're trying to
accomplish? What are the big picture and the larger goal here? From
the code snippet you posted, I don't think you're going about it the
right way -- certainly you can't shell to
"appAccess.OpenCurrentDatabase".

 

[Dirk Goldgar]

In

what do you mean I can't shell to "appAccess.OpenCurrentDatabase".
it does open an instance of access, and I believe I read that code in
access help?

*This* code:

.... opened a second instance of Access and made it visible. If your
call to the ShellWait function did anything, I don't know what it would
be, because "appAccess.OpenCurrentDatabase" is not an executable command
line that would be recognized by the operating system.

 

[WANNABE]

I can not find the site where I got this.
What should the command be?
should I use the path and file name of the MDB?
====================================
In

what do you mean I can't shell to "appAccess.OpenCurrentDatabase".
it does open an instance of access, and I believe I read that code in
access help?

*This* code:

.... opened a second instance of Access and made it visible. If your
call to the ShellWait function did anything, I don't know what it would
be, because "appAccess.OpenCurrentDatabase" is not an executable command
line that would be recognized by the operating system.

 

[Dirk Goldgar]

In

I can not find the site where I got this.

You probably got the ShellWait code from
http://www.mvps.org/access/api/api0004.htm .

What should the command be?
should I use the path and file name of the MDB?

What is it you're trying to accomplish? What is the overall intent of
the code? You're already opening another instance of Access, and you
can manipulate that via automation without using ShellWait at all, so
maybe ShellWait is not what you need. But I can't say without knowing
what you're trying to do.

 

[Programmer - wannaB]

The procedure needs to open another database and require that the user close
that second data base before they can return to the application that it was
called from.

 

[Dirk Goldgar]

In

The procedure needs to open another database and require that the
user close that second data base before they can return to the
application that it was called from.

Now we're getting somewhere. You could do something like this, which
doesn't use the ShellWait function at all:

'----- start of code -----
On Error GoTo Cleanup

Dim appAccess As Access.Application

' Start up another instance of Access.
Set appAccess = New Access.Application

' In that Access application, open the Adhoc Reporting database.
appAccess.OpenCurrentDatabase "D:\Dev\AdhocReporting.mdb"

' Turn control of that application over to the user.
appAccess.Visible = True
appAccess.UserControl = True

' Minimize this Access application.
RunCommand acCmdAppMinimize

' Loop until the user closes the other application.
Do Until appAccess.UserControl = False
Loop

Cleanup:
On Error Resume Next

' Restore this Access application.
RunCommand acCmdAppRestore

appAccess.Quit
Set appAccess = Nothing

'----- end of code -----

Note, please, that the above code is only lightly tested, and I may have
overlooked some pitfall. I'm not thrilled with the loop, and I suppose
it would be possible to do it by getting a handle to the other Access
instance and doing an OS wait for it, but this does seem to work.

May I ask why this is necessary? Considering that the data in the other
database can be modified directly by the current database, and the other
database could be referenced as a library to extend the functionality in
the current one, it may not be necessary to open the second database at
all. Or you could have code in each database to start the other
database and close, so that the first database starts the second one and
closes, then when the second DB is done with its work it starts the
first one again.

 

[Programmer - wannaB]

Thanks - I will see what I can do with the code you've provided, I really
appreciate all the assistance.

As for the need, I am new to the company, they have an access database that
about 8 or 10 people use daily. This was built by a user who started
something small which grew over the 8 or 10 years that he worked on it. He
is no longer here. many of the users are used to going to the backend and
making changes to the tables, changing data directly in the tables, and
creating simple queries. I wish to take this away from them, and prevent any
access to the backend. BUT I need to do this carefully. The long range plan
is to move back end to SQL, I know the number of users is, according to what
I recently read just above the acess limit. For this reason I am not sure
that I want to split it. which would be another way to secure things....
Anyway there is a plan in place and the First step will be to secure the
MDB, which I have done and it is in test. But with that they insist on the
ability to run adhoc queries as it is a main part of their job. I thought of
providing a second MDB with links to the production tables, but I am not
comfortable with that being enough to prevent them from creating problems.
So I have created a process that will check to see that the adhoc DB is not
in use and when it's not in use a list of queries will run to delete and
repopulate the tables in the adhoc db, then open that db and allow users to
do what they want. OH DID I MENTION THE PRODUCTION DATBASE IS A MESS WITH
OBJECTS THAT SOMEONE STARTED AND LEFT UNFINISHED. In our last meeting I
explained that data would only be updated as long as there was no one else in
the adhoc db. yada yada yada, I now need to create this so they need to do
what is needed and get out of the adhoc db so it is not tied up all day, by
someone who left it open.
Was that too long ???

You idea of closing the production DB might be an option, but I need to run
it by all users, to understand better how they work.

 

[Dirk Goldgar]

In

Thanks - I will see what I can do with the code you've provided, I
really appreciate all the assistance.

As for the need, I am new to the company, they have an access
database that about 8 or 10 people use daily. This was built by a
user who started something small which grew over the 8 or 10 years
that he worked on it. He is no longer here. many of the users are
used to going to the backend and making changes to the tables,
changing data directly in the tables, and creating simple queries. I
wish to take this away from them, and prevent any access to the
backend. BUT I need to do this carefully. The long range plan is to
move back end to SQL, I know the number of users is, according to
what I recently read just above the acess limit. For this reason I
am not sure that I want to split it. which would be another way to
secure things.... Anyway there is a plan in place and the First step
will be to secure the MDB, which I have done and it is in test. But
with that they insist on the ability to run adhoc queries as it is a
main part of their job. I thought of providing a second MDB with
links to the production tables, but I am not comfortable with that
being enough to prevent them from creating problems. So I have
created a process that will check to see that the adhoc DB is not in
use and when it's not in use a list of queries will run to delete and
repopulate the tables in the adhoc db, then open that db and allow
users to do what they want. OH DID I MENTION THE PRODUCTION DATBASE
IS A MESS WITH OBJECTS THAT SOMEONE STARTED AND LEFT UNFINISHED. In
our last meeting I explained that data would only be updated as long
as there was no one else in the adhoc db. yada yada yada, I now need
to create this so they need to do what is needed and get out of the
adhoc db so it is not tied up all day, by someone who left it open.
Was that too long ???

Wow. It sounds like a real mess, with your biggest problem being one of
data access control. There could be a lot of ways to attack the various
problems, and I don't have a good enough understanding of it all to make
strong recommendations. My inclination would be to apply user-level
security to bring this under control, and to have an adhoc reporting
database that uses linked tables to get at the data in the production
database -- but only grant read-only access to those tables. If people
need to be able to modify data in "what-if" scenarios, then they should
do that in local tables that could be loaded by pulling from the
production tables.

No one except you should ever be allowed directly into the back-end, and
no one (except you) should be making changes directly in the tables.
That way lies total confusion and unreliable data. (Oh, wait, you said
you have that already.) Unfortunately, you're in a bad position because
people are accustomed to doing things this way, and you've got to
convince them that their best interests lie in stopping. So you have to
replace their current methods with new ways that they won't find more
cumbersome. Maybe you can sell them on the idea with forms that provide
a better user interface to help them do what they want.

It sounds like you're planning to replace a single, unsplit production
DB with an unsplit production DB and and unsplit adhoc DB. This strikes
me as only a small step in the right direction. Assuming there are
forms, reports, and modules in the production database that are part of
its production use, it should be split, and each authorized user should
have a copy of the front-end. It may be that the adhoc DB should be
similarly split, but that depends on whether it's more than just a
special front-end to the production database.

Regarding this:

The long range plan is to
move back end to SQL, I know the number of users is, according to
what I recently read just above the acess limit.

If, as you said before, only 8 or 10 people use the database daily, I
don't see how you are getting up to the limit. Bear in mind that Access
is only limited in the number of concurrent accesses. 8 or 10 users
wouldn't likely be causing you any problem, especially not if the
database is split.

Incidentally, if you're going to be moving the back-end to SQL Server
someday, you're certainly going to have built a proper front-end for it,
so there's no reason not to get started on it now, even if you have to
provide a kludgey intermediate solution.

 

[Programmer - wannaB]

I read somewhere that 10 should be the largest number of users attached to an
access database and that more then that would lead to data problems.

I was trying to avoid splitting the database, until converting to SQL. At
that time it will be split and security will be applied. There is no
security right nowit is all admin.

This first step also includes cleaning the MDB file out and importing only
the production objects into an access 2003 MDB. upgrade from 97.
The adhoc MDB will be a copy of the tables only.

I may need to rethink this whole process, but I will need a very complete,
solid plan before I can change the course of things now.
==================================

 

[Dirk Goldgar]

In

I read somewhere that 10 should be the largest number of users
attached to an access database and that more then that would lead to
data problems.

Not true. You can support 10 to 20 *times* that number, if they are
only reading data. If they are updating, you can still support more
than 10 concurrent users, though of course you have a greater chance of
running into possible record-locking issues, where two users are
attempting to edit the same record at the same time. But Access
normally handles that fairly well. And a lot depends on how many users
are actually trying to update the database at the same moment, which
depends on your usage patterns.

This first step also includes cleaning the MDB file out and importing
only the production objects into an access 2003 MDB. upgrade from 97.

Bear in mind that Access 97 tolerates the sharing of an unsplit database
on a network much better than the later version, including Access 2003,
do. That's because of the change in the way the database's VB project
is stored -- it's now one monolithic object, so saving any change to any
object containing VB code requires saving the whole project. This makes
Access 2003 more susceptible to corruption when shared over a network
than Access 97 was, since Access is constantly wanting to save objects
because the user has applied a sort or filter.

I don't recommend sharing an unsplit Access 2003 database on a network,
unless it's completely read-only for everyone.

I may need to rethink this whole process, but I will need a very
complete, solid plan before I can change the course of things now.

Agreed. Good luck.