Solved

How to unlock the database Which is being accessed ?

Posted on 1998-07-16
5
134 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

810 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