vidyas
asked on
Opening an ADODB connection in exclusive mode
I need to open the database in exclusive mode so that I ensure consistent data.
I'm using an ADO connection to connect to the database.
if i give a con.mode=adModeShareExclus ive just before opening the connection, i get an error.
Are there any restrictions in using this mode?
any clues as to why this happens?
I'm using an ADO connection to connect to the database.
if i give a con.mode=adModeShareExclus
Are there any restrictions in using this mode?
any clues as to why this happens?
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
' Assign connection string to variable.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
' Open connection and titles table.
Set cnn1 = New ADODB.Connection
cnn1.Mode = adModeShareExclusive
cnn1.IsolationLevel = adXactIsolated
cnn1.Open strCnn
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
' Assign connection string to variable.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
' Open connection and titles table.
Set cnn1 = New ADODB.Connection
cnn1.Mode = adModeShareExclusive
cnn1.IsolationLevel = adXactIsolated
cnn1.Open strCnn
Deteige:
Very nice answer.. IF the questioner is using SQL as their server. As not all Provider's are created equal.. How do you know that SQL is the questioner's server of choice?.
As I find AngelIII's comment most germaine to the question, please change your answer to a comment.. until we know more.. <smile>.
Very nice answer.. IF the questioner is using SQL as their server. As not all Provider's are created equal.. How do you know that SQL is the questioner's server of choice?.
As I find AngelIII's comment most germaine to the question, please change your answer to a comment.. until we know more.. <smile>.
ASKER
The provider is the default: Microsoft OLE DB Provider
I want to query an Access database.
Code::
Public con As New ADODB.Connection '//The sole connection object
Public rs As New ADODB.Recordset
Public connectString As String
Public Function Initialize() As Boolean
'//This function initializes TracerInitVariables
'//when the application is invoked
'//for the first time
If Not TracerInit.IsDebugMode Then On Error GoTo errHandler
'//-connection set up
connectString = "DSN=Tracer"
con.Mode = adModeShareDenyWrite 'adModeShareExclusive
con.Open connectString
rs.CursorLocation = adUseClient
Initialize = True
Exit Function
errHandler:
Call TracerError.ShowErr(Err)
Initialize = False '//-signal init failure
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~
Error returned::
The flow jumps to the label errHandler and displays a message "Errors occured".
and the return value of the function is false.
I want to query an Access database.
Code::
Public con As New ADODB.Connection '//The sole connection object
Public rs As New ADODB.Recordset
Public connectString As String
Public Function Initialize() As Boolean
'//This function initializes TracerInitVariables
'//when the application is invoked
'//for the first time
If Not TracerInit.IsDebugMode Then On Error GoTo errHandler
'//-connection set up
connectString = "DSN=Tracer"
con.Mode = adModeShareDenyWrite 'adModeShareExclusive
con.Open connectString
rs.CursorLocation = adUseClient
Initialize = True
Exit Function
errHandler:
Call TracerError.ShowErr(Err)
Initialize = False '//-signal init failure
End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~
Error returned::
The flow jumps to the label errHandler and displays a message "Errors occured".
and the return value of the function is false.
detiege changed the proposed answer to a comment
Unfortunately, you didn't tell us which version of Access, but i got the same error for A97 and A2K, so i think (need to look up doc) that this mode is not supported by ODBC driver for Access.
In the options of the ODBC DSN however i detected in the advanced part some interesting checkboxes which may resolve your problem...
FYI:
I suggest that you don't declare your variables AS NEW, as this is a performance break.
Instead declare AS and issue you SET = NEW statements in the Initialize Sub
In the options of the ODBC DSN however i detected in the advanced part some interesting checkboxes which may resolve your problem...
FYI:
I suggest that you don't declare your variables AS NEW, as this is a performance break.
Instead declare AS and issue you SET = NEW statements in the Initialize Sub
ASKER
oops.. forgot to mention the version.
it is Access 97.
pardon me, I couldn't find any checkboxes. could you please guide me where to find these options?
reg your suggestion:: thanks for the info and I'm gonna remove the 'new' from the declaration as per your suggestion, but i'd still like to know how this could affect the performance??
it is Access 97.
pardon me, I couldn't find any checkboxes. could you please guide me where to find these options?
reg your suggestion:: thanks for the info and I'm gonna remove the 'new' from the declaration as per your suggestion, but i'd still like to know how this could affect the performance??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
guess what?? I don't get any kind of checkboxes.
What is the OS you are working on?
I'm on Win NT
What is the OS you are working on?
I'm on Win NT
NT 4 SP 5 Access 97 MDAC 2.1
ASKER
does this mean that i cannot open an access databse in exclusive mode?
if i can do this somehow, please let me know.
if i can do this somehow, please let me know.
ASKER
Thanks angelIII
I marked the database to be opened in Exclusive mode through control panel.
I marked the database to be opened in Exclusive mode through control panel.
What database do you want to query?
What Error do you get?
Please post of your code!