Open existing Access database

  • Thread starter Thread starter SimeonD
  • Start date Start date
S

SimeonD

Hi
I have an access database called Sales.Mdb
In vb.net 2005, I'd like to open it. Which I can do.
What I can't figure out is how to figure out if Sales.Mdb is open already.
If so, I want to open that instance.
Any ideas how I would do this?
Thanks
 
Huh?

'open it'

Do you mean access the Jet database via the OleDbConnection class?
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(VS.71).aspx
Or something else?

................

My guess is that you're concerned about "is it already opened" because
you're getting concurrency issues.

I would investigate these methods:
ExecuteNonQuery
ExecuteReader
ExecuteScalar

With something as fragile as a Jet database, you need to learn how to "get
in, and get OUT...as quick as possible" for concurrency.

I do not know of a way to know the database is "opened" (<<I'm assuming you
mean by the PROGRAM : Microsoft Access ).

You can download a very basic example of using .ExecuteReader on a Jet
database here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry
The downloadable code is in C#.
 
Hi
I have an access database called Sales.Mdb
In vb.net 2005, I'd like to open it. Which I can do.
What I can't figure out is how to figure out if Sales.Mdb is open already..
If so, I want to open that instance.
Any ideas how I would do this?
Thanks
Click to expand...

I'm guessing you want to do one of two things, first you might mean
you want to open the file using Process.Start(...). If so, then I
don't know of a good way of seeing if it's already open, and I don't
think it matters. If the user wants multiple instances and Access will
let them, then you should too (in other words, it's not your problem).
If however you are opening Access as part of an automation task
through Process.Start(...), don't.

Secondly, if you are trying to interact with Access as part of an
automation routine, you should be doing so through ADO.NET. Doing this
will fix the problem of Access being opened and not closed, as ADO.NET
will not start Access. If you don't know how to do this, some
searching on Google should turn up some step-by-step instructions.

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/
 
I should have been clearer.
Below is the code I have already. This will open the form, in a new instance
of access.
The reason I want to check for existing Access, is to stop the same db being
opened multiple times.

Dim oAccess As Access.Application
Dim oForm As Access.Form
Dim stCriteria 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

oAccess.OpenCurrentDatabase(filepath:=Sales_Path, Exclusive:=False)
sForm = "frmSales"
oAccess.DoCmd.OpenForm(sForm, Access.AcFormView.acNormal)


Hi
I have an access database called Sales.Mdb
In vb.net 2005, I'd like to open it. Which I can do.
What I can't figure out is how to figure out if Sales.Mdb is open already.
If so, I want to open that instance.
Any ideas how I would do this?
Thanks
Click to expand...

I'm guessing you want to do one of two things, first you might mean
you want to open the file using Process.Start(...). If so, then I
don't know of a good way of seeing if it's already open, and I don't
think it matters. If the user wants multiple instances and Access will
let them, then you should too (in other words, it's not your problem).
If however you are opening Access as part of an automation task
through Process.Start(...), don't.

Secondly, if you are trying to interact with Access as part of an
automation routine, you should be doing so through ADO.NET. Doing this
will fix the problem of Access being opened and not closed, as ADO.NET
will not start Access. If you don't know how to do this, some
searching on Google should turn up some step-by-step instructions.

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/
 
If I can give you one suggestion with one part of your code:
your DIM sForm as string...

and then you set
sForm = "frmSales"

If you do this, and you change the name of your form (for whatever reason),
you will not get a compile time error, but a Runtime Error.
I suggest you do this:

DIM sForm As frmSales.Name ' ( Where frmSales im assuming is listed in
your project forms ).

That way if your form changes names, your code will still work,

that or call your function directly:
oAccess.DoCmd.OpenForm(frmSales.Name, Access.AcFormView.acNormal)

But if you want to use the string meathod - that is my little suggestion.

I do not understand the full issue with your access problem, but you are
opening up a can of worms I ran into that I fixed by doing the above change.
( I changed my form name, and other code stopped working without knowing
about it ).

Cheers'

Miro
 
Interesting point actually. It doesn't apply in this case, as frmSales is
actually in Access, so the name won't be available. I think.
 
I should have been clearer.
Below is the code I have already. This will open the form, in a new instance
of access.
The reason I want to check for existing Access, is to stop the same db being
opened multiple times.

      Dim oAccess As Access.Application
      Dim oForm As Access.Form
      Dim stCriteria 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

      oAccess.OpenCurrentDatabase(filepath:=Sales_Path, Exclusive:=False)
    sForm = "frmSales"
    oAccess.DoCmd.OpenForm(sForm, Access.AcFormView.acNormal)




I'm guessing you want to do one of two things, first you might mean
you want to open the file using Process.Start(...). If so, then I
don't know of a good way of seeing if it's already open, and I don't
think it matters. If the user wants multiple instances and Access will
let them, then you should too (in other words, it's not your problem).
If however you are opening Access as part of an automation task
through Process.Start(...), don't.

Secondly, if you are trying to interact with Access as part of an
automation routine, you should be doing so through ADO.NET. Doing this
will fix the problem of Access being opened and not closed, as ADO.NET
will not start Access. If you don't know how to do this, some
searching on Google should turn up some step-by-step instructions.

Thanks,

Seth Rowe [MVP]http://sethrowe.blogspot.com/
Click to expand...

Why isn't all that code inside Access? In my opinion doing Office
interop is a very dangerous thing, and should be avoided at all costs.
At this point I can't really tell what you're doing that needs .NET at
all. The only you would accomplish is a large code base, more
overhead, and additional system requirements.

Perhaps it would be better to explain what you're meaning to
accomplish, and then we could perhaps provide a better way?

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/
 
Why isn't all that code inside Access? In my opinion doing Office
interop is a very dangerous thing, and should be avoided at all costs.
At this point I can't really tell what you're doing that needs .NET at
all. The only you would accomplish is a large code base, more
overhead, and additional system requirements.

Perhaps it would be better to explain what you're meaning to
accomplish, and then we could perhaps provide a better way?

Thanks,

Seth Rowe [MVP]http://sethrowe.blogspot.com/
Click to expand...

That was one of my sights, too when i first read topic. You can use
Access DB using ADO.NET, OleDB and its classes instead of risky
dependecy-requiring interop steps. (Primary Interop Assemblies are
further concept.)

Look at here for usage with OleDB:
http://www.startvbdotnet.com/ado/msaccess.aspx

Look at here what your options are:
http://support.microsoft.com/kb/317113

HTH,

Onur Güzel
 
My first post alluded to all this, but I agree.

The faster you get OUT of the interop business, the better.

.............




Why isn't all that code inside Access? In my opinion doing Office
interop is a very dangerous thing, and should be avoided at all costs.
At this point I can't really tell what you're doing that needs .NET at
all. The only you would accomplish is a large code base, more
overhead, and additional system requirements.

Perhaps it would be better to explain what you're meaning to
accomplish, and then we could perhaps provide a better way?

Thanks,

Seth Rowe [MVP]http://sethrowe.blogspot.com/
Click to expand...

That was one of my sights, too when i first read topic. You can use
Access DB using ADO.NET, OleDB and its classes instead of risky
dependecy-requiring interop steps. (Primary Interop Assemblies are
further concept.)

Look at here for usage with OleDB:
http://www.startvbdotnet.com/ado/msaccess.aspx

Look at here what your options are:
http://support.microsoft.com/kb/317113

HTH,

Onur Güzel
 
Check for the existence of the corresponding .ldb file for that .mdb.
If the .ldb exists - then the .mdb is open.

Rich
 
Information about Sales are stored in an access database. However, the
SaleID is also stored in a third party accounts package.
This package allows one to call an external program with parameters.
Lets call my vb.net app "ViewAccessSales.exe". In the accounts package, I
can then call
ViewAccessSales SaleID
My .net program then:
1) Stores the SaleID parameter
2) Calls Access
3) Opens the form for the SaleID

