Trying not to open a database exclusively.

[Samibouni]

Hi

I have been trying to print ot a report in Acess from VB. I havew worked out the code to do this, but I have already opened the database up in my project & so I get an error about the database being opened exclusively already by another user.

This might be because in opening the database at the start of the program I have used

Dim bln1ExclusiveMode As Boolean
    Dim bln1ReadOnly As Boolean
'
'   Declare workspace and open database
'
    bln1ExclusiveMode = True
    bln1ReadOnly = False
    str3Database = App.Path & "\Sam.mdb"
    Set wsp3workspace = DBEngine.Workspaces(0)
    Set dba3Database = wsp3workspace.OpenDatabase(str3Database, bln1ExclusiveMode, bln1ReadOnly)

to cut down on any problems with many people opening up the database. The problem is that when I call the report I use the code:

Private Sub ReportBorrowed()
 
Dim AccessDB As Access.Application
Dim strFilter As String
Dim strWhere As String
 
 
    Set AccessDB = New Access.Application
    AccessDB.OpenCurrentDatabase App.Path & "\Sam.mdb"
    AccessDB.DoCmd.OpenReport "Borrowed", acViewNormal, strFilter, strWhere
    AccessDB.Visible = True
    AccessDB.Quit acQuitSaveAll
    Set AccessDB = Nothing

which causes the problem. I am trying to combat this but have drawn a blank with the methods I've used so far.Could anyone suggest how to:

a) open the database in a mode that isn't exclusive

or

b) get the repots to use the same connection to the database that I made on opening the program.

Thanks

Sam

[maged]

hi there,

try this

the error may be here

bln1ExclusiveMode = True

try replacing true by false


hope u can advise me if this work

[Samibouni]

That was the first thing that I tried that but it didnt make any difference to the problem.

I don't quite understand why that would not work, but it doesn't.

Is there anything else I'm missing?

[randem]

What you are missing ... If you opened the database exclusive already... Do not open attempt to open another connection to the database in your other routine. Use the same connection variable. So you must set the connection variable as a global so the rest of your program can use the same connection.

ie..

Global dba3Database as database


This should do the trick. Just reference the connection as you would normally do anywhere in your program.

[Samibouni]

Randem,,

Thanks for your response, you're making a lot of sense. Would you mind applying what you said about using the same connection variable for using the database for other purposes to the example I supplied. Namely, how would I code:

Private Sub ReportBorrowed()
 
Dim AccessDB As Access.Application
Dim strFilter As String
Dim strWhere As String
 
 
    Set AccessDB = New Access.Application
    AccessDB.OpenCurrentDatabase App.Path & "\Sam.mdb"
    AccessDB.DoCmd.OpenReport "Borrowed", acViewNormal, strFilter, strWhere
    AccessDB.Visible = True
    AccessDB.Quit acQuitSaveAll
    Set AccessDB = Nothing

Thanks for your help so far

[randem]

I re-wrote your code so that it works and you will have a better understanding on database access techniques.

You will need to put in so references for this to work, I imagine you have a few in already. DAO, Access, Script Control.

The Table 'Contacts' is just a substitution for the table that is in your database. So without opening another connection I can access the all the objects in the Access database through DAO. If you are going to use the dba3Database object in other places in your code you will want to make it a global in a module, this way you can use it everywhere in your program.

The SQL and the OpenRecordset statement are just there to show you how to access the tables from the database on the same connection.

Bad database techniques use many connection to a database. Let say that a database can only support 1000 connections. Each of your apps takes up five connections... Only 200 of your apps can access the database at anytime as opposed to 1000 of your apps if you use good database techniques.

Just a friendly thought!!! Always keep the end results in mind when writing an app.


Enjoy

Global dba3Database As Database
Global AccessDB As Access.Application
 
Private Sub Main()
Dim bln1ExclusiveMode As Boolean
Dim bln1ReadOnly As Boolean
Dim snpTable As Recordset
Dim SQL As String
 
'
'   Declare workspace and open database
'
    bln1ExclusiveMode = True
    bln1ReadOnly = False
    Set AccessDB = New Access.Application
    
    str3Database = "Sam.mdb"
    AccessDB.OpenCurrentDatabase App.Path & "\" & str3Database, bin1exclusivemode
    Set wsp3workspace = DBEngine.Workspaces(0)
    Set dba3Database = AccessDB.CurrentDb
    
    SQL = "Select * from Contacts"
    Set snpTable = dba3Database.OpenRecordset(SQL, dbOpenDynaset, dbReadOnly)
    
    AccessDB.DoCmd.OpenReport "Borrowed", acViewNormal, strFilter, strWhere
    AccessDB.Visible = True
    AccessDB.Quit acQuitSaveAll
    snpTable.Close
    Set snpTable = Nothing
    
End Sub
 
Private Sub Terminate() ' or what ever exit routine you will use
    dba3Database.Close
    Set dba3Database = Nothing
    AccessDB = Nothing
End Sub