Q5. Как сжимать БД?
5.1. Через меню: Сервис/Служебные программы/Сжать и восстановить базу данных;
5.2. Через меню: Сервис/Параметры/Вкладка "ОБЩИЕ"/Флаг "Сжимать при закрытии". С помощью данной настройки можно организовать автоматическое сжатие БД при каждом ее закрытии. Действие данного флажка относится только к открытой базе данных, при этом подключенные (прилинкованные) базы данных сжаты не будут. Как сжать прилинкованную БД см. Вопрос/Ответ Q6;
5.3. Через преобразование БД к предыдущей версии БД (Сервис/Служебные программы/Преобразовать базу данных…) с последующим преобразованием полученного файла к текущей версии БД. Данный способ наиболее эффективен для сжатия БД после внесения изменений в его интерфейсную часть (редактирование форм, отчетов, кода и т.п.), так как иногда удается обнаружить ряд скрытых ошибок, зачастую возникших из-за сбоев самого Access, а так же физически удалить из файла БД логически удаленные объекты.
5.4. Через импорт в пустой (новый) файл БД. По преследуемым целям схож с предыдущим пунктом.
5.5. Через выполнение запуска Access в командной строке со специальными параметрами. Например:
"C:\Program Files\Microsoft Office\Office\Msaccess.exe" "С:\Мои документы\MyDB.mdb" /Excl /Compact
5.6. Через VBA-код, при этом можно использовать несколько различных технологий программного сжатия БД (См. ответы на вопросы Q6 и Q7).
5.7. C помощью утилиты JetComp.exe, которая помимо сжатия умеет также восстанавливать некоторые повреждения .mdb-файлов, которые невозможно исправить средствами MS Access. Справку по параметрам командной строки jetcomp.exe можно получить с помощью
jetcomp.exe /?
Q6. Как сжать БД из VBA-кода моего Проекта?
6.1 - Вариант 1
Можно программно нажать кнопку "Сжать и восстановить базу данных" (меню Сервис/Служебные программы). Этот вариант является наиболее простым, но в то же время наименее гибким из предложенных далее, а также обладает рядом серьезных недостатков (см. ниже):
Public Function AutoCompact()
With CommandBars.Add(, 1, , True)
.Controls.Add 1, 2071, , , True
.Visible = True
.Controls(1).SetFocus
DoEvents
SendKeys "~"
End With
End Function
Достоинства:
# Функция позволяет сжать текущую БД (базу данных, в которой выполняется VBA-код, вызывающий сжатие). Данный вариант является единственным вариантом, позволяющим сжимать текущую не закрытую БД. Не смотря на это, авторы FAQ настоятельно рекомендуют ознакомиться с пунктом 6.4, где рассмотрен более гибкий и надежный вариант реализации сжатия текущей БД;
# Пример сообщает о наличии подобного варианта "программного сжатия БД" и раскрывает его недостатки.
Недостатки:
# Сжатие происходит только текущей БД (базы, в котором выполняется код), таким образом, если БД разделена на Интерфейсную часть и часть с Данными (см. Вопрос/Ответ Q8.3), то будет сжата только Интерфейсная часть в которой выполняется код, а часть с Данными останется несжатой;
# БД после сжатия перезапускается (открывается повторно);
# После последней строки в функции не должно быть никакого дополнительного кода, т.к. это может привести либо к невозможности выполнить сжатие, либо к невозможности выполнить данный дополнительный код;
# Метод не дает программного контроля над выполняемым сжатием, т.е. невозможно отследить (перехватить) и обработать соответствующим способом ошибки, которые могут возникнуть в процессе сжатия БД.
6.2. Вариант 2
Сжатие через DAO-метод DBEngine.CompactDatabase.
Данный метод сжатия наиболее предпочтителен для MDB/MDE форматов БД расположенных "настольно" (без сети) или для сетевых БД, организованных по технологии "Файл-Сервер". Основным условием является доступность (подключение) Библиотеки DAO.
Метод не пригоден для сжатия текущей БД (базы данных, в которой выполняется процедура сжатия), но прекрасно подходит для разделенных БД (см. Пункт 8.3). Для сжатия базы данных, в которой выполняется программный код, инициирующий данное сжатие, можно воспользоваться Вариантом 1 (см пункт 6.1) или, что намного лучше, Отдельным процессом, который и произведет данное сжатие (см. пункт 6.4). Кроме того можно попытаться воспользоваться (на свой страх и риск) вариантом, приведенным в Пункте 7.2
Сжатие производится следующим образом:
- если к БД подключаются несколько пользователей, то перед сжатием БД необходимо убедиться, что ни один пользователь не подключен к базе. В противном случае система сгенерирует перехватываемую ошибку и сжатие БД выполнено не будет;
- сформировать полный путь к сжимаемой БД и полный путь к новому (временному) файлу, в который сжатая БД будет записана;
- сжать БД;
- скопировать новый (появившийся в результате сжатия БД) файл на место старого (копирование под именем, которое принадлежало старому файлу);
- удалить новый (временный) файл.
В качестве примера можно привести два варианта реализации сжатия БД DAO-методом, отличающихся способами манипулирования файлами:
6.2.1. Пример 1
'ФУНКЦИЯ СЖАТИЯ БД DAO-Методом'
' gflngCompactDatabase(...)'
'ВХОДНЫЕ ПАРАМЕТРЫ ФУНКЦИИ:'
' CompactingDBPathAndName - строковый параметр, задающий ПОЛНЫЙ ПУТЬ (путь + имя файла)'
' к сжимаемой БД.'
' BackupBeforeCompactDB - необязательный логический параметр, указывающий на'
' необходимость сделать перед сжатием резервную копию сжимаемой БД (резервная'
' копия выкладывается в файл с именем "ИмяСжимаемогоФайла_Backup"). При'
' отсутствии параметра резервное копирование не производится.'
'ВОЗВРАЩАЕМОЕ ФУНКЦИЕЙ ЗНАЧЕНИЕ:'
' = 0, если сжатие произведено;'
' = Номеру возникшей ошибки, если выполнить сжатие не удалось.'
'ОСОБЕННОСТИ:'
' Для выполнения процедуры сжатия автоматически создается временный файл'
' с именем "ПолныйПуть\ИмяСжимаемогоФайла_Temp".'
' Резервное копирование, выполнение которого определяется параметром "BackupBeforeCompactDB",'
' производится в файл с именем "ПолныйПуть\ИмяСжимаемогоФайла_Backup"), при'
' этом старая копия резерва перезаписывается новой (фактически удаляется).'
' В случае, если сжимаемая БД открыта, то файл БД не будет скопирован (соответствующая'
' ошибка появится в момент копирования БД).'
Public Function gflngCompactDatabase( _
CompactingDBPathAndName As String, _
Optional BackupBeforeCompactDB As Boolean = False) As Long
Dim strTempFile As String
On Error GoTo ErrHandler
'Формируем имя для временного ("принимающего") файла'
strTempFile = Left(CompactingDBPathAndName, (Len(CompactingDBPathAndName) - 4)) & _
"_Temp" & Right(CompactingDBPathAndName, 4)
'Создаем (если надо) резервную копию файла БД перед сжатием'
If BackupBeforeCompactDB = True _
Then FileCopy CompactingDBPathAndName, _
Left(CompactingDBPathAndName, (Len(CompactingDBPathAndName) - 4)) & _
"_Backup" & Right(CompactingDBPathAndName, 4)
'Сжимаем файл БД (с перезаписью сжатого файла в новый файл)'
DBEngine.CompactDatabase CompactingDBPathAndName, strTempFile, dbLangCyrillic
'Перезаписываем сжатый (временный файл) на место несжатого (старого файла)'
FileCopy strTempFile, CompactingDBPathAndName
'Удаляем временный файл'
Kill strTempFile
Exit Function
ErrHandler:
'обрабатываем возможные ошибки'
gflngCompactDatabase = Err.Number
Err.Clear: Exit Function
End Function
Достоинства:
# Для манипулирования файлами (получения имени для временного файла и копирование файла) функция использует стандартные команды Access и не требует наличия на компьютере библиотеки "Microsoft Scripting Runtime" (SCRRUN.DLL), которая (как показывает практика) может отсутствовать;
Недостатки:
# Для получения имени временного файла функция добавляет к имени сжимаемого файла суффикс "_Temp" ("ПолныйПуть\ИмяСжимаемойБД_Temp"), поэтому в случае, если имеется рабочая БД с подобным именем, то по окончании процесса сжатия она будет удалена. Данный недостаток удается избежать либо приняв правило "Не присваивать рабочим файлам БД имени, оканчивающегося на '_Temp'", либо изменив функцию (заменить добавление суффикса "_Temp" на путь и имя файла, специально отведенного для сжатия БД), либо воспользовавшись технологией, приведенной в следующем Примере:
6.2.2. Пример 2
'ФУНКЦИЯ СЖАТИЯ БД DAO-Методом с использованием FileSystemObject для манипулирования файлами '
' gflngCompactDatabaseFSO(...)'
'ВХОДНЫЕ ПАРАМЕТРЫ ФУНКЦИИ:'
' CompactingDBPathAndName - строковый параметр, задающий ПОЛНЫЙ ПУТЬ (путь + имя файла)'
' к сжимаемой БД.'
' BackupBeforeCompactDB - необязательный логический параметр, указывающий на'
' необходимость сделать перед сжатием резервную копию сжимаемой БД (резервная'
' копия выкладывается в файл с именем "ИмяСжимаемогоФайла_Backup"). При'
' отсутствии параметра резервное копирование не производится.'
'ВОЗВРАЩАЕМОЕ ФУНКЦИЕЙ ЗНАЧЕНИЕ:'
' = 0, если сжатие произведено;'
' = Номеру возникшей ошибки, если выполнить сжатие не удалось.'
'ОСОБЕННОСТИ:'
' Резервное копирование, выполнение которого определяется параметром "BackupBeforeCompactDB",'
' производится в файл с именем "ПолныйПуть\ИмяСжимаемогоФайла_Backup"), при'
' этом старая копия резерва перезаписывается новой (фактически удаляется).'
' В случае, если сжимаемая БД открыта, то файл БД будет скопирован и соответствующая'
' ошибка появится только в момент сжатия БД.'
Public Function gflngCompactDatabaseFSO( _
CompactingDBPathAndName As String, _
Optional BackupBeforeCompactDB As Boolean = False) As Long
On Error GoTo ErrHandler
Dim strTempFile As String
Dim objFileSystem As Object
'Создаем объект Файловой системы '
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
'Создаем (если надо) резервную копию файла БД перед сжатием'
If BackupBeforeCompactDB = True _
Then objFileSystem.CopyFile CompactingDBPathAndName, _
Left(CompactingDBPathAndName, (Len(CompactingDBPathAndName) - 4)) & _
"_Backup" & Right(CompactingDBPathAndName, 4)
'Получаем имя для временного ("принимающего") файла'
strTempFile = objFileSystem.GetTempName
'Сжимаем файл БД (с перезаписью сжатого файла в новый файл)'
DBEngine.CompactDatabase CompactingDBPathAndName, strTempFile, dbLangCyrillic
'Перезаписываем сжатый (временный файл) на место несжатого (старого файла)'
objFileSystem.CopyFile strTempFile, CompactingDBPathAndName, True
'Удаляем временный файл'
Kill strTempFile
'Уничтожаем объект Файловой системы'
Set objFileSystem = Nothing
Exit Function
ErrHandler:
'обрабатываем возможные ошибки'
gflngCompactDatabaseFSO = Err.Number
Err.Clear: Exit Function
End Function
Достоинства:
# Для получения имени временного файла функция использует специальный метод объекта FileSystemObject, генерирующий уникальное имя для файла, что исключает возможность перезаписать (фактически удалить) какой-либо существующий файл.
Недостатки:
# Для манипулирования файлами (получения имени для временного файла и копирование файла) функция использует библиотеку "Microsoft Scripting Runtime" (SCRRUN.DLL), которая (как показывает практика) может отсутствовать на компьютере. Данный недостаток удается избежать воспользовавшись предыдущим примером DAO-реализации сжатия БД (пункт 6.2.1).
6.3. Вариант 3
Сжатие через JRO-метод ("расширение" ADO) JRO.JetEngine.
Данный метод сжатия наиболее предпочтителен для ADP/ADE форматов БД, обычно не использующих DAO-библиотеку.
Сжатие производится следующим образом:
- Отключаем от базы данных клиента, с которого запускается процедура сжатия. На данном этапе отладки необходимо убедиться, что все соединения, рекордсеты и т.п. закрыты, т.е. ldb-файл базы данных отсутствует.
- Проверяем, подключен ли кто-нибудь еще к базе данных, что можно сделать двумя вариантами:
1) Проверить наличие .ldb файла
If DIR(полный путь к ldb файлу)<>"" then ...
2) Использовать метод ADO OpenSchema для получения информации о подключенных пользователях (если есть файл рабочей группы) или машинах (в данном примере именно так, где имя машины - 10 символов). Вариант более гибкий, т.к. можно узнать, кто именно блокирует файл (в данном примере проверяем подключен кто-то или нет, но из полученного рекордсета легко можно забрать и реальные имена пользователей/машин).
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim je As New JRO.JetEngine
Dim lngConnected As Long, strCurPCName As String, strBE As String, strBETemp As String
Const adhcUsers = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
strBE = "Полный путь к файлу БД"
strBETemp = "Полный путь к новому (сжатому) файлу БД"
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open "Data Source=" & strBE
'Используем элемент Connection Control (Jet версии не младше 4.0) для'
'запрета подключения новых пользователей.'
cnn.Properties("Jet OLEDB:Connection Control") = 1
Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, SchemaID:=adhcUsers)
' strCurPCName - имя машины, с которой запускается процедура'
strCurPCName = Environ("COMPUTERNAME")
With rst
Do Until .EOF
' Считаем кол-во подключенных машин, исключая машину, с которой
' запущена процедура. Наверняка вычленить реальное имя машины из
' этого поля можно более красивым и правильным способом, мне просто
' лень было, у меня и так работает
'
If Not strCurPCName = Left(.Fields(0), 10) Then lngConnected = lngConnected + 1
.MoveNext
Loop
End With
If lngConnected > 0 Then
' База данных заблокирована другим пользователем->сжать не удастся
' -> выходим любым приемлемым способом, не забыв закрыть rst и cnn'
End If
' База не заблокирована -> переходим к сжатию. Вариант с принудительным
' отключением пользователей здесь не рассматривается, т.к. лично я
' считаю его приемлемым только в аварийных случаях.'
'Закрываем открытый ранее рекордсет'
If Not rst Is Nothing Then rst.Close
Set rst=nothing
- Производим сжатие, при этом должна быть подключена библиотека Microsoft Jet and Replication Objects x.x Library (JRO)
' Проверяем наличие временного файла (возможно остался
' от предыдущей неудачной попытки сжатия) и удаляем его
If Dir(strBETemp) <> "" Then Kill strBETemp
' Закрываем объект cnn, открытый в п.2, иначе сжать данные не получится'
Set cnn=Nothing
' Сжимаем'
je.CompactDatabase "Data Source=" & strBE & ";", _
"Data Source=" & strBETemp & ";"
' Маловероятно, но гипотетически за время сжатия,
' кто-нибудь мог подключиться к файлу БД, поэтому можно еще раз
' проверить наличие подключенных пользователей (см. пример выше),
' если никто не подключен, то удаляем старый файл и
' переименовываем новый сжатый файл. Если кто-то все-таки
' успел открыть БД, то просто выходим, оставляя временный
' сжатый файл. Добавить обработку такой ситуации по вкусу.
Kill strBE
Name strBETemp As strBE
Set je = Nothing
6.4. Вариант 4
Вынесение в отдельный процесс кода, сжимающего БД, что позволяет сжать любые базы данных (в том числе и ту, которая инициировала процесс сжатия БД). Данный вариант является наиболее предпочтительным и гибким решением, позволяющим комбинировать рассмотренные ранее варианты программного сжатия БД, а также производить дополнительное обслуживание баз данных.
Как это работает:
База данных, в которой выполняется код (Клиентская часть или Неразделенная БД), перед закрытием (возможно, не при каждом закрытии: См. Вопрос/Ответ Q4) вызывает другую программу, которая производит все необходимые действия по обслуживанию БД.
В приведенном ниже примере в качестве обслуживающих действий выполняется сжатие Вызвавшей БД и Дополнительной БД, при этом в качестве Сервис-Провайдера используется Access.
Для реализации описанного механизма:
- Создайте новую базу данных с именем "DBServiceProvaider.mdb", в которой создайте форму. Скопируйте в модуль формы следующий код:
Const TimeDelay As Long = 250 'задержка в миллисекундах между неудачными попытками сжатия'
Const MaxIterations As Long = 10 'количество повторов сжатия при неудачных попытках сжатия'
Dim mlngStep As Long 'номер текущего выполняемого шага'
Private Sub Form_Load()
Me.TimerInterval = 1 'запускаем таймер'
End Sub
Private Sub Form_Timer()
Me.TimerInterval = 0 'останавливаем таймер (исключаем повторное событие в момент сжатия)'
Select Case mlngStep
Case 0 'Первый шаг "Обслуживания БД"'
Call gsTryToCompactDB("C:\Тест\MyDB1.mdb")
Case 1 'Второй шаг "Обслуживания БД"'
Call gsTryToCompactDB("C:\Тест\MyDB2.mdb")
'Case N ''N-ный шаг "Обслуживания БД"'
'Действия по обслуживанию БД...'
'Приращиваем значение mlngStep для выполнения следующего шага обслуживания'
'Запускаем таймер для выполнения следующего шага'
Case 2 'заключительный шаг (N+1), заканчивающий выполнение Проекта "Обслуживания БД"'
MsgBox "Обслуживание завершено!", vbInformation
DoCmd.Quit
End Select
End Sub
Private Function gsTryToCompactDB(CompactingDB As String)
Dim lngUserAnswer As Long
Static lngIterationCnt As Long
If gflngCompactDatabase(CompactingDB) = 0 Then 'сжимаем БД и проверяем, получилось ли'
'если сжатие произошло...'
mlngStep = mlngStep + 1
lngIterationCnt = 0
Me.TimerInterval = 1
Else
'если сжатие не произошло (возникла ошибка)...'
If lngIterationCnt <= MaxIterations Then
lngIterationCnt = lngIterationCnt + 1
Me.TimerInterval = TimeDelay
Else
lngIterationCnt = 0
lngUserAnswer = MsgBox( _
"В данный момент не получается сжать файл '" & _
CompactingDB & "'." & vbNewLine & _
"Попытаться сжать данный файл еще раз?", _
vbExclamation + vbYesNoCancel, "Не удается сжать БД")
Select Case lngUserAnswer
Case vbYes
Me.TimerInterval = TimeDelay
Case vbNo
mlngStep = mlngStep + 1
Me.TimerInterval = 1
Case vbCancel
DoCmd.Quit 'Выход из Acces'
End Select
End If
End If
End Function
Обратите внимание, что в процедуре Private Sub Form_Timer() указан путь к двум сжимаемым БД, который необходимо настроить в соответствии с именами и размещением сжимаемых БД на машине.
- сохраните форму и укажите ее имя в качестве автоматически открываемой формы (Меню Сервис\Параметры запуска\Вывод формы или страницы);
- создайте модуль и разместите в нем функцию, приведенную в Пункте 6.2.1 (можно воспользоваться и Примером из пункта 6.2.2, соответствующим образом откорректировав процедуру gsTryToCompactDB());
- подключите DAO-библиотеку (в случае, если еще не подключена), откомпилируйте код и закройте созданную "DBServiceProvaider.mdb";
- откройте базу, из которой будет вызываться созданный нами "Провайдер Обслуживания БД", и добавьте в соответствующее место (например, на кнопку "Выход", или на событие выгрузки главной формы проекта) код:
Shell "C:\Program Files\Microsoft Office\Office\Msaccess.exe " & _
"""C:\Тест\DBServiceProvaider.mdb""", vbMaximizedFocus
DoCmd.Quit
Обратите внимание, что сначала указывается полный путь к исполнимому файлу Access, а затем полный путь к созданной "DBServiceProvaider.mdb", при этом как тот, так и другой необходимо указать в соответствии с расположением файлов на машине.
Следует отметить, что процедура Private Sub Form_Timer() может содержать сколько угодно шагов, направленных на обслуживание БД.
Код приведен в качестве упрощенного примера и призван показать в общих чертах пути реализации приведенной концепции.
7. Что еще можно сделать и почему так делать не надо?
7.1. Можно Программно нажать кнопку "Сжать и восстановить базу данных". Один из наиболее работоспособных вариантов подобного способа сжатия описан в Пункте 6.1. Здесь рассмотрен вариант выполнения аналогичных действий всего лишь одной командой (только для Access 200x):
'Для Англоязычной версии Access'
CommandBars("Menu Bar").Controls("Tools"). _
Controls("Database utilities").Controls("Compact and repair database..."). _
accDoDefaultAction
'Для Русскоязычной версии Access'
CommandBars("Menu Bar").Controls("С&ервис"). _
Controls("&Служебные программы").Controls("С&жать и восстановить базу данных"). _
accDoDefaultAction
Из приведенного примера видно, что код должен соответствовать локализованной версии Access, именно поэтому использовать данный способ не рекомендуется.
Здесь же можно привести несколько видоизмененную версию описываемого способа сжатия БД:
'Обращение к панели инструментов через индексы'
CommandBars(40).Controls(12). _
Controls(7).Controls(2). _
accDoDefaultAction
Данный вариант не зависит от локализации Access и работает в версиях 2000 и XP, но гарантировать невозможно, что в следующих версиях индексы останутся прежними, а значит, что приложение использующее данный код в следующих версиях останется работоспособным.
Кроме рассмотренных особенностей использования данного способа сюда можно отнести и все сказанное в пункте 6.1
7.2. Попытаться сжать Текущую БД (базу данных, в которой выполняется код, инициирующий сжатие) можно на основе метода, описанного в Пункте 6.2.2, при этом следует внести изменения в алгоритм выполнения сжатия:
- скопировать текущую (исполняемую) БД во временный файл;
- сжать временный файл в другой временный файл;
- полученный сжатый временный файл скопировать на место текущего (выполняемого) файла.
Данная методика, так же как и методика, описанная в Пункте 7.1, приведена здесь с целью ознакомления со всеми возможными вариантами сжатия БД и настоятельно не рекомендуется к реальному применению! Причин несколько. Во-первых, не всегда получается подменить (перезаписать) файл открытой БД. Во-вторых, если подмена будет "удачной" (система позволит перезаписать файл открытой БД), то это может обернуться полным разрушением БД. В-третьих, читаем Help: "CompactDatabase Method: Copies and compacts a closed database...", т.е. сжимать можно только закрытую (не используемую) БД.
