Question Does MS Access 97 supports record level locking?

[Nishidh]

Hi,

I have MS Access 97 database. I need to use ADO to access database in VB 6.0 application through JET OLEDB 4.0.

I want to know that whether MS Access 97 supports the Record level locking for above specification.

If it supports, please let me know how to do it?

Thank you very much in Advance.

Thanks,
Nishidh

 

[Banana]

Pretty sure it doesn't; only page locking is supported.

 

[Nishidh]

Hi

Thanks for the quick reply.

One MS Access 97 database is accessed by Front End and Data Entry applications. So in this case, how can we manage concurrent access to database at record level? so we can avoid unnecessary application crash?

Thanks,
Nishidh

 

[Banana]

I assume you've already split your database, then.

If you don't mind, describe a bit more about how your users use the application, and what exactly happens when there is a concurrent access?

 

[KeithG]

I dont think your going to be able to with Access 97. Can you migrate your db to SQL Server Express? This is a free version of SQL Server with a 3 gig limit. You are using .Net for your front end, correct?

 

[datAdrenaline]

... I want to clarify something ... Are you USING Access 97 to work with the data? ... or are you using VB6 to interface with an MDB file created by Access 97. If its the latter, then Access 97 has nothing to do with it, your limitations are imposed by the JET 3.5 database engine ... Which makes me ask, why are use using the JET 3.5 engine, when you should be able to use JET 4.0? ... JET 4.0 ships with VB6, I beleive.

 

[datAdrenaline]

I forgot to say this in my first post ... but ...

JET 3.5 only allows locks at the page level; JET 4.0 saw the implementation of a record level locking. But, I ask, why would a locked record crash the application? ... If that is the case, then even with Record Level locking you will run into a problem because invariably, someone will open a record that is locked by another.

 

[Banana]

Hi Keith,
Thanks for the reply.
I need to use Access 97 for the client requirement. I am using Visual Basic 6.0 for front End - GUI.

Just to make sure we don't muddle the water, I'd repeat Brent's question-

Are your clients actually using Access 97's GUI? Or do they do all works through VB6 GUI?

If you said no to former question, then you don't actually need Access 97, as Brent explained and can move on to Jet 4.0

 

[Nishidh]

I dont think your going to be able to with Access 97. Can you migrate your db to SQL Server Express? This is a free version of SQL Server with a 3 gig limit. You are using .Net for your front end, correct?

Hi Keith,
Thanks for the reply.
I need to use Access 97 for the client requirement. I am using Visual Basic 6.0 for front End - GUI.

 

[Nishidh]

... I want to clarify something ... Are you USING Access 97 to work with the data? ... or are you using VB6 to interface with an MDB file created by Access 97. If its the latter, then Access 97 has nothing to do with it, your limitations are imposed by the JET 3.5 database engine ... Which makes me ask, why are use using the JET 3.5 engine, when you should be able to use JET 4.0? ... JET 4.0 ships with VB6, I beleive.

Hi datAdrenaline,

Thanks for the reply.

I am USING VB6 to interface with an MDB file created by Access 97 through JET OLEDB 4.0. I read one article in which its been mentioned that Jet OLEDB 4.0 is compatible with Access 97.

Thanks for the reply.
Nishidh

 

[Nishidh]

I assume you've already split your database, then.

If you don't mind, describe a bit more about how your users use the application, and what exactly happens when there is a concurrent access?

Hi banana,

Thanks for the reply.

My Access 97 database is shared by 2 application. Front end App and Data Entry App.

Front End appl users use the database to view the data.

Data Entry appl users enters the new data, change the data and delete the data from shared access 97 database.

In update statement in Data Entry appl, it gives error like "Coult not update; currently locked by user 'Admin' on machine 'machinename'."

Thanks
Nishidh

 

[Nishidh]

... I want to clarify something ... Are you USING Access 97 to work with the data? ... or are you using VB6 to interface with an MDB file created by Access 97. If its the latter, then Access 97 has nothing to do with it, your limitations are imposed by the JET 3.5 database engine ... Which makes me ask, why are use using the JET 3.5 engine, when you should be able to use JET 4.0? ... JET 4.0 ships with VB6, I beleive.

Hi datAdrenaline ,

Thanks for the reply.

I am USING VB6 to interface with an MDB file created by Access 97 through JET OLEDB 4.0. I read one article in which its been mentioned that Jet OLEDB 4.0 is compatible with Access 97.

Thanks for the reply.
Nishidh

 

[Nishidh]

Just to make sure we don't muddle the water, I'd repeat Brent's question-

Are your clients actually using Access 97's GUI? Or do they do all works through VB6 GUI?

If you said no to former question, then you don't actually need Access 97, as Brent explained and can move on to Jet 4.0

Hi Banana,

I read one article in which it is written that Jet OLEDB 4.0 supports MS Access 97, is it true?

Thanks,
Nishidh

 

