Citat:
cesare: Pozdrav !!!
Imam jedan problem za koji bih trebao vasu pomoc. Pravim program koji koristi Access bazu i polje koje je jedinstveni kljuc za raspoznavanje robe koja se nalazi u tabeli lager_lista je tipa autonumber. Posto vrednost ovog polja ima svoju maksimalnu vrednost, a roba se stalno menja u tabeli (brise se stara koja se proda, a dodaje se nova koja se nabavi), moje pitanje je da li ce Access, posto nikada nece imati zapisa u tabeli koliki je limit za autonumber svojstvo, praviti problem kad vrednost ovog polja dostigne svoj maksimum, ili ce obelezavati ovo polje ponovo sa 1,2 itd. Posto nemam previse iskustva sa ovom bazom zamolio bih vas za pomoc !!!
Unapred hvala ...
Mislim da neces imati problem sa ovim, ali naravno sve zavisi koliko se opterecuje baza, tj. koliko je dnevni upis podataka u bazu. Evo recimo koda koji resetuje AutoNumber vrednost (verovatno je kod napisan iz razloga jer je neko nekad imao problema sa tim...)
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
Polje koje je primarni kljuc u bazi
Re: Polje koje je primarni kljuc u bazi