CacheSize read-only for Recordset?

[Chris Burnette]

I am trying to open a persistent Recordset object and set the CacheSize
property using VBA. I do this in the Form_Open event, but every time I try
to do it I get the message "this property is read-only and can't be set."

Before I run this code, I have the user login via a form which prompts for a
username and password and then uses a DSN-less connection string to connect
to an MSDE back-end.

My code is below:

Option Compare Database

Public rst As Recordset
Public db As DAO.Database

Private Sub Form_Close()
rst.Close
Set rst = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * FROM EntireSpreadsheet",
dbOpenDynaset, dbSeeChanges)
rst.CacheSize = 30
'Me.Visible = False
End Sub

I am not sure why I am getting this error, and my main reason for wanting to
do this is to improve the performance of the database. If anyone has any
ideas as to why I might be getting this message, I'd love to hear them.

Thanks,

Chris

 

[Brendan Reynolds]

I copied and pasted your code, Chris, changing only the name of the table to
one that exists in my test database, and the code runs for me without error.
Possibly the problem may be related to the way you are connecting to the
database - could you post that code?

 

[Chris Burnette]

My code for connecting to the database looks like this:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Dim stConnect As String

Dim stUID As String
Dim stPWD As String
stUID = Me.txtUserName.Value
stPWD = Me.txtPassword.Value

stConnect = "ODBC;DRIVER={SQL Server}" _
& ";Trusted_Connection=no" _
& ";SERVER=OFFICE-DC" _
& ";Address=10.0.0.252,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"

Set tdf = db.CreateTableDef("LoginTable")
tdf.SourceTableName = "EntireSpreadsheet"
tdf.Connect = stConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh
db.TableDefs.Delete "LoginTable"

I tried getting rid of the public DAO.Database and just using
CurrentDb().OpenRecordset in my original code, but I get the same error.

Any ideas?

 

[Brendan Reynolds]

On the face of it, Chris, that code would appear to be creating the linked
table and immediately deleting it again - shouldn't the tdf.Delete statement
be near the start of the code, to delete the linked table if it already
exists, before creating it?

I tried a test with code as close to yours as possible, but I'm just not set
up to test it without using integrated security, so I had to make quite a
few changes, mostly to the connection string. I'm afraid I still can't
reproduce the problem. As a trouble-shooting step, it might be worth
creating a DSN and linking a table in the normal way using the DSN, and
testing to see if the CacheSize code still fails - the result might help to
narrow down the problem somewhat. I'm sorry I can't offer anything more
definite than that right now.

Here's my test code that executes without error ...

Public Sub TestCacheSize()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim stConnect As String
Dim rst As DAO.Recordset

Set db = CurrentDb()
db.TableDefs.Delete "dbo_Employees"
db.TableDefs.Refresh

stConnect = "ODBC;DRIVER={SQL Server}" _
& ";Trusted_Connection=Yes" _
& ";SERVER=(local)" _
& ";DATABASE=Northwind" & ";"

Set tdf = db.CreateTableDef("dbo_Employees")
tdf.SourceTableName = "Employees"
tdf.Connect = stConnect
db.TableDefs.Append tdf
db.TableDefs.Refresh

Set rst = db.OpenRecordset("dbo_Employees", dbOpenDynaset, dbSeeChanges)
rst.CacheSize = 5
rst.FillCache
Debug.Print rst.Fields(0).Name, rst.Fields(0).Value
rst.Close

End Sub

--
Brendan Reynolds

My code for connecting to the database looks like this:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Dim stConnect As String

Dim stUID As String
Dim stPWD As String
stUID = Me.txtUserName.Value
stPWD = Me.txtPassword.Value

stConnect = "ODBC;DRIVER={SQL Server}" _
& ";Trusted_Connection=no" _
& ";SERVER=OFFICE-DC" _
& ";Address=10.0.0.252,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"

Set tdf = db.CreateTableDef("LoginTable")
tdf.SourceTableName = "EntireSpreadsheet"
tdf.Connect = stConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh
db.TableDefs.Delete "LoginTable"

I tried getting rid of the public DAO.Database and just using
CurrentDb().OpenRecordset in my original code, but I get the same error.

Any ideas?

 

[Chris Burnette]

Brendan, I modified your code and it works.

Thanks,

Chris

On the face of it, Chris, that code would appear to be creating the linked
table and immediately deleting it again - shouldn't the tdf.Delete statement
be near the start of the code, to delete the linked table if it already
exists, before creating it?

I tried a test with code as close to yours as possible, but I'm just not set
up to test it without using integrated security, so I had to make quite a
few changes, mostly to the connection string. I'm afraid I still can't
reproduce the problem. As a trouble-shooting step, it might be worth
creating a DSN and linking a table in the normal way using the DSN, and
testing to see if the CacheSize code still fails - the result might help to
narrow down the problem somewhat. I'm sorry I can't offer anything more
definite than that right now.

Here's my test code that executes without error ...

Public Sub TestCacheSize()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim stConnect As String
Dim rst As DAO.Recordset

Set db = CurrentDb()
db.TableDefs.Delete "dbo_Employees"
db.TableDefs.Refresh

stConnect = "ODBC;DRIVER={SQL Server}" _
& ";Trusted_Connection=Yes" _
& ";SERVER=(local)" _
& ";DATABASE=Northwind" & ";"

Set tdf = db.CreateTableDef("dbo_Employees")
tdf.SourceTableName = "Employees"
tdf.Connect = stConnect
db.TableDefs.Append tdf
db.TableDefs.Refresh

Set rst = db.OpenRecordset("dbo_Employees", dbOpenDynaset, dbSeeChanges)
rst.CacheSize = 5
rst.FillCache
Debug.Print rst.Fields(0).Name, rst.Fields(0).Value
rst.Close

End Sub

 

[Brendan Reynolds]

Great, thanks for letting me know that it worked.

--
Brendan Reynolds

Brendan, I modified your code and it works.

Thanks,

Chris