Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Sep 2010
    Posts
    31

    Unanswered: I split my database and now getting 3251 errors

    So I bit the bullet and split my ACCESS 2003 database, but now I'm getting this 3251 "Operation us not supported for this type of object" on the Index line

    I googled, but couldn't get a straight answer. Hopefully its something simple and I don't have to do mass changes.

    Help?

    Dim x As Recordset
    Set x = CurrentDb.OpenRecordset("table")
    x.Index = "primarykey" <------------------error line
    If IsNull(IDref) = False Then
    x.Seek "=", IDref
    x.Edit
    Else
    x.AddNew
    End If

    And I know this isn't the tightest code. Its from way back when I was starting out.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    Dim x As dao.Recordset
    Set x = CurrentDb.OpenRecordset("table", dbOpenTable, dbSeeChanges)
    x.Index = "primarykey"
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Posts
    31
    Dim x As dao.Recordset
    Set x = CurrentDb.OpenRecordset("table", dbOpenTable, dbSeeChanges) <--gives me a 3219 Invalid Operation

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry! This is because "Table" is a linked table. MSAccess opens a recordset of type Dynaset for Access linked tables on CurrentDb.
    If you really need to use a recordset of type Table, you could try:
    Code:
        Dim dbs As dao.Database
        Dim rst As dao.Recordset
        Set dbs = OpenDatabase("C:\Documents and Settings\SinnDHo\My documents\Access\MyDatabase.mdb")
        Set rst = dbs.OpenRecordset("Table", dbOpenTable, dbSeeChanges)
    Have a nice day!

  5. #5
    Join Date
    Sep 2010
    Posts
    31
    Thanks,

    But ugh, now I have to go through all my seek and index references and re-do all that code. Never thought a database split would cause all this work

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Opening Table type recordsets and using indexes on them is not the usual way to handle data sets. Moreover recordsets of type Table have very limited functionalities. The only justification for using them is when speed really is a matter.
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Posts
    31
    Quote Originally Posted by Sinndho View Post
    Opening Table type recordsets and using indexes on them is not the usual way to handle data sets. Moreover recordsets of type Table have very limited functionalities. The only justification for using them is when speed really is a matter.
    I'm open for suggestions.

    Looking at my simple example above, how would I go about writing code to edit/add a record in my linked table?

    Note that I've written my forms to load all customer data into unbound fields and then save when they hit the save button. I never cared for the live editing of a record. It was just something I believed in when I started writing it 8 years ago.

    So I would normally open the table save all the xx("table item")= data items then do a closing .update

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do it in a very similar manner with a Dynaset type RecordSet. The main difference is how you find a particular row in the table:
    Code:
    Function PopulateControls(ByVal RecordID As Long) As Boolean
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Tbl_CF_DATA", dbOpenDynaset, dbSeeChanges)
        With rst
            .FindFirst "SysCounter = " & RecordID
            ' SysCounter is the primary key of the table Tbl_CF_DATA
            If .NoMatch = False Then
                Me.Text_SysCounter.Value = !SysCounter
                Me.Text_Customer.Value = !Customer
                Me.Text_Destination.Value = !Destination
                Me.Text_Order_Number.Value = !Order_Number
                Me.Text_Engineer_Fist_Name.Value = !Engineer_Fist_Name
                Me.Text_Engineer_Last_Name.Value = !Engineer_Last_Name
                Me.Text_Channel.Value = !Channel
                Me.Text_Vehicle.Value = !Vehicle
                Me.Text_IPC.Value = !IPC
                Me.Text_Archived.Value = !Archived
                PopulateControls = True
            End If
            .Close
        End With
        Set rst = Nothing
        Set dbs = Nothing
        
    End Function
    
    Function SaveControlValues(ByVal RecordID As Long) As Boolean
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Tbl_CF_DATA", dbOpenDynaset, dbSeeChanges)
        With rst
            ' SysCounter is the primary key of the table Tbl_CF_DATA
            .FindFirst "SysCounter = " & RecordID
            If .NoMatch = False Then
                !Customer = Me.Text_Customer.Value
                !Destination = Me.Text_Destination.Value
                !Order_Number = Me.Text_Order_Number.Value
                !Engineer_Fist_Name = Me.Text_Engineer_Fist_Name.Value
                !Engineer_Last_Name = Me.Text_Engineer_Last_Name.Value
                !Channel = Me.Text_Channel.Value
                !Vehicle = Me.Text_Vehicle.Value
                !IPC = Me.Text_IPC.Value
                !Archived = Me.Text_Archived.Value
                SaveControlValues = True
            End If
            .Close
        End With
        Set rst = Nothing
        Set dbs = Nothing
        
    End Function
    Have a nice day!

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by ronosh View Post
    Thanks,

    But ugh, now I have to go through all my seek and index references and re-do all that code. Never thought a database split would cause all this work
    This is why I urge everyone to split the database initially. I start every database split. There are many benefits to splitting it from the start. IMHO, splitting is not just for multi-user database but for better software development.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Sep 2010
    Posts
    31
    Okay, how about this.

    How do I get

    DoCmd.DeleteObject acTable, "Promotions"

    to delete the linked table, not the "link". The link is gone, but the table still exists in the back end.

    same goes for the rename. I guess I have to be more specific?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As a matter of course you would not want to be deleting and creating tables, however assuming you have a good reason just use good ol' ISO SQL.
    Code:
    DB.Execute "DROP TABLE Promotions"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2010
    Posts
    601
    The key to using

    Code:
    DB.Execute "DROP TABLE Promotions"
    on the back end database is to first set DB to the back end.


    Example:
    Code:
    Set DB = OpenDatabase("\\server\sharename\mybackend.mdb")
    
    DB.Execute "DROP TABLE Promotions"[
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  13. #13
    Join Date
    Sep 2010
    Posts
    31
    I see the DROP function, but there is no RENAME function.

    I was using this to set up my DEMO (replace real table with pre-loaded demo table) tables programatically, so I didn't have to hand move each one.

    Gonna have to build queries to move the data I suppose

    Holy headache, I still haven't gotten to my import/export of data from satellite locations/databases.

    Maybe I should go back to my single database that when I send out an update I just import all the tables into in...I know, set up right from the start, but it was years ago when I started and knew next to nothing back then

    Thanks for you're continued help!

  14. #14
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by ronosh View Post
    I see the DROP function, but there is no RENAME function.

    I was using this to set up my DEMO (replace real table with pre-loaded demo table) tables programatically, so I didn't have to hand move each one.

    Gonna have to build queries to move the data I suppose

    Holy headache, I still haven't gotten to my import/export of data from satellite locations/databases.

    Maybe I should go back to my single database that when I send out an update I just import all the tables into in...I know, set up right from the start, but it was years ago when I started and knew next to nothing back then

    Thanks for you're continued help!
    Now that you have split the database you have many new options. You might want to rethink how you do things now that you have this new design.

    If you are setting up a demo, why not just relink to a different back end that has the demo data.

    I regular relink to different back ends for testing.

    I also include my own custom Link Table Manger code that allows switching between backend very easy.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  15. #15
    Join Date
    Sep 2010
    Posts
    31
    Creating a DEMO back end :]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •