MyTetra Share
Делитесь знаниями!
FAQ: Проверка наличия таблицы в базе данных.
Время создания: 17.03.2019 00:15
Раздел: !Закладки - VBA - Access

FAQ: Проверка наличия таблицы в базе данных.

(обращений: 20516 с 16.01.2007)

Разделы:  Таблицы, Полезные функции, ЧаВо (FAQ)

 

Описание: Пример функций, которые проверяют наличие таблиц в базах данных, как в текущей, так и в удаленной.

Автор: Дмитрий Сонных (AKA Joss)

Добавил на сайт: Joss 16.01.2007

Периодически на форуме появляется вопрос типа: «Как проверить (определить) наличие конкретной таблицы в текущей (удаленной) базе данных?»

Я решил выложить пару функций, чтобы помочь решить эту проблему.
Итак, приступим.

Проверка наличия таблицы в указанной базе данных. Метод ADO.

Не забудьте для использования метода ADO добавить в References
Microsoft ADO Ext. for DDL and Security и Microsoft ActiveX Data Objects Library. Их версии зависят от версий Microsoft Access и обновлений, установленных на компьютере.

'---------------------------------------------------------------------------------------

' Procedure : IsTableADO, Release 2.0

' DateTime : 11.01.2007 16:59

' Author : DSonnyh

' Purpose : Проверка наличия таблицы в указанной базе данных

'---------------------------------------------------------------------------------------

'

Public Function IsTableADO(Name As String, strBase As String) As Integer

' проверка на наличие определенной таблицы. Метод ADO

' Name - наименование таблицы

' strBase - полный путь к файлу базы


Dim cnn As ADODB.Connection

Dim cat As ADOX.Catalog

Dim tdf As ADOX.Table

On Error GoTo IsTable_Error_ADO


Set cnn = New ADODB.Connection

Set cat = New ADOX.Catalog

Dim strProvider As String

' строка подклюбчения

strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=;" _

& "Data Source=" & strBase & ";Mode=Share Deny None;" _

& "Extended Properties=" & "''" & ";Jet OLEDB:System database=;Jet O"

If strBase = CurrentProject.FullName Then

cat.ActiveConnection = CurrentProject.Connection

Else

cnn.ConnectionString = strProvider

cnn.Open

cat.ActiveConnection = cnn.ConnectionString

End If

IsTableADO = 0

For Each tdf In cat.Tables

If tdf.Name = Name Then

IsTableADO = 1

Exit For 'Function

End If

Next tdf

Set tdf = Nothing

Set cat = Nothing

If strBase <> CurrentProject.FullName Then

cnn.Close

End If

Set cnn = Nothing


On Error GoTo 0

Exit_IsTable_ADO:

Exit Function


IsTable_Error_ADO:


MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") в процедуре IsTableADO"

Resume Exit_IsTable_ADO


End Function

И вторая функция. Предназначена для тех, кто до сих пор пользуется Access 97, или предпочитает метод DAO.

Проверка наличия таблицы в указанной базе данных. Метод DAO.

Не забудьте добавить в References ссылку на
Microsoft DAO Object Library. Обычно она бывает версии 3.51 (A97) и 3.60 (A2000+).

'---------------------------------------------------------------------------------------

' Procedure : IsTableDAO, Release 2.0

' DateTime : 15.01.2007 10:39

' Author : DSonnyh

' Purpose : Проверка наличия таблицы в указанной базе данных

'---------------------------------------------------------------------------------------

'

Public Function IsTableDAO(Name As String, strBase As String) As Integer

' проверка на наличие определенной таблицы

' Name - наименование таблицы

' strBase - полный путь к файлу базы

On Error GoTo IsTableDAO_Error

Dim dbs As Database, tdf As TableDef

If strBase = CurrentDb.Name Then

Set dbs = CurrentDb

Else

Set dbs = DBEngine.Workspaces(0).OpenDatabase(strBase)

End If

IsTableDAO = 0

For Each tdf In dbs.TableDefs

If tdf.Name = Name Then

IsTableDAO = 1

Exit For

End If

Next tdf

Set tdf = Nothing

dbs.Close

Set dbs = Nothing


On Error GoTo 0

Exit_IsTableDAO:

Exit Function


IsTableDAO_Error:


MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") в процедуре IsTableDAO"

Resume Exit_IsTableDAO


End Function


И последняя функция. Я её написал для совместимости со своими старыми версиями и для того, чтобы облегчить переход с DAO на ADO (Если возникнет такое желание).

'---------------------------------------------------------------------------------------

' Procedure : IsTable, Release 2.0

' DateTime : 15.01.2007 13:28

' Author : DSonnyh

' Purpose : Вариант вызова фукции, используемый для совместимости со старыми версиями

'---------------------------------------------------------------------------------------

'

Public Function IsTable(Name As String, Optional strBase As Variant, _

Optional intMetod As Variant = 0) As Integer


' проверка на наличие определенной таблицы

' Name - наименование таблицы

' strBase - полный путь к файлу базы (опционально), по умолчанию - текущая

' intMetod - используемый метод 0 - DAO или 1 - ADO (опционально), по умолчанию - DAO


On Error GoTo IsTable_Error

Dim strBaseName As String, intMetodA As Integer

If IsMissing(strBase) Then

If intMetod = 0 Then

strBaseName = CurrentDb.Name

IsTable = IsTableDAO(Name, strBaseName)

Else

strBaseName = CurrentProject.FullName

IsTable = IsTableADO(Name, strBaseName)

End If

Else

strBaseName = strBase

If intMetod = 0 Then

IsTable = IsTableDAO(Name, strBaseName)

Else

IsTable = IsTableADO(Name, strBaseName)

End If

End If


On Error GoTo 0

Exit_IsTable:

Exit Function


IsTable_Error:


MsgBox "Ошибка " & Err.Number & " (" & Err.Description & ") в процедуре IsTable " _

& "в Module modConnect"

Resume Exit_IsTable


End Function


Замеченные недостатки и особенности функций.
Определение наличия таблицы происходит при переборе коллекции
TableDefs, поэтому скорость их работы невысока и зависит от числа элементов коллекции. У ADO в коллекцию входят все объекты базы: таблицы, запросы, макросы, формы, отчеты и т.д.
Создание коллекции
TableDefs для удаленной базы методом DAO происходит несколько медленнее, чем методом ADO.

Из справки для DAO:
«…Для доступа к другой базе данных в то время, когда текущая база данных открыта в окне Microsoft Access, следует использовать метод
OpenDatabase объекта Workspace. Метод OpenDatabase не открывает вторую базу данных в окне Microsoft Access, а возвращает переменную типа Database, представляющую вторую базу данных.
Примечание. В предыдущей версии Microsoft Access для получения указателя на текущую базу данных можно было использовать следующий синтаксис: DBEngine.Workspaces(0).Databases(0) или DBEngine(0)(0). В Microsoft Access 2000 для этой цели следует использовать метод CurrentDb. Метод CurrentDb создает новый экземпляр текущей базы данных, а переменная DBEngine(0)(0) ссылается на открытую копию текущей базы данных. Метод CurrentDb позволяет создавать несколько переменных типа Database, ссылающихся на текущую базу данных. Синтаксис вида DBEngine(0)(0) по-прежнему поддерживается, но во избежание конфликтов в многопользовательских базах данных использовать его не рекомендуется.»
Функции не различают прилинкованные таблицы и таблицы, находящиеся в базе.
Если Вы хотите проверить на нахождение в базе данных целой группы таблиц, то для этого лучше написать отдельную функцию
(я сейчас как раз такую функцию отлаживаю, и как появится возможность, то выложу).

Статьи с похожим содержанием:
Форма для поиска объектов в базе данных.
Получение списка таблиц другой Базы данных (MSA 2000)

[Back]


Текущий рейтинг:
0 из 5 (проголосовало:0).

