Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: Autinumber Key field

    For annoying reasons of my own, I am importing a table by creating a new one and (with code) importing each record one at a time.

    My problem lies with the fact that I do not want their autonumber fields to change. My idea was to convert the autonumber field to an indexed long int while importing, then changing that field to an autonum.

    As me all know, access doesn't like this idea. How can I make it work?

    TIA
    Scott

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    - create a new table with the layout that you are looking for

    -create a vba subroutine that adds new dummy data into any field x number of times

    if your highest current autonumber is 3478 - then
    ....
    for i = 1 to 3478
    'add a new record
    ....
    next i
    ....

    - then run an update query linking the two tables on their autonumbers

    - run a delete query - deleting all remaining bogus records

  3. #3
    Join Date
    Jan 2004
    Posts
    26

    RE Autonumber key field

    That would normally work, except the orignal autonum field was random.

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    can you explain in more detail what you mean by random

  5. #5
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    i see what you mean now

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    why not import the table as is and use this copy as your 'new' table

    leave the autonum field alone but make whatever changes you want

    then run an update query linking the tables on the autonum

    prior to running the update - null ay fields that would not get updated

    you should be able to then delete any records that did not get update

  7. #7
    Join Date
    Jan 2004
    Posts
    26
    Due to corruptions, I want to rebuild the table from scratch from code. Then (again with code) import each record in to it individually. Because of links to other tables and such, the key field (the autonum) must remain the same. However once the rebuild is complete, it must be able to generate the autnum again.

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Try this-

    Make sure your old table is coming in sorted on the autonumber

    Keep calling this function to increment the new table's autonumber seed

    Let me know if it worked

    -Matt



    Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
    'You must pass the following variables to this function.
    'strTbl = Table containing autonumber field
    'strCol = Name of the autonumber field
    'lngSeed = Long integer value you want to use for next AutoNumber.

    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column

    'Set connection and catalog to current database.
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn

    Set col = cat.Tables(strTbl).Columns(strCol)

    col.Properties("Seed") = lngSeed
    cat.Tables(strTbl).Columns.Refresh
    If col.Properties("seed") = lngSeed Then
    ChangeSeed = True
    Else
    ChangeSeed = False
    End If
    Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing

    End Function

  9. #9
    Join Date
    Jan 2004
    Posts
    26
    It is working well, except it appears to be locking the database, so I cant add a new record. Even if I stop the code completly the database remains locked. However, if actually open the table in question and then close it, it will unlock it.

    Let me know if you can think of a reason for this or if you know how to fix it.


    TIA
    Scott
    Last edited by Balinor; 02-10-04 at 12:07.

  10. #10
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    I just tried the snippet below and I had no issues-

    Dim i As Long
    Dim sSQL As String
    Dim fOK As Boolean
    Dim db As DAO.Database

    Set db = CurrentDb

    For i = 1 To 20
    fOK = ChangeSeed("Table1", "ID", i * 2)
    sSQL = "INSERT INTO Table1 (Field1) VALUES ('BlahBlah')"
    db.Execute (sSQL)
    Next i



    When, where and how are you attempting to add a new record?

    Matt

  11. #11
    Join Date
    Jan 2004
    Posts
    26
    I use it like so:
    'Assume all variables are Dimmed (as am I) using DAO where applicable
    Ie. Dim DB as DAO.Database

    .
    .
    .
    ChangeSeed "MyTableName, "MyAutoField", MyNewSeed

    Set DB = CodeDB
    Set rst = DB.OpenRecordset ("MyTableName")

    rst.addnew 'Right here is where it will gives me the locked error

    rst![FirstName] = "Bla"
    rst![LastName] = "h"

    rst.update
    .
    .
    .

    The strange thing is, that it won't happen on every record, it appears to be random. As a matter of fact, if I start everything over, it wont even happen on the same records.

    Like I said before, if I pause it on the line that gives me the error, then open and immediatly close the table manually. It continues on just fine. If I comment out the SeedChange function...the code works just fine (except my autonums aren't the way I want them)

  12. #12
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Have you tried adding a DoEvents?

    Matt

  13. #13
    Join Date
    Jan 2004
    Posts
    26
    Originally posted by Rockey
    Have you tried adding a DoEvents?

    Matt
    Good suggestion....not working.

    Sorry to be such a pain,
    Scott

  14. #14
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Can you post all of your code.

  15. #15
    Join Date
    Jan 2004
    Posts
    26
    Here you go:
    I had made a test database with only a few records in it that worked fine. But, when I went to do a real life test. I started getting errors.

    Function GenTables(PathToDB As String) As Boolean
    'You must pass the path of the database you want to recreate
    Dim DB As DAO.Database
    Dim db2 As DAO.Database
    Dim SeedWorked As Boolean
    Dim tdf As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim tdfs As DAO.TableDefs
    Dim fld As DAO.Field
    Dim fld2 As DAO.Field
    Dim flds As DAO.Fields
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strTemp As String
    Dim X As Integer
    Dim Y As Integer
    Dim Z As Integer

    Set DB = DAO.Workspaces(0).OpenDatabase(PathToDB)
    Set tdfs = DB.TableDefs
    Set db2 = CodeDb

    'Start by recreating each table
    For X = 0 To (tdfs.Count - 1)
    Set tdf = tdfs(X)
    Set flds = tdf.Fields
    Set tdf2 = DB.CreateTableDef(tdf.Name)
    If Strings.Left(tdf.Name, 4) = "Usys" Or Strings.Left(tdf.Name, 4) = "Msys" Then GoTo NotThisTable

    'Recreate each field for that table
    For Each fld In flds
    Set fld2 = tdf2.CreateField(fld.Name, fld.Type, fld.Size)
    fld2.Attributes = fld.Attributes
    'Append our new field to the new table
    tdf2.Fields.Append fld2
    Set fld2 = Nothing
    Next fld
    'append the table to the current database's table definitions
    db2.TableDefs.Append tdf2

    Set flds = Nothing
    Set tdf2 = Nothing
    Set tdf = Nothing
    NotThisTable:
    Next X


    'Now that all of the tables are re-created, we must populate the records.
    For Each tdf In tdfs
    If Strings.Left(tdf.Name, 4) = "Usys" Or Strings.Left(tdf.Name, 4) = "Msys" Then GoTo Out
    Set flds = tdf.Fields
    Set rst = DB.OpenRecordset(tdf.Name)
    rst.MoveFirst
    strTemp = "None"
    For Each fld In flds
    If fld.Attributes = 17 Then
    strTemp = fld.Name
    GoTo FoundAutoNum
    End If
    Next fld
    FoundAutoNum:


    Do Until rst.EOF
    SeedNoWork:
    db2.Close
    Set db2 = Nothing

    Set db2 = CurrentDb

    'Change the autonumber seed
    If strTemp <> "None" Then
    SeedWorked = ChangeSeed(rst.Name, strTemp, rst.Fields(strTemp).Value)
    End If
    DoEvents
    If SeedWorked = False Then GoTo SeedNoWork

    Set rst2 = db2.OpenRecordset(tdf.Name)
    If rst2.EOF = False Then
    rst2.MoveFirst
    rst2.MoveLast
    End If

    rst2.AddNew
    For Each fld In flds
    rst2.Fields(fld.Name).Value = rst.Fields(fld.Name).Value
    Next fld
    rst2.Update
    rst2.Close
    Set rst2 = Nothing
    rst.MoveNext
    Loop
    Set flds = Nothing
    rst.Close
    Set rst = Nothing
    Set rst2 = Nothing
    Set tdf = Nothing
    Out:
    Next tdf
    GenTables = True
    End Function

Posting Permissions

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