[Nishidh]

I forgot to say this in my first post ... but ...

JET 3.5 only allows locks at the page level; JET 4.0 saw the implementation of a record level locking. But, I ask, why would a locked record crash the application? ... If that is the case, then even with Record Level locking you will run into a problem because invariably, someone will open a record that is locked by another.

Hi datAdrenaline,

Once again thanks for reply.

Yes you are very true that if record is accessed by one user n access to the same record by another user will give error for concurrency, evenif we have record level locking enable.

I want to make sure in code that recordset is opened in recordlevel locking... that is the main question?
So want to make sure that whether the specification or my approach for record level locking is proper?

If I do not use record level locking, and one table is access by front end appl then data entry user can not do anything in that table.... evenif Front end appl user accessing different row/ record at that time.

So let me know, for VB6.0, MS Access 97 and using JET OLEDB 4.0... how to achieve Record Level Locking?

Thanks,
Nishidh

 

[datAdrenaline]

So ... Access 97 is really NOT part of this situation ... you are probably dealing with VB6.0 and JET 4.0 simply because that is what is provided by VB6, if A97 is installed on your machine, then you have JET 3.x on your system as well, but when manipulating the file through VB6 and JET OLEDB 4.0, I am fairly certain you are using the JET 4.0 installation on your machine to manipulate a lower formated MDB. Again, A97 is really not part of this whole thing. Although I will grant to you that A97 uses JET 3.x and thus will have the ability to interface with the file you have created.

So ... You make the statement:

>> MDB file created by Access 97 through JET OLEDB 4.0. <<

Then later ask if JET OLEDB 4.0 is compatible with A97 ??? ... confuses me as to how the file was created, so I will ASSUME you have created a JET 3.x file (the MDB file) with the JET OLEDB 4.0 provider through some VB6 code. I assume that you did this via the ADOX object library and the .Create method of a ADOX.Catalog object, with the provider specific property named "Jet OLE:Engine Type" set to a value of 4, thus indicating a JET 3.x format level for the MDB. If that is the case, then you can set the "Jet OLE:Engine Type" to a value of 5 in order to create an MDB file at the JET 4.0 format level, which is the prefered format. Since, your client has explicitly set the criteria for a JET 3.x level MDB, then I beleive you are stuck with page level locking ... but ... you may want to open your database with the provider specific property named "Jet OLEDB:database Locking Mode" set to value of 1 in your connection string, to indicate you want Row Level Locking. Maybe the JET OLEDB 4.0 Provider can pull a few strings, since in all likelyhood you are using the JET 4.0 engine to manipulate a JET 3.x file and acheive your request... but I'm doubtful. Please take note that the FIRST user to open the database sets the locking mode for all subsequent connections to the MDB file, regardless of this setting used to open the MDB.

 

[Nishidh]

So ... Access 97 is really NOT part of this situation ... you are probably dealing with VB6.0 and JET 4.0 simply because that is what is provided by VB6, if A97 is installed on your machine, then you have JET 3.x on your system as well, but when manipulating the file through VB6 and JET OLEDB 4.0, I am fairly certain you are using the JET 4.0 installation on your machine to manipulate a lower formated MDB. Again, A97 is really not part of this whole thing. Although I will grant to you that A97 uses JET 3.x and thus will have the ability to interface with the file you have created.

So ... You make the statement:

>> MDB file created by Access 97 through JET OLEDB 4.0. <<

Then later ask if JET OLEDB 4.0 is compatible with A97 ??? ... confuses me as to how the file was created, so I will ASSUME you have created a JET 3.x file (the MDB file) with the JET OLEDB 4.0 provider through some VB6 code. I assume that you did this via the ADOX object library and the .Create method of a ADOX.Catalog object, with the provider specific property named "Jet OLE:Engine Type" set to a value of 4, thus indicating a JET 3.x format level for the MDB. If that is the case, then you can set the "Jet OLE:Engine Type" to a value of 5 in order to create an MDB file at the JET 4.0 format level, which is the prefered format. Since, your client has explicitly set the criteria for a JET 3.x level MDB, then I beleive you are stuck with page level locking ... but ... you may want to open your database with the provider specific property named "Jet OLEDB:database Locking Mode" set to value of 1 in your connection string, to indicate you want Row Level Locking. Maybe the JET OLEDB 4.0 Provider can pull a few strings, since in all likelyhood you are using the JET 4.0 engine to manipulate a JET 3.x file and acheive your request... but I'm doubtful. Please take note that the FIRST user to open the database sets the locking mode for all subsequent connections to the MDB file, regardless of this setting used to open the MDB.

Hi datAdrenaline,

Thanks for the reply.

Yes, you are correct. Database is created in MS Access 97 in some other machine. I do have MS Access 2003 installed on my PC. I am not directly accessing databse through MS Access. I am accessing database through VB 6.0 and JET OLEDB 4.0... and yes Access 97 is not part of this process, the only thing is that database in created only in MS Access 97 version. Sorry about that you are very correct.