Здравствуйте!
Для участия в рейтинге необходимо залогиниться на сайт.
Это сделано для того, чтобы более точно производить оценку статей (чтобы одному и тому же человеку было труднее голосовать несколько раз, портя тем самым статистику.
Эта процедура очень быстрая и, надеюсь, Вас не затруднит :).
Все мысли по поводу работы сайта всегда можно высказать на
форуме!
Вход на сайт


Обсуждение статьи:   

без темы

NeAs

  

Полезная статья и хорошо изложена, что, впрочем, характерно для Joss.

Для определения наличия таблицы в текущей базе
можно, также, использовать следующую функцию

Public Function IsTable(ZP_NameTable As String) As Long

' проверка на наличие определенной таблицы

' ZP_NameTable - наименование таблицы


Application.Echo False 'отключаем обновление экрана

On Error Resume Next

DoCmd.OpenTable ZP_NameTable

If Err.Number = 0 Then

DoCmd.Close acTable, ZP_NameTable

Else

IsTable = Err.Number

End If

Application.Echo True

End Function 'IsTable



P.S.
Универсализм, по моему мнению, зачастую приводит к снижению производительности программы (хотя программисту конечно легче).
Например, я провел тест предложенных процедур на своей А97 и получил для 1000 таблиц такие результаты относительного времени поиска отсутствующей таблицы:
1 IsTable (NeAs)__11221276
2 IsTableDAO____11221387
3 IsTable (Joss)__11221497
Это все микросекунды, но все же.

И еще.
Строка
strBaseName = CurrentProject.FullName
в IsTable (Joss) для А97, естественно, дает ошибку при компиляции.

16.01.2007 15:00


Re:без темы

АлексейЕ

  

Работает от msa 97 до 2007
Проверяет наличие таб. как внутри базы, так и во внешней,
как в mdb формате, так и в dbf и др.
Дает в меру интеллектуальный ответ причины не нахождения.

Public Function ExistsTable(strNameTable As String, Optional ByRef strResult As String = "") As Byte

On Error Resume Next


DoCmd.SetWarnings False

DoCmd.RunSQL "Insert INTO " & strNameTable & " SELECT * FROM " & strNameTable & " WHERE 0=1"

Select Case Err.Number

Case 0

ExistsTable = 0

strResult = "Таблица существует"

Case 3078

ExistsTable = 1

strResult = "Таблица отсутствует"

Case 3024, 3011

ExistsTable = 2

strResult = "Не найден файл"

Case 3044

ExistsTable = 3

strResult = "Указан ошибочный путь"

Case 3343

ExistsTable = 4

strResult = "Нераспознаваемый формат базы данных"

Case 3321

ExistsTable = 5

strResult = "Не задана база данных в строке подключения"

Case Else

ExistsTable = 6

strResult = "Неизвестная ошибка"

End Select

Err.Clear

DoCmd.SetWarnings True

End Function



Проверять так

Public Sub Test()

Dim qq As String

Debug.Print ExistsTable("Код_сотрудников IN 'e:\Проект2007.adp'", qq); Tab; qq

Debug.Print

Debug.Print ExistsTable("Код_сотрудников IN 'e:\11\db120000031.mdb'", qq); Tab; qq

Debug.Print ExistsTable("Код_сотрудников IN 'e:\db120000031.mdb'", qq); Tab; qq

Debug.Print ExistsTable("Код_сотрудников1 IN 'e:\db12000003.mdb'", qq); Tab; qq

Debug.Print ExistsTable("Код_сотрудников IN 'e:\db12000003.mdb'", qq); Tab; qq

Debug.Print

Debug.Print ExistsTable("PERSONS IN """" [dBASE IV;DATABASE=]", qq); Tab; qq

Debug.Print ExistsTable("PERSONS IN """" [dBASE IV;DATABASE=e:\Temp\1]", qq); Tab; qq

Debug.Print ExistsTable("PERSONS1 IN """" [dBASE IV;DATABASE=e:\Temp]", qq); Tab; qq

Debug.Print ExistsTable("PERSONS IN """" [dBase IV;DATABASE=E:\Temp]", qq); Tab; qq

Debug.Print

