Link to home
Start Free TrialLog in
Avatar of jdhackett
jdhackettFlag for Ireland

asked on

Open Access from VB.Net

Using some code from the Microsoft website, I can now open the database, and view the form.
http://msdn.microsoft.com/en-us/library/aa167790(office.11).aspx
My modified code is below.
However, I still have some questions:

1) If the database is already open, I'd like to switch to it and open the Clients form there. How can I set oAccess to the open database?
2) Some of my users have the runtime version of Access instead. How do I use that instead?
3) I want the Access window to be maximised, but oAccess.DoCmd.Maximize() doesn't work.

Thanks!

Dim oAccess As Access.Application
Dim oForm As Access.Form
Dim stCriteria As String
Dim sDBPath As String
Dim sForm As String 'name of form to show
 
' Start a new instance of Access for automation:
oAccess = New Access.ApplicationClass()
 
' Make sure Access is visible:
If Not oAccess.Visible Then oAccess.Visible = True
 
sDBPath = DB_PATH
oAccess.OpenCurrentDatabase(filepath:=sDBPath, Exclusive:=False)
oAccess.DoCmd.Maximize()
 
' Now open the form for specified Client
sForm = "frmClientMain"
stCriteria = "Branch = " & iBranch & " AND ClientID = '" & stClientID & "'"
oAccess.DoCmd.OpenForm(sForm, Access.AcFormView.acNormal, , stCriteria)
 
' Release Application object and allow Access to be closed by user:
If Not oAccess.UserControl Then oAccess.UserControl = True
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
oAccess = Nothing

Open in new window

Avatar of Christopher Kile
Christopher Kile
Flag of United States of America image

I can answer question (2):  your code doesn't change - it's already using the Access Runtime.
On question (3), I'd recommend based on my readings that you remove the .Maximize command from your VB code and place it in the OnOpen event in each of the forms you plan to use.
I don't know how to verify my answer question (1), but I suspect the answer is no.
Avatar of jdhackett

ASKER

Thanks.
1) I was hoping AppActivate or somesuch would let me set oAccess to an already opened DB, but I can't figure out how.
2) This program won't run on a PC with access runtime. The error seems to happen around
oAccess = New Access.ApplicationClass()
 Actually, now that I think about it, maybe thats what the ShellGetApp function is for on the MS website. Will look at that.
AccessError.jpg
There is a support page for your runtime error (which is not peculiar to Access, as it happens):
http://support.microsoft.com/kb/870655
This article, on the other hand, uses GetObject() to do what you wanted to do in point 1:  if your database is already open, this will use the currently open instance (sorry, been a while otherwise I'd've thought of this one myself :) ):
http://support.microsoft.com/kb/317113
 
BTW, the second link in the previous message (http://support.microsoft.com/kb/317113) also describes how to use the Access runtime (again, using GetObject()).
OK, I have it using the runtime now. Though I had to change the group policy, as otherwise the standard message would pop up about unsafe expressions when Access was opened.
But ShellGetDB and ShellGetApp both seem to have the same effect - a new instance of Access is opened.
Maybe I'm missing something. Could you explain how I should use GetObject to open the existing instance please.
Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Christopher Kile
Christopher Kile
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Heh, seems easy now! Thanks
One last thing - the focus doesn't switch this way. How would I switch the focus to Access?
I've looted this from another piece of code.
 
[DllImport("user32.dll")]
        public static extern bool SetForegroundWindow(IntPtr hWnd);
.
.
.
            SetForegroundWindow(oAccess.hWndAccessApp);
 

   
Thanks, you've really helped me out.
Sorry, that won't work in VB.  Should be declared as shown in code snippet.
 

Declare Auto Function SetForegroundWindow Lib "user32.dll" Alias "SetForegroundWindow" (ByVal hWnd as Integer)

Open in new window

Actually, it should be

Declare Auto Function SetForegroundWindow Lib "user32.dll" Alias "SetForegroundWindow" (ByVal hWnd As Integer) As Long

Have to return a value from a function, otherwise get some variant error.
But thats fine, had it figured. But fair dues on coming back though.
LOL sorry about that....I remember reminding myself to include the return value, too....glad you figured it out on your own :)