Now, you got it that i am merely accessing database from Vb 6.0 using ADO Object through JET OLEDB 4.0 only. And I did not use any method for creating tables thorugh code.

When I am accessing database, I already set the connection with "Jet OLEDB:database Locking Mode" = 1. But it was giving me page level locking. But When I did research on it.... I come to know that to support the Record Level Locking, Database Locking Mode = 1 is not the only property I need to set along with that I have to set the recordset property "rs.Properties("Jet OLEDB:Locking Granularity") = 2" in combination of "Database Locking Mode = 1" to achieve the Record Level Locking. Still it is giving me same error like "Could not update,; Currently locked by another User "Username" on machine "MachineName"."

Please

 

[datAdrenaline]

I guess it boils down to this ... JET 3.x does NOT allow record Level Locking, no matter what settings you try to apply. JET 4.0 saw the implementation of record level locking.

 

[Nishidh]

So ... Access 97 is really NOT part of this situation ... you are probably dealing with VB6.0 and JET 4.0 simply because that is what is provided by VB6, if A97 is installed on your machine, then you have JET 3.x on your system as well, but when manipulating the file through VB6 and JET OLEDB 4.0, I am fairly certain you are using the JET 4.0 installation on your machine to manipulate a lower formated MDB. Again, A97 is really not part of this whole thing. Although I will grant to you that A97 uses JET 3.x and thus will have the ability to interface with the file you have created.

So ... You make the statement:

>> MDB file created by Access 97 through JET OLEDB 4.0. <<

Then later ask if JET OLEDB 4.0 is compatible with A97 ??? ... confuses me as to how the file was created, so I will ASSUME you have created a JET 3.x file (the MDB file) with the JET OLEDB 4.0 provider through some VB6 code. I assume that you did this via the ADOX object library and the .Create method of a ADOX.Catalog object, with the provider specific property named "Jet OLE:Engine Type" set to a value of 4, thus indicating a JET 3.x format level for the MDB. If that is the case, then you can set the "Jet OLE:Engine Type" to a value of 5 in order to create an MDB file at the JET 4.0 format level, which is the prefered format. Since, your client has explicitly set the criteria for a JET 3.x level MDB, then I beleive you are stuck with page level locking ... but ... you may want to open your database with the provider specific property named "Jet OLEDB:database Locking Mode" set to value of 1 in your connection string, to indicate you want Row Level Locking. Maybe the JET OLEDB 4.0 Provider can pull a few strings, since in all likelyhood you are using the JET 4.0 engine to manipulate a JET 3.x file and acheive your request... but I'm doubtful. Please take note that the FIRST user to open the database sets the locking mode for all subsequent connections to the MDB file, regardless of this setting used to open the MDB.

Hi datAdrenaline,
Once again thanks for reply.

Yes, Access 97 is really NOT part of this situation ... I am dealing with VB6.0 and JET 4.0. Database is created only in Access 97. And access 97 is not installed on my PC.
I am using JET OLEDB 4.0 to access the database. Both my Front End and DataEntry application using the same setting for it. I am extremely Sorry to put you in confusion. While specifying the connection string, I have specified the "Jet OLEDB:database Locking Mode" set to value of 1. As well as, I read one article in that it is specified the to achieve record level locking you have to open the connection with above connection object property and set the recordset object property to "rs.Properties("Jet OLEDB:Locking Granularity") = 2" in combination with Connection object property that you mentioned. I am openning the connection in Front End Application and Data Entry application with Record level locking but still it gives the problem.....

Fragment of code for Recordset Object is as below :
rs.ActiveConnection = cnObject
rs.Properties("Jet OLEDB:Locking Granularity") = 2
rs.LockType = adLockPessimistic
rs.CursorType = adOpenKeyset
rs.Source = strSql
rs.Open

I used above code at both end ( Front End and Data Entry applications)
but still it gives the error like "Could not update; Currently locked by user "Admin" on machine "machinename".

Please give me solution for it. Your effort will be greatful to me.

Thanks in advance
Nishidh

 

[datAdrenaline]

>> I am dealing with VB6.0 and JET 4.0. Database is created only in Access 97 <<

You are not dealing with a JET 4.0 formatted data file ... you may be manipulating the data with the JET 4.0 engine, but the format is JET 3.x ... so you are still stuck will page level locking. If you convert the format to JET 4.0 format, then you have already mastered the code to get record level locking.

 

[Nishidh]

>> I am dealing with VB6.0 and JET 4.0. Database is created only in Access 97 <<

You are not dealing with a JET 4.0 formatted data file ... you may be manipulating the data with the JET 4.0 engine, but the format is JET 3.x ... so you are still stuck will page level locking. If you convert the format to JET 4.0 format, then you have already mastered the code to get record level locking.

So How to convert the format to JET 4.0 format?