The users want to be able to view the Sale as quickly as possible from the
accounts package - without having to launch Access and then search for the
sale.
If this can be done within Access, without interop, I'd be very pleased
indeed. However, I can't see how it would be possible.
Thanks for taking the time to give your suggestions.
 
Information about Sales are stored in an access database. However, the
SaleID is also stored in a third party accounts package.
This package allows one to call an external program with parameters.
Lets call my vb.net app "ViewAccessSales.exe". In the accounts package, I
can then call
ViewAccessSales SaleID
My .net program then:
1) Stores the SaleID parameter
2) Calls Access
3) Opens the form for the SaleID

The users want to be able to view the Sale as quickly as possible from the
accounts package -  without having to launch Access and then search forthe
sale.
If this can be done within Access, without interop, I'd be very pleased
indeed. However, I can't see how it would be possible.
Thanks for taking the time to give your suggestions.
Click to expand...

With that information, you have definitely shown a reason for the
interop. The way you are doing things is viable, but I would prefer to
cut out Access entirely. If it were me, I'd reproduce the Access form
and make it a true .NET application. Doing this should give you better
performance all around.

The issue here is having to reproduce the form. Depending on how it
was written and how complex it is, it could be a trivial task, or one
that's complex enough that there isn't enough ROI to make the rewrite
a good business decision.

Thanks,

Seth Rowe [MVP]
http://sethrowe.blogspot.com/
 
<snip>

There is no way there will be a rewrite, the form will remain in Access.
Now, back to the problem at hand:

I have two main issues, using the code in the given example from the
website:
1) It won't switch to the open Access db
2) If I use the runtime, the ShellGetDB or ShellGetApp won't switch focus to
Access. Even if its a new DB opened through code.

Please help - its driving me bananas!

Thanks
 
¤ Hi
¤ I have an access database called Sales.Mdb
¤ In vb.net 2005, I'd like to open it. Which I can do.
¤ What I can't figure out is how to figure out if Sales.Mdb is open already.
¤ If so, I want to open that instance.
¤ Any ideas how I would do this?

Open the database for exclusive access. If the database is already in use the open will fail. You
can trap the error in a Try...Catch block and display a message indicating that the database is
currently in use.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top