Debug.Print ExistsTable("Таблица2", qq); Tab; qq

Debug.Print ExistsTable("Таблица21", qq); Tab; qq

End Sub


4 Нераспознаваемый формат базы данных


3 Указан ошибочный путь

2 Не найден файл

1 Таблица отсутствует

0 Таблица существует


5 Не задана база данных в строке подключения

3 Указан ошибочный путь

2 Не найден файл

0 Таблица существует


0 Таблица существует

1 Таблица отсутствует



Сделал только что, на коленке и распространяется по принципу "КАК ЕСТЬ". :)

16.01.2007 17:55


Замеченные неточности, исправления и добавления.

Joss

  

Опечатка "...Обычно она бывает версии 3.51 (A97) и 4.0 (A2000+)." Версии 4.0 библиотеки DAO пока нет (и возможно не будет:)), Версия DAO на текущий день 3.60, а 4.0 - это версия Jet.

to NeAs

"Строка

strBaseName = CurrentProject.FullName

в IsTable (Joss) для А97, естественно, дает ошибку при компиляции."

Её можно заменить на

strBaseName = CurrentDb.Name

Вообще-то я эту подпрограмму предлагал использовать в А2000 и выше, когда перетаскивал старые проекты с А97 на новые версии.

А вот функция
ExistsTable от АлексейЕ построена на обработке ошибок. Просто и эффективно. Может у кого-нибудь тоже есть свои решения этой задачи, которые здесь не упоминались?


16.01.2007 18:26


List the Tables in a Database (Daniel Pineault)

Joss

  

Статья Даниэля Пино "Список таблиц в базе данных", взята здесь

Следующая функция будет перечислять таблицы в базе данных Access. Опция, использующая входную переменную bShowSys, служит для включения или исключения системных таблиц из возвращаемого списка.

'---------------------------------------------------------------------------------------

' Procedure : listTables

' Author : CARDA Consultants Inc.

' Website : http://www.cardaconsultants.com

' Purpose : вернуть список всех таблиц в базе данных

' Copyright : The following code may be used as you please, but may not be resold, as

' long as the header (Author, Website & Copyright) remains with the code.

'

' Входные переменные:

' ~~~~~~~~~~~~~~~~

' bShowSys - True/False включать или нет системные таблицы в список

'

' История изменений:

' Rev Date(yyyy/mm/dd) Description

' **************************************************************************************

' 1 2008-June-01 Initial Release

'---------------------------------------------------------------------------------------

Function listTables(bShowSys As Boolean) As String

On Error GoTo listTables_Error

Dim db As DAO.Database

Dim td As DAO.TableDefs

Set db = CurrentDb()

Set td = db.TableDefs

For Each t In td ' цикл перебора всех таблиц

If Left(t.Name, 4) = "MSys" And bShowSys = False Then GoTo Continue

If Left(t.Name, 1) = "~" And bShowSys = False Then GoTo Continue

Debug.Print t.Name

Continue:

Next

Set td = Nothing

Set db = Nothing

If Err.Number = 0 Then Exit Function

listTables_Error:

MsgBox "MS Access обнаружил следующую ошибку" & vbCrLf & vbCrLf & "Номер ошибки: " & _

Err.Number & vbCrLf & "Источник ошибки: listTables" & vbCrLf & "Описание ошибки: " & _

Err.Description, vbCritical, "Произошла ошибка!"

Exit Function

End Function

27.09.2017 12:44 (последнее изменение - 27.09.2017 12:48) 


от Hauder

Joss

  

Способ от Hauder

Возвращает: true - существует, false - нет.

Public Function TableExist(strTableName As String) As Boolean

Dim db As DAO.Database

Dim i As Long


TableExist = False

Set db = CurrentDb

With db

For i = 0 To .TableDefs.Count - 1

If .TableDefs(i).name = strTableName Then

TableExist = True

Exit For

End If

Next i

End With

Set db = Nothing

End Function

22.08.2018 12:10

Так же в этом разделе:
 
MyTetra Share v.0.53
Яндекс индекс цитирования