Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

CacheSize read-only for Recordset?

Discussion in 'Information Technology' started by Chris Burnette, Oct 27, 2005.

  1. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    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
     
  2. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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?

    --
    Brendan Reynolds

    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    news:F1C51AB1-EF87-4733-9B8A-49211D5CDCF1@microsoft.com...
    >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
     
  3. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    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" wrote:

    > 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?
    >
    > --
    > Brendan Reynolds
    >
     
  4. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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


    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    news:86380F62-7645-4D01-885C-E924D4A1909D@microsoft.com...
    > 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" wrote:
    >
    >> 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?
    >>
    >> --
    >> Brendan Reynolds
    >>

    >
     
  5. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    Brendan, I modified your code and it works.

    Thanks,

    Chris

    "Brendan Reynolds" wrote:

    >
    > 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
    >
    >
    > "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    > news:86380F62-7645-4D01-885C-E924D4A1909D@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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?
    > >>
    > >> --
    > >> Brendan Reynolds
    > >>

    > >

    >
    >
    >
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Great, thanks for letting me know that it worked.

    --
    Brendan Reynolds


    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    news:3991E89E-8BC8-4DEA-8C66-82AD039A83B4@microsoft.com...
    > Brendan, I modified your code and it works.
    >
    > Thanks,
    >
    > Chris
    >
    > "Brendan Reynolds" wrote:
    >
    >>
    >> 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
    >>
    >>
    >> "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in
    >> message
    >> news:86380F62-7645-4D01-885C-E924D4A1909D@microsoft.com...
    >> > 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" wrote:
    >> >
    >> >> 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?
    >> >>
    >> >> --
    >> >> Brendan Reynolds
    >> >>
    >> >

    >>
    >>
    >>
     

Share This Page