Resetovanje AutoNumber u Accessu?

Discussion in 'Operativni sistemi, aplikacije i programiranje' started by Zombie, Feb 20, 2009.

  1. Zombie

    Zombie Aktivista

    Vozdra,
    radio sam u accessu neku masku za bazu podataka, i kada sam je testirao i unosio probne podatke AutoNumber im je dodjeljivao automatski brojeve o 1 pa nadalje. Sada kada hocu da unosim prave podatke on nasatvalja da dodjeljuje brojeve tamo gdje je stao sa onim probnim, iako sam ove probne recordse izbrisao. Pitanje je, kako da resetujem taj AutoNumber da pocne opet brojati od 1. Procitao sam da to radi funkcija Compact and Repair Database, ali ne funkcionise.
     
  2. skahu

    skahu Guest

    ovo sam nasao na http://www.elitesecurity.org
    tamo imaju rasirenu diskusiju o ACCESS bazama podataka, mozda pomogne

    Code:

    Function AutoNumFix() As Long
    'Purpose: Find and optionally fix tables in current project where
    ' Autonumber is negative or below actual values.
    'Return: Number of tables where seed was reset.
    'Reply to dialog: Yes = change table. No = skip table. Cancel = quit searching.
    'Note: Requires reference to Microsoft ADO Ext. library.
    Dim cat As New ADOX.Catalog 'Catalog of current project.
    Dim tbl As ADOX.Table 'Each table.
    Dim col As ADOX.Column 'Each field
    Dim varMaxID As Variant 'Highest existing field value.
    Dim lngOldSeed As Long 'Seed found.
    Dim lngNewSeed As Long 'Seed after change.
    Dim strTable As String 'Name of table.
    Dim strMsg As String 'MsgBox message.
    Dim lngAnswer As Long 'Response to MsgBox.
    Dim lngKt As Long 'Count of changes.

    Set cat.ActiveConnection = CurrentProject.Connection
    'Loop through all tables.
    For Each tbl In cat.Tables
    lngAnswer = 0&
    If tbl.Type = "TABLE" Then 'Not views.
    strTable = tbl.Name 'Not system/temp tables.
    If Left(strTable, 4) <> "Msys" And Left(strTable, 1) <> "~" Then
    'Find the AutoNumber column.
    For Each col In tbl.Columns
    If col.Properties("Autoincrement") Then
    If col.Type = adInteger Then
    'Is seed negative or below existing values?
    lngOldSeed = col.Properties("Seed")
    varMaxID = DMax("[" & col.Name & "]", "[" & strTable & "]")
    If lngOldSeed < 0& Or lngOldSeed <= varMaxID Then
    'Offer the next available value above 0.
    lngNewSeed = Nz(varMaxID, 0) + 1&
    If lngNewSeed < 1& Then
    lngNewSeed = 1&
    End If
    'Get confirmation before changing this table.
    strMsg = "Table:" & vbTab & strTable & vbCrLf & _
    "Field:" & vbTab & col.Name & vbCrLf & _
    "Max: " & vbTab & varMaxID & vbCrLf & _
    "Seed: " & vbTab & col.Properties("Seed") & _
    vbCrLf & vbCrLf & "Reset seed to " & lngNewSeed & "?"
    lngAnswer = MsgBox(strMsg, vbYesNoCancel + vbQuestion, _
    "Alter the AutoNumber for this table?")
    If lngAnswer = vbYes Then 'Set the value.
    col.Properties("Seed") = lngNewSeed
    lngKt = lngKt + 1&
    'Write a trail in the Immediate Window.
    Debug.Print strTable, col.Name, lngOldSeed, " => " & lngNewSeed
    End If
    End If
    End If
    Exit For 'Table can have only one AutoNumber.
    End If
    Next 'Next column
    End If
    End If
    'If the user chose Cancel, no more tables.
    If lngAnswer = vbCancel Then
    Exit For
    End If
    Next 'Next table.

    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    AutoNumFix = lngKt
    End Function
     
  3. Zombie

    Zombie Aktivista

    Hvala, rijesio sam problem.