?
Solved

Open Access from VB.Net

Posted on 2008-11-14
12
Medium Priority
?
491 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:jdhackett
  • 7
  • 5
12 Comments
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 22961446
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.
0
 
LVL 1

Author Comment

by:jdhackett
ID: 22977016
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
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 22977804
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
 
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 23

Expert Comment

by:Christopher Kile
ID: 22977820
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()).
0
 
LVL 1

Author Comment

by:jdhackett
ID: 22984046
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!
0
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 2000 total points
ID: 22985219
Dim oAccess as Access.Application
oAccess = GetObject("path to database")
 
If it's already open, you'll be given the Application object of the open instance; if not, a new Application object will be opened and a new Access instance will be created.
0
 
LVL 1

Author Comment

by:jdhackett
ID: 22985314
Heh, seems easy now! Thanks
One last thing - the focus doesn't switch this way. How would I switch the focus to Access?
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 22985778
I've looted this from another piece of code.
 
[DllImport("user32.dll")]
        public static extern bool SetForegroundWindow(IntPtr hWnd);
.
.
.
            SetForegroundWindow(oAccess.hWndAccessApp);
 

   
0
 
LVL 1

Author Comment

by:jdhackett
ID: 22985791
Thanks, you've really helped me out.
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 22985867
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

0
 
LVL 1

Author Comment

by:jdhackett
ID: 23005198
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.
0
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 23005412
LOL sorry about that....I remember reminding myself to include the return value, too....glad you figured it out on your own :)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question