Solved

How to unlock the database Which is being accessed ?

Posted on 1998-07-16
5
133 Views
Last Modified: 2010-05-03
My question is how can i unlock the database (data.mdb) which is being accesses by the my vb program.
The reason i want to do that is because i want to replace the current updated  database
with the original one (which is the backup .mdb) when the user press close button.

netcool
0
Comment
Question by:netcool
  • 3
5 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 1466057
You need to close the database and set the object to nothing I think

db.close
set db = nothing


0
 
LVL 5

Author Comment

by:netcool
ID: 1466058
deighton,

i tried but not working. Let me explain what i encouter and what ineed to be done.
I open the database and many recordset during my program running, so now i want to unlock/close  the database in order to delete the database and replace it with new one
(same name) .I can't delete the mdb file because it atatched with .ldb file unless i exist my program.
Another question, do i have to always set db = opendatabase("c:\data.mdb") whenever
i need to open recordset set tb = db.openrecordset("Employee") ????

Netcool

0
 
LVL 18

Expert Comment

by:deighton
ID: 1466059
The database needs to be open for you to open a recordset.  But you can open a database and then open and close as many recordsets as you like on that database, before finally closing the database.

You must always make sure that your databases and recordsets have been closed after use.  Avoid the following

function misuse() as integer

dim db as database
dim rs as recordset

set db = workspaces(0).opendatabase("x.mdb")
set rs = db.openrecordset("x",dbopendynaset)





end function

You always have to have rs.close then db.close at the bottom of the function

e.g

function ok() as integer

dim db as database
dim rs as recordset

set db = workspaces(0).opendatabase("x.mdb")
set rs = db.openrecordset("x",dbopendynaset)



rs.close
db.close



end function


You can  make the db a global or form level variable then do the following sort of thing

in declarations

dim db as database

the in form load or form initialse you open the database e.g. set db = workspaces..  etc

You then open and close recordsets in your form whenever you need them.  Finally you must do db.close in the form unload or  terminate event.

I think you might be leaving databases or recordsets open by accident.  See if this helps

0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1466060
Right.  And because of bugs in the MS code in various versions of VB and .dlls, it's always adviseable to set your objects = Nothing after closing them, in order to clean out all references to them in the OS.  Then you should be able to operate on your .mdbs as just another file, assuming nobody else in another app is using them.
0
 
LVL 18

Accepted Solution

by:
deighton earned 30 total points
ID: 1466061
You need to close the database and set the object to nothing I think

      db.close
      set db = nothing


      then open and close as many recordsets as you like on that database, before finally closing the
      database.

      You must always make sure that your databases and recordsets have been closed after
      use.  Avoid the following

      function misuse() as integer

      dim db as database
      dim rs as recordset

      set db = workspaces(0).opendatabase("x.mdb")
      set rs = db.openrecordset("x",dbopendynaset)





      end function

      You always have to have rs.close then db.close at the bottom of the function

      e.g

      function ok() as integer

      dim db as database
      dim rs as recordset

      set db = workspaces(0).opendatabase("x.mdb")
      set rs = db.openrecordset("x",dbopendynaset)



      rs.close
      db.close



      end function


      You can  make the db a global or form level variable then do the following sort of thing

      in declarations

      dim db as database

      the in form load or form initialse you open the database e.g. set db = workspaces..  etc

      You then open and close recordsets in your form whenever you need them.  Finally you must do
      db.close in the form unload or  terminate event.

      I think you might be leaving databases or recordsets open by accident.  See if this helps
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use Multiple Forms 4 49
Paint/Redraw window while dragging 16 68
VBA: Select SQL query based on a config Sheet v2 11 38
How to make an ADE file by code? 11 80
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now