Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Update Primary Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Update Primary Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Wouldn't you want to move to a more normalized structure? (Main table with primary owners, related sub table with alternate owners)?

    To do what you requested, you can use the following code; replace tblTest with the name of your table.

    Sub ConcatNames()
    Dim dbs As DAO.Database
    Dim rstPri As DAO.Recordset
    Dim rstAlt As DAO.Recordset

    Set dbs = CurrentDb
    Set rstPri = dbs.OpenRecordset _
    ("SELECT * FROM tblTest WHERE T='P'", dbOpenDynaset)
    Do While Not rstPri.EOF
    rstPri.Edit
    rstPri!OwnerNamePriAlt = rstPri!OwnerName
    Set rstAlt = dbs.OpenRecordset _
    ("SELECT OwnerName FROM tblTest WHERE AN=" & _
    rstPri!AN & " AND T='A'", dbOpenForwardOnly)
    Do While Not rstAlt.EOF
    rstPri!OwnerNamePriAlt = _
    rstPri!OwnerNamePriAlt & ", " & rstAlt!OwnerName
    rstAlt.MoveNext
    Loop
    rstPri.Update
    rstPri.MoveNext
    Loop

    rstAlt.Close
    rstPri.Close
    Set rstAlt = Nothing
    Set rstPri = Nothing
    Set dbs = Nothing
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Update Primary Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    Your right about a more normalized structure, however this file is generated every 2 weeks for lookup reference purposes only and goes no further than this. This seemed the simplest way.

    Your code worked great, nice example of using 2 record sets on the same table.

    23,159 P records
    8,306 A records
    took 4 hours on a very well equipped computer

    I created a Make Table Query with just the A records, adjusted your code accordinly and it took 12 minutes, the table I made did not have any indexes on AN and T.

    To further speed this up, in code how can I make make an index for AN and T?

    Thanks for your help.

    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to Update Primary Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    The DAO TableDef object has a method CreateIndex; look it up in the online help and view the example. Here is some old code I once posted. Usage example:

    AddIndex "Table1", "Field1", "MyNewIndex"

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub AddIndex(TableName As String, FieldName As String, IndexName As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field

    On Error GoTo Err_AddIndex

    ' Reference to current database
    Set dbs = CurrentDb
    ' Reference to table
    Set tdf = dbs.TableDefs(TableName)
    ' Create new index
    Set idx = tdf.CreateIndex(IndexName)
    ' Create field for index
    Set fld = idx.CreateField(FieldName)
    ' Append the dield to the field list of the index
    idx.Fields.Append fld
    ' Append the index to the list of indexes of the table
    tdf.Indexes.Append idx
    ' Make sure the database engine knows about it
    tdf.Indexes.Refresh

    Exit_AddIndex:
    ' Clean up
    Set fld = Nothing
    Set idx = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_AddIndex:
    ' Inform user what went wrong
    MsgBox Err.Description, vbExclamation
    ' Goto clean up
    Resume Exit_AddIndex
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

Posting Permissions

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