MS Access

Что мы знаем про каунтеры (aka счетчики)

Опубликовано: 30 апр 04
Рейтинг:

Автор: Владимир Саныч, участники форума по Аксессу
Прислал: Владимир Саныч

Вопросы


Q1: Как создать свой счетчик (чтобы поле было не типа счетчик)?

Q2: Как заставить счетчик начать выдавать значения начиная с некоторой заданной величины?

Q3: Может ли поле счетчика содержать повторяющиеся значения?

Q4: В таблице есть счетчик, но его значения идут не подряд, несколько чисел в середине отсутствуют. Как перезаполнить поле, чтобы дырок не было? (Другой вариант вопроса. Счетчик показывает, что последняя запись в моей таблице имеет номер N, а реально записей меньше. Почему счетчик неправильно считает количество записей в таблице? Что это - баг или фича?)

Q5: Как сымитировать счетчик в отчете?

Q6: Как сымитировать счетчик в запросе на добавление?

Q7: Как сымитировать счетчик в обычном запросе либо ленточной форме?

Q8: Как получить значение счетчика только что добавленной записи?

Q9: Как создать одним запросом таблицу со счетчиком?

Вопросы с ответами


Q1: Как создать свой счетчик (чтобы поле было не типа счетчик)?

A: Надо написать функцию, к которой обращаться либо в DefaultValue контрола (к сожалению, DefaultValue поля в таблице допускает только ограниченный набор стандартных функций), либо в программе, которая добавляет запись через рекордсет, либо в запросе на добавление. Ниже приведено несколько вариантов такой функции. Особое внимание надо уделить обработчику ошибок.

Вариант 1

Nz(DMax(...),0)+1

Так можно нумеровать записи даже внутри группы, а не только насквозь через всю таблицу. Для этого надо правильно задать параметры DMax.

Правда, надо отдельно позаботиться о ситуации, когда два юзера обратятся к этому "генератору счетчиков" одновременно.

Вариант 2

Заводим отдельную таблицу с одним полем типа счетчик и без данных. Приводимая ниже процедура обращается к такой таблице и возвращает очередное значение для "нашего" счетчика. Внимание - файл, в котором сидит эта таблица, запрещено сжимать.

Function Cou() As Long

Dim ws As Workspace, db As Database
Dim rsCounter As Recordset

On Error GoTo errCou
Set ws = DBEngine(0)
Set db = CurrentDb
1
ws.BeginTrans
2
Set rsCounter = db.OpenRecordset("select * from tabCounter")
3
rsCounter.AddNew
Cou = rsCounter!nCounter
'Close without update!'
rsCounter.Close
4
ws.CommitTrans
5
Exit Function

errCou:
Select Case Erl
    Case 3
        rsCounter.Close
        Set rsCounter = Nothing
        ws.Rollback
        DBEngine.Idle DB_FREELOCKS
        Resume 1
    Case 2, 4
        ws.Rollback
        DBEngine.Idle DB_FREELOCKS
        Resume 1
    Case Else
        Resume Next
End Select

End Function

Подвариант: все-таки делать rsCounter.Update, тогда по виду этой таблицы будет сразу ясно, какое значение было выдано последним. Правда, в этом случае файл станет расти.

Еще подвариант: держать в этой таблице одну запись, в которой находится очередное значение счетчика, и вместо AddNew делать Edit и rsCounter!nCounter=rsCounter!nCounter+1, а потом соответственно Update.

Вариант 3 (от Гетца)

Держим в отдельной таблице очередное значение счетчика и каждый раз увеличиваем его на 1. Таблица блокируется на момент чтения и увеличения счетчика, а все, кто в нее будут в это время стучаться, спокойно ждут (см. обработчик ошибок adhGetNextAutoNumber_Err) освобождения таблицы.

Function adhGetNextAutoNumber(ByVal strTableName As String) As Long

    On Error GoTo adhGetNextAutoNumber_Err

    Dim wrk As dao.Workspace
    Dim db As dao.Database
    Dim rstAutoNum As dao.Recordset
    Dim lngW As Long
    Dim lngX As Long
    Dim intRetryCount As Integer
    
    Randomize
    DoCmd.Hourglass True
    intRetryCount = 0

    Set wrk = dao.DBEngine.Workspaces(0)
    Set db = wrk.OpenDatabase(adhCurrentDBPath() & adhcAutoNumDb, False)
    Set rstAutoNum = db.OpenRecordset(strTableName & "_ID", dbOpenTable, dbDenyRead)

    rstAutoNum.MoveFirst
    rstAutoNum.Edit
    rstAutoNum!NextAutoNumber = rstAutoNum!NextAutoNumber + 1
    rstAutoNum.Update
 
    adhGetNextAutoNumber = lngNextAutoNum

