Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    Unanswered: Preserving record numbers through backend upgrade

    Hi folks, more help needed please ....

    Summary: need to be able to carry data forward from one version of a backend to another, while preserving the record ID number.

    Full version:

    I have a multiuser database with Access 2007 Backend and Frontends. There is one main data entry form: I am currently using an Autonumber field to create a unique ID per record.

    The first part of my problem is that there is a lot of change going on - new requirements all the time - and although I try hard to keep these to the FE, every now and then something comes up that requires a change to the BE, while making sure that existing data gets carried forward.

    We can assume that there aren't any great data conversion problems - I'm mostly adding new fields more than anything else. So there is a reasonably simple upgrade process: open up a new empty backend, import the required tables from the old backend, copy and paste the data over for each table.

    I could probably (semi-)automate this but its easy enough that I don't feel the need to.

    HOWEVER (here comes the problem): each record includes a free-text box in which users write comments. Often the comments include helpful references to another record (eg "this is related to record 27"). As this is free text, I have no chance of codifying this in proper relationships.

    But, if I use the Copy/Paste method above, the record numbers will all be recreated. Yes, I can force a restart at 1 ... but this won't help as, in the meantime, some records will have been deleted so the current data does not have consecutive ID numbers. This means that the user comments are now pointing to the wrong records!

    I've read some threads about using a manual autonumber instead of the built in one: for example, using DMAX to get the highest current number and add 1 ... but this is prone to failure in a multiuser environment if two people open a new record at the same time. Or using a single record in another table to remember the current max number - read, use and increment from a function. I'm not entirely confident about being absolutely sure that this will always work, though.

    I've wondered about having two fields: an Autonumber field which Access can make anything it wants and which is invisible to the user, and a visible "record number" field which is visible and, not being an Autonumber, can easily carry forward from one BE version to another. But this still begs the question of how I make sure that this visible record number is unique ... and if I am ensuring that it is unique, why bother with the Autonumber field at all?

    Are there any suggestions out there for the best way to handle this? Or a completely different approach to the problem?

    TIA
    Ceejay

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    copy the old back end
    make your changes to the copy. if youwant to be swankly make those as queries using DDL
    don't import the tables or copy and paste
    jobsagoodun
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    Hi

    Many thanks for the speedy response. As always, there is something I left out of my original post (I thought it was long enough already!).

    There are multiple copies of this system in use, some of them far away from me, and I was hoping to make the upgrade process simple enough that I could give a moderately literate person instructions as to what to do.

    I can imagine writing something down that includes "copy" and "paste" and having it followed, I am less confident about writing stuff about creating new fields, changing validation rules, changing data types, etc.

    But its an interesting thought: could I reasonably easily script the changes to be made to the BE design? I've not done that before...

    Ceejay

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    deploy an upgrade db that connects to the the data
    run the update queries against that data
    deploy the new front end
    the new front end connects to the new data by code

    most of this sort of code should be available in the coide bank
    if not its available in the Sybex 'Access Developer Handbook', so it should also be out on t'web

    it helps if you don't leave out stuff when posing your questons
    if you are worried about the length of the OP then prune it to remove redundant or irrelevant info
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's an easy way to copy all tables (except MSys*) from an existing database to a new one:
    Code:
    Function CreateNewDb(ByVal SourceDbName As String, ByVal DestinationDbName As String)
    
        Dim appAccess As Access.Application
        Dim wks As DAO.Workspace
        Dim dbSource As DAO.Database
        Dim dbDestination As DAO.Database
        Dim tbl As DAO.TableDef
        
        If Len(Dir(SourceDbName)) = 0 Then
            MsgBox "Cannot find " & SourceDbName, vbExclamation, "CreateNewDb"
        ElseIf Len(Dir(DestinationDbName)) > 0 Then
            MsgBox DestinationDbName & " already exists", vbExclamation, "CreateNewDb"
        Else
            Set wks = Workspaces(0)
            Set dbDestination = wks.CreateDatabase(DestinationDbName, dbLangGeneral)
            dbDestination.Close
            Set appAccess = New Access.Application
            With appAccess
                .OpenCurrentDatabase SourceDbName
                Set dbSource = appAccess.CurrentDb
                For Each tbl In dbSource.TableDefs
                    If Left(tbl.Name, 4) <> "MSys" Then .DoCmd.CopyObject DestinationDbName, , acTable, tbl.Name
                Next
                .Quit
            End With
        End If
        
    End Function
    Have a nice day!

  6. #6
    Join Date
    Mar 2010
    Posts
    88
    More thanks for both of the last two responses, but I'm not getting how they help me solve my problem. If I use an upgrade database to pull information out of the old BE, do I use an AutoNumber ID or not? If I do, then the IDs will get renumbered. If not, do I then have to use a manual autonumber?

    I can see that healdem's first post would work, but only if I can easily get to each production BE.

    Aren't both of the last two suggestions much the same as the copy and paste, albeit a bit neater and easier?

    Thanks

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CeejayDBF View Post
    Aren't both of the last two suggestions much the same as the copy and paste, albeit a bit neater and easier?
    Yes they are; but from there nothing prevents you from going further. If the purpose of the upgrade is to modify the structure of one or several tables, you can write functions executing SQL DDL (Data Definition Language) statements to modify the metatada of this or these tables. It can be done even if they are populated with data.

    You can then create a totally automatic upgrade program that will:
    1- Create a new database and import the existing tables into it.
    2- Use SQL DDL to modify the structure of the imported tables and/or create new ones.
    Have a nice day!

  8. #8
    Join Date
    Mar 2010
    Posts
    88
    I can't lay my hands on it just at the moment, but I did do some looking up of DDL as implemented in Access and it seems that I'd be very limited - you can't make anything other than basic changes to the table structures.

    And I'm no closer to understanding how any of that would help me preserve ID numbers, or any other solution to my underlying problem.

    Ceejay

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Another solution would consist in:

    1- Create the new tables with their desired structures, including the Identity column (autonumber).
    2- Populate each table with as many empty rows as the max. Identity in the related old table.
    3- Use an update query to insert the data columns from each old table into the related new one, using a Join or WHERE clause to link both old and new tables by the Identity column.
    4- Delete the rows of the new tables that have no match in the related old tables, also usin a query (DELETE FROM [NewTable].

    * To create the rows in a new (empty) table:
    Code:
    Function CreateRows()
    
        ' Creates rows into the new table.
        '
        ' SysCounter is the name of the Identity column in both tables (old and new).
        '
        Const strSQL As String = "INSERT INTO Tbl_Data_New ( SysCounter ) VALUES ( @ );"
    
        Dim i As Long
        
        For i = 1 To DMax("SysCounter", "Tbl_Data_Old")
            CurrentDb.Execute Replace(strSQL, "@", i), dbFailOnError
        Next i
        
    End Function
    * To insert data from old table into new table:
    Code:
    Function AppendData()
    
        ' Copy rows of data (except Identity value) from old table to new table
        ' where Identy values match in both tables.
        '
        Dim strSQL As String
        
        strSQL = "UPDATE Tbl_DATA_Old INNER JOIN Tbl_Data_New ON Tbl_DATA_Old.SysCounter = Tbl_Data_New.SysCounter " & _
                 "SET Tbl_Data_New.CLIENT = Tbl_DATA_Old.CLIENT;"
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Function
    * To delete non matching rows:
    Code:
    Function DeleteNonMatchingRows()
    
        ' Delete rows in new table that have no matching row in old table.
        '
        Dim strSQL As String
        
        strSQL = "DELETE * FROM Tbl_Data_New WHERE SysCounter NOT IN ( SELECT SysCounter FROM Tbl_Data_Old );"
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Function
    According to the database from where you run the code, you'll have to replace CurrentDb by a reference to the target database.
    Have a nice day!

  10. #10
    Join Date
    Mar 2010
    Posts
    88
    Sinndho - many thanks for that, it is very interesting and I can see how I could make that work very neatly: just ship the new BE with some built in functions for upgrading from the previous version - ask for the name/location of the old BE and do the stuff you've outlined as well as any other data pulling/changing that might be necessary.

    I can see in principle how the functions work but can I just ask a dumb question?

    In Append Date, could you explain "CLIENT" in the last part of the SQL string:
    SET Tbl_Data_New.CLIENT = Tbl_DATA_Old.CLIENT ?

    Thanks again
    Ceejay

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can create tables, alter tables, create indexes in JET DDL
    How to use common Data Definition Language (DDL) SQL statements for the Jet database engine
    so what the limiting factor?
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    CLIENT simply is the name of a column in the table. You need to use the usual syntax of the UPDATE SQL statement:
    Code:
    UPDATE TargetTable SET Column1 = Value1, Column2 = Value2, ... etc.
    This is annoying if you have many columns, but you can automate the processus and build a SQL statement generator:
    Code:
    Function BuildSQL_UPDATE() As String
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim strSQL As String
        
        Set dbs = CurrentDb
        Set tdf = dbs("Tbl_DATA_Old")
        For Each fld In tdf.Fields
            If fld.FieldSize <> "SysCounter" Then   ' Skip Identity column.
                If Len(strSQL) > 0 Then strSQL = strSQL & ", "
                strSQL = strSQL & "Tbl_Data_New." & fld.Name & " = Tbl_DATA_Old." & fld.Name
            End If
        Next
        strSQL = "UPDATE Tbl_DATA_Old INNER JOIN Tbl_Data_New ON Tbl_DATA_Old.SysCounter = Tbl_Data_New.SysCounter SET " & strSQL & ";"
        BuildSQL_UPDATE = strSQL
        
    End Function
    Have a nice day!

  13. #13
    Join Date
    Mar 2010
    Posts
    88
    Quote Originally Posted by healdem View Post
    you can create tables, alter tables, create indexes in JET DDL
    How to use common Data Definition Language (DDL) SQL statements for the Jet database engine
    so what the limiting factor?
    I was looking here Microsoft Access tips: DDL Programming Code Examples and read this:

    "While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero-length strings. You can create a Yes/No field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a Date/Time field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all."

  14. #14
    Join Date
    Mar 2010
    Posts
    88
    Quote Originally Posted by Sinndho View Post
    CLIENT simply is the name of a column in the table. You need to use the usual syntax of the UPDATE SQL statement:
    More thanks, that's very helpful. I will have a go at this...

    Ceejay

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CeejayDBF View Post
    I was looking here Microsoft Access tips: DDL Programming Code Examples and read this:

    "While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero-length strings. You can create a Yes/No field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a Date/Time field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all."
    This is true. However what you cannot do to a table using DDL can be done by manipulating the corresponding TableDef object through VBA.
    Have a nice day!

Posting Permissions

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