Что мы знаем про каунтеры (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: http://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)