adhGetNextAutoNumber_Exit:
    DoCmd.Hourglass False
    On Error Resume Next
    rstAutoNum.Close
    Set rstAutoNum = Nothing
    db.Close
    Set db = Nothing
    wrk.Close
    Set wrk = Nothing
    Exit Function

adhGetNextAutoNumber_Err:
    Select Case Err.Number
        Case adhcErrRI, adhcLockErrCantUpdate2, adhcLockErrTableInUse
            intRetryCount = intRetryCount + 1
            If intRetryCount > adhcLockRetries Then
                adhGetNextAutoNumber = -1
                Resume adhGetNextAutoNumber_Exit
            Else
                dao.DBEngine.Idle
                lngW = intRetryCount ^ 2 * _
                  Int((adhcLockUBound - adhcLockLBound + 1) * Rnd() + adhcLockLBound)
                For lngW = 1 To lngW
                    DoEvents
                Next lngW
                Resume
            End If
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, _
             vbOKOnly + vbCritical, "adhGetNextAutoNumber"
            adhGetNextAutoNumber = -1
            Resume adhGetNextAutoNumber_Exit
    End Select

End Function

Q2: Как заставить счетчик начать выдавать значения начиная с некоторой заданной величины?

A1: Добавить в таблицу со счетчиком при помощи инсерта запись, в которой полю счетчика дается значение на 1 меньше, чем надо. Потом удалить эту запись. Способ работает только при условии, что этот счетчик этого или большего значения еще не выдавал. (Если таким образом дать счетчику отрицательное значение, то он начнет выдавать отрицательные значения, несмотря на то что уже выдавал значения, большие их. Играя на этом, можно добиться, чтобы счетчик выдавал любые значения, в т.ч. и те, которые уже были.)

A2: Сжать базу, в которой сидит таблица со счетчиком. Счетчик будет выдавать значения начиная с наибольшего из существующих +1. В некоторых версиях Аксесса это работает только при условии, что таблица со счетчиком пуста (и тогда счетчик начнет выдавать значения с 1).

A3: Начиная с Аксесса 2000, можно запустить запрос наподобие такого:

alter table Таблица1 alter column ПолеСчетчик counter(1,1)

Q3: Может ли поле счетчика содержать повторяющиеся значения?

A: В принципе да. Этого несложно достичь, меняя состояние счетчика описанными способами. Однако если при этом возникнут нарушения ключа (вообще говоря, поле счетчика можно и не делать ключевым, но обычно все-таки принято делать), то записи просто не смогут добавляться. Каждая неудачная попытка добавить запись будет увеличивать значение счетчика на 1. Когда зона существующих значений будет пройдена, то записи опять смогут добавляться.

Q4: В таблице есть счетчик, но его значения идут не подряд, несколько чисел в середине отсутствуют. Как перезаполнить поле, чтобы дырок не было? (Другой вариант вопроса. Счетчик показывает, что последняя запись в моей таблице имеет номер N, а реально записей меньше. Почему счетчик неправильно считает количество записей в таблице? Что это - баг или фича?)

A: Это нормальная ситуация. Если возникла необходимость, чтобы значения счетчика шли подряд, значит база была спроектирована неверно. Поле счетчика должно служить только для однозначной идентификации записей (и, возможно, порядка их занесения), юзер не должен видеть его значений, а если и увидит, то не должен возражать против тех значений, которые есть. Счетчик не служит для подсчета записей.

Q5: Как сымитировать счетчик в отчете?

A: Заводим текстбокс и задаем ему свойства:

ControlSource = "=1"
RunningSum = Over All

Q6: Как сымитировать счетчик в запросе на добавление?

A: Пишем функцию примерно такого вида:

Function MyFun(varDummy As Variant, Optional iStartValue As Variant) As Long
Static n As Long
If IsMissing(iStartValue) Then
    MyFun = n
    n = n + 1
Else
    n = iStartValue
    MyFun = True
End If
End Function

В запросе обращаемся к ней дважды:

  • в части WHERE - с параметрами (чтоугодно,N), где N равно нужному начальному значению счетчика;
  • в части SELECT - в качестве первого параметра передавать любое поле из таблицы, второй параметр не указывать.

    Внимание: упрощать этот код, удаляя из него параметр, передаваемый в части SELECT, - нельзя. Без параметра функция будет вызвана только один раз, а не в каждой записи заново.

    Q7: Как сымитировать счетчик в обычном запросе либо ленточной форме?

    A1:
    SELECT (Select Sum(1) From t AS p Where p.f<=p1.f), p1.f
    FROM t AS p1
    ORDER BY p1.f;
    

    A2:
    SELECT DCount("f", "t","f<=" & CStr(f)), f
    FROM t
    ORDER BY f;
    

    Примечание 1. Поле f обязано быть уникальным.

    Примечание 2. Способ 1 быстрее работает, но является необновляемым.

    Примечание 3. Аналогичный вопрос в другом разделе FAQ: https://www.sql.ru/faq/faq_topic.aspx?fid=126

    A3: В новых версиях Аксесса у формы есть свойство CurrentRecord.

    Q8: Как получить значение счетчика только что добавленной записи?

    A1: Если запись добавляется через рекордсет, то так:

    rs.AddNew
    переменная = rs!полесчетчика
    ...
    rs.Update
    
    'или
    
    Set rs = ...OpenRecordset("...where 1=0") 'обязательно пустой рекордсет
    rs.AddNew 'ровно один раз; при добавлении двух и более записей ничего не получится
    ...
    rs.Update
    rs.MoveFirst
    переменная = rs!полесчетчика
    
    'или
    
    rs.AddNew
    ...
    rs.Update
    rs.Bookmark = rs.LastModified
    переменная = rs!полесчетчика
    

    A2: Более широкий круг применимости у такого способа:

    Dim rs As ADODB.Recordset 
    Set rs = New ADODB.Recordset
    CurrentProject.Connection.Execute "INSERT ..."
    rs.Open "SELECT @@identity as cou", CurrentProject.Connection
    переменная = rs!cou
    rs.Close
    

    Однако и этот способ имеет ограничения, а именно:

  • работает либо через ADO, либо через DAO в Jet 4 и позже, и только с базами формата Аксесса 2000 и позже;
  • возвращает значение только из записи, добавленной программно, но не через юзер-интерфейс.

    A3: Для adp годится такая модификация того же способа:

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
    

    Q9: Как создать одним запросом таблицу со счетчиком?

    A: create table xx (id counter)

  • Комментарии


    • Создать поле числового типа с именем "Номер"
      If Me.Номер.Value = 0 Then
      Me.Номер.Value = Me.CurrentRecord
      End If

    • Почему в таблице появляются повторяющиеся значения счетчика и как от этого избавиться?

    • Люди! объясните неграмотному! Что означает create table xx (id counter). Куда это нужно вставить???

    • В Q7 как указать, чтобы выбиралось начиная с определенного номера и определенное количество? Например, RowNumber >= 500 AND RowNumber <= 1000

      SELECT (Select Sum(1) From t AS p Where p.f<=p1.f)
      AS RowNumber, p1.f
      FROM t AS p1
      ORDER BY p1.f;

    • 25 января 2010, 12:01 Rustem Tastanov

      Спасибо! Получил более чем исчерпывающий ответ.

    • alter table containers alter column [n] counter(1,1);
      Помогло в 2-часовом биении фейсом об тейбл! Спасибо!

    • Спасибо

    • Все супер, но вот такой простенький вопрос... Создавая базу я переодически ее проверяю, на добавление записей, на удаление... В итоге, счетчик постоянно двигается в перед... А как, когда все будет готово, просто тупо удалить все записи во всех таблицах и что б при этом счетчики в каждой из таблиц опять стартовали с единицы?

    • Предлагаю и спрашиваю Q10: Как программно определить поле счетчик в таблице?

    • 08 июля 2007, 22:44 Erik_Kartmann

      create table xx (id counter)
      о!

    • 16 мая 2007, 02:30 ist-stylish

      действительно полезно, очень помогло:)

    • Огромный Вам респект, а то уже голову сломал.
      Спаасибо!

    • 31 марта 2006, 20:03 Данилыч

      Не описано почему при реальном счетчике возникают дыры (например при откате - Esc), на туже тему: при подмене поля счетчик, не описана ситуация как поступать если сделан тот-же откат (ведь новое значение уже получено)

    • Просто прекрасный обзор. Корректо, понятно, объемлюще. Здорово!

    • Q2!A2 - работает!

    • Да, в техническом плане, тема практически исчерпана. Не обсудить ли теперь, так сказать, гуманитарную сторону. То есть, возможности и ограничения практического использования ключевых полей. Например, если справочник клиентов ведут 10 менеджеров, то как можно обеспечить ввод одного клиента не более чем один раз? Как не допустить «ООО Днепр» вместе с «ТОВ Дніпро»? То есть, вопрос в использовании внешних ключей и в том, откуда их брать.

    • Саныч, едрён батон, что бы мы без тебя делали!!!



    Необходимо войти на сайт, чтобы оставлять комментарии

    Раздел FAQ: MS Access / Что мы знаем про каунтеры (aka счетчики)