Hi All,

I m using VB6 and DAO for developing my application. I wanted to open/close a database exclusively for my purpose.

I m newer to the DAO technology. can any one provide code for opening/closing database and recordset associated with database for exclusive access.

Thanks,
Surya

____________________________
surya satya

Hi Surya,
Since you do not specify the Database you are using I am assuming that it is Access, which uses the Jet Database Engine. You need to open a Jet Workspace Object, and then open a Database Object against it, then open the REcordset.
The Workspace object is easy :-

Dim wsp As Workspace Set wsp = DBEngine.Workspaces(0)

The syntax to open the database is:-
Set database = workspace.OpenDatabase (dbname, options, read-only, connect)
"dbname" is the full path to, and name, of the the database.
"Options" is where you set the exclusive parameter, "True" is Exclusive, "False" is shared. NOTE: This only works for Access, for ODBC Direct the options are different.
"ReadOnly" is self explanatory - True = ReadOnly - False = Read/Write
"connect" is for a connection string that can pass other parameters to the database, one example would be if the database is password protected you would set this part to ";pwd=MyPassword"
All of these parts, except for the database name,  are optional, which means that you can leave them all off, in which case the defaults are used for "options" and "readonly" - both of which are set to "False"
So, to open your Database with a password:-

Dim Db As Database Set Db = wsp.OpenDatabase("C:\My Documents\MyDatabase.mdb", True, False, ";pwd=MyPassword")

Or to just open it as exclusive:-

Dim Db As Database Set Db = wsp.OpenDatabase("C:\My Documents\MyDatabase.mdb", True)

Now you can use the "OpenRecordset" method to get your data. This method also takes 4 arguements, so the Syntax is:-
Set Recordset = Database.OpenRecordset (source, type, options, lockedits)
"source" can either be a SQL "SELECT" Statement, or the name of a table in the database.
"type" is the type of Recordset - which depends on what you want to do with the data. If you just need to display the information without making changes then "dbOpenSnapshot" would be the best type, but if you want to make changes then set it to "dbOpenDynaset" - this is the default when you open a recordset based on a Query and don't specify the type.
"options" and "lockedits" offers the ability to fine tune how you want the recordset to behave. To fully understand all these highlight "OpenRecordset" when you have typed it into your VB6 module, and hit "F1" for help.
So you now open your Recordset with:-

Dim strSQL As String Dim rst As Recordset strSQL = "SELECT Customers.* FROM Customers WHERE ((Customers.Is_Current")=True) ORDER BY Customers.CustomerName" Set rst = Db.OpenRecordset(strSQL, dbOpenSnapshot, , dbReadOnly)

Hope that helps.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hi GeoffS,

Thanks alot for your detailed reply. This helps me so much...

I wanted to add one more question to this topic i.e, after opening my database in exclusive mode no one can access it further without closing it. But, our customer wanted to access it in their code after I have closed my DB.

How to check whether that DB is still using? and Is there any way to put it in loop until database is closed?

I am using VB6, Access and DAO...

can any one provide me code for doing the same. This is urgent requirement from my cusotmer. Please help!!!

Thanks,
Surya.

____________________________
surya satya

Hi Surya,
If they attempt to connect whilst you have the Database open it will raise an error, so they could trap the Error and then re-start the code to try and connect again.

Sub ConnectDatabase() On Error GoTo errConnect 'Set a Label for the start of the connection attempt StartConnect: 'Code for connection Exit Sub errConnect: GoTo StartConnect End Sub

You may want to put a counter into this and exit completely when you get to a certain value, otherwise the code could be going round in circles for a very long time. Far better that the connect attempt just raises a MsgBox telling the user to try again later.

When you have finished using the database in your code make sure you dispose of all references to it, then it will be available to others. So finish with :-

rst.Close Db.Close Set Db = Nothing Set wsp = Nothing

[Edited by GeoffS on 05-02-2007 at 02:56 PM GMT]

[Edited by GeoffS on 05-02-2007 at 02:59 PM GMT]

____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Hi GeoffS,

Thanks for your detailed explanation. But, implementing the same in my project giving some problem.

I have opened my database in exclusive mode and then closing the same as you mentioned in the code snippet. I have double checked that no refrences exist to my database.

After that, I am launching our customer's EXE thru createprocess WIN API function and their EXE keep on trying to open my database in exclusive mode for one minute. But, it always throwing error like

Could not open Workspace or Database for CDR DB: Could not use 'C:\viewstore\pambati01_NBXCDR\application_and\cdr\\nbxcdr.mdb'; file already in use.

whats the problem here... can any one help...

Thanks,
Surya

____________________________
surya satya