Solved: Access Exclusive Locking Issue

[Jacob Hilderbrand]

Followup to this thread:

http://www.vbaexpress.com/forum/showthread.php?t=12440

I am using this connection string:



Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
DBPath & ";Jet OLEDB:Database Password=" & DBPassword & ";"


Here is part of the connection code:



Dim RS As Object
Dim Conn As Object

Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = Connect
Conn.Open()

RS = CreateObject("ADODB.Recordset")
RS = Conn.Execute(StrSQL)

'
'
'
'
'

RS.Close()
Conn.Close()

RS = Nothing
Conn = Nothing


Now everything works fine, but occationnally I, or a user will get an error that the connection could not be opened because the database is opened exclusively by some other user.

I do not work with connection strings too much so I am probably just missing an option. The database should not be opened exclusively, in some cases it can be opened read only.

So my question is, how do I specify to open it read only, and how do I specify to open it in a normal mode (not exclusivily locked)?

Thanks

[Tommy]

Hi DRJ,

I think the biggest problem is you use createobject to create the recordset and the connection. IMHO this will lead to extra connections (implied) and recordsets without any control. In other words the objects get created but when you don't use the "suggested" method (OMG did I really say that?) the properties are not set the way you want them, it uses the defaults.

I picked this code up from here:
http://msdn2.microsoft.com/en-us/library/aa141412(office.10).aspx

Sub OpenReadOnlyRecordset(strDBPath As String, _
strSQL As String)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set cnn = New ADODB.Connection
' Open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With

Set rst = New ADODB.Recordset
With rst
' Open a forward-only, read-only Recordset object.
.Open Source:=strSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly

' Display the records in the Debug window.
Do While Not .EOF
For Each fld In .Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
.MoveNext
Loop

'Close the Recordset.
.Close
End With
' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub


I am going to post a sub I use for PostgreSQL. It is a ODBC way of doing it, and no I did not follow the "suggested" way. :devil2: It was developed with VB5, Word97, MDAC2.0 (I think, it's been a while). I am scared to death to change it, it has worked for a long time with no problems. Through multiple upgrades in OS's, databases, and programming enviroments. 60+- users, 200+- remote users.

FWIW:

Public Function ExecSql()
On Error GoTo CnEh
If Not Conn Then 'I check for an existing connection
If INHouse Then 'check if user user access or another type of database
cn.CursorDriver = rdUseOdbc
cn.Connect = "DSN=sched;DATABASE=" & Database & _
";SERVER=1.1.1.1;PORT=**;UID=user;PWD=password;" & _
"READONLY=0;PROTOCOL=database specific;FAKEOIDINDEX=1;" & _
"SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=1;CONNSETTINGS="
End If
cn.EstablishConnection
Conn = True ' a variable I use to determine if my connection is open and still valid
End If
cn.Execute SQL, rdExecDirect
cn.BeginTrans
cn.CommitTrans
On Error GoTo 0
Exit Function
CnEh:
Err.Clear
On Error GoTo 0
End Function

[Jacob Hilderbrand]

Ok, I think I had some of that, but lost it while trying to fix another problem.

Thanks