ADO & Readonly MDB/MDE

[Calvin]

Ok, I've got a new one, I recently created a new MDE app and to help prevent it from becoming bloated and corrupted I made the MDE file ReadOnly (I've done this dozens of times), but all of a sudden I'm having ADO problems when the file is set to ReadOnly, but as soon as I disable the ReadOnly file property it runs fine.

    Dim rst As New ADODB.Recordset
    rst.Open "tblPrmDAC_ServiceLog", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
    rst.AddNew

Anyone have any suggestions or hear of this before?

fyi, the table is linked to a BE mdb that is not readonly, so that is not the problem as this works with DAO.

 

[a.sinatra]

why does it have to be Read Only? It's a MDE, so it is esentially read only because you cant change anything. So i dont see a point really.
________
RHODE ISLAND DISPENSARY

 

[pono1]

Calvin,

You may want to use another type of lock or change your connection object:

    Dim rst As New ADODB.Recordset
    rst.Open "tblPrmDAC_ServiceLog", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    rst.AddNew

adLockBatchOptimistic -- the lock in your code -- works only with client-side cursors. But the CurrentProject.Connection object defaults to a server side cursor.

Regards,
Tim

 

[Calvin]

still stumpted

Tim (pono1),
Good eye, but I've cycled through all of the locktypes and still no solution, it's weird, it works fine if I disable the files readonly property, but errors out when readonly is set.

Runtime error:3251
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

a.sinatra,
A compiled MDE is not the same as Readonly, it just prevents the objects and controls from being modified, but you can still insert new data into a table while the system tables and the file it self are still suseptible to bloating and corruption. Setting the MDE to readonly just helps keep it healthy.

I'd have to speculate that ADO recordsets are storing/changing something in the MDB/MDE file and setting the file to Readonly prevents this and forces the recordset into a Readonly condition.

Still looking for a solution or at least some understanding on the matter, if anyone knows anything (moderators?) please jump in. In the meantime I'll just have to convert the code back to DAO (because I know it works).

 

[pono1]

Calvin,

Have you tried changing your connection object as a workaround?

'Roughing it here...

'Create object var.
    Dim CnnDB As New ADODB.Connection

'Build connection string.
    Dim strConnect As String

    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source='" & MyAppPath & "'"
    
'Open the connection using the connection string.
    CnnDB.Open strConnect

'And onward...
    Dim rst As New ADODB.Recordset
     rst.Open "tblPrmDAC_ServiceLog", CnnDB.. etc"

There's a ado.chm help file some place on support.microsoft.com... Not sure if that will tell you what's happening behind the scenes with ADO connections though.

Regards,
Tim