Automate MSAccess from Windows .Net app

[Guest]

From a windows .Net app, I need to 1) check if MSAccess is already open to a
certain Access application. If so, then open a form and find a certain
record. 2) If not open, then open MSAccess and open a secured Access
application and then open a form and find a record. (The Access App is
secured not with a database password but with full user name and password
security.)

I can open the form and find the record OK. I am having problems checking if
the app is already open and opening the secured app.

Using "GetObject" has the problem of finding other instances of MSAccess but
not the database application I am interested in.

I used to use API calls to see if a window was already open with the name of
the MSAccess app but I can't get the API calls to work in .Net

I really would rather not use the shell() function since it has some
problems but I can't see any other way to automate a secured Access
Application.

Any ideas?

 

[Norman Yuan]

First of all, if possible, I'd avoid automate Access app: now that you are
writeing a stand-alone app, you can easily access data in that *.mdb file
amd manipulate the data (add/update/delete), why build an app, have the new
app start an existing app to process data. Maybe, you do not want to
re-write a fairly complicated process in that Access app. Anyway, if you
have do things this way, a solution I can think of, is to make sure that
Access app is only front-end app, the data part should be splitted as back
end. This way, your .NET app does not need to care if the Access front end
app is newly created instance or existing instance, or whether there are
multiple that Access app instances running, because all these app(s) work
toward the same data in the back-end and there is only one record in the
back end you need to find.

 

[Guest]

Norman,
Thanks for your reply. You make some good points. The best idea you gave me
was to just open another instance of the access application and not worry if
one already exists. The apps are split into front end / back end so that
should work fine. Great idea.

This is a mapping application that shows seven different utilities.
(Electric, water, sewer, stormwater, fiber and traffic.) Each utility has its
own inventory and maintenance database so it would take me years to duplicate
all of the functionality into my mapping front end.

So, I am back to opening a secured Access application from .Net I don't
think I can use:

Dim oAccess As Access.Application
' Start a new instance of Access for Automation:
oAccess = New Access.ApplicationClass()
' Open a database in exclusive mode:
oAccess.OpenCurrentDatabase(filepath:="c:\mydb.mdb", Exclusive:=True)

Since this won't open a secured application.

Will I have to use something like:

x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup
/user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34),
vbMinimizedFocus)

On Error GoTo WAITFORACCESS
Set accObj = GetObject(, "Access.Application")

Are these my only choices?

Thanks much!

First of all, if possible, I'd avoid automate Access app: now that you are
writeing a stand-alone app, you can easily access data in that *.mdb file
amd manipulate the data (add/update/delete), why build an app, have the new
app start an existing app to process data. Maybe, you do not want to
re-write a fairly complicated process in that Access app. Anyway, if you
have do things this way, a solution I can think of, is to make sure that
Access app is only front-end app, the data part should be splitted as back
end. This way, your .NET app does not need to care if the Access front end
app is newly created instance or existing instance, or whether there are
multiple that Access app instances running, because all these app(s) work
toward the same data in the back-end and there is only one record in the
back end you need to find.

 

[Paul Clement]

¤ Norman,
¤ Thanks for your reply. You make some good points. The best idea you gave me
¤ was to just open another instance of the access application and not worry if
¤ one already exists. The apps are split into front end / back end so that
¤ should work fine. Great idea.
¤
¤ This is a mapping application that shows seven different utilities.
¤ (Electric, water, sewer, stormwater, fiber and traffic.) Each utility has its
¤ own inventory and maintenance database so it would take me years to duplicate
¤ all of the functionality into my mapping front end.
¤
¤ So, I am back to opening a secured Access application from .Net I don't
¤ think I can use:
¤
¤ Dim oAccess As Access.Application
¤ ' Start a new instance of Access for Automation:
¤ oAccess = New Access.ApplicationClass()
¤ ' Open a database in exclusive mode:
¤ oAccess.OpenCurrentDatabase(filepath:="c:\mydb.mdb", Exclusive:=True)
¤
¤ Since this won't open a secured application.
¤
¤ Will I have to use something like:
¤
¤ x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup
¤ /user " & user & _
¤ " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34),
¤ vbMinimizedFocus)
¤
¤ On Error GoTo WAITFORACCESS
¤ Set accObj = GetObject(, "Access.Application")
¤
¤ Are these my only choices?

If you're implementing user-level security I'm afraid there isn't really another option when using
automation.


Paul
~~~~
Microsoft MVP (Visual Basic)