Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
 Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
Доброго дня.
Начну из далека. Есть проект accecc, которой я переделываю из *.adp под современный *.accdb. Формы, код, все уже перенес и адаптировал, таблицы с sql привязал через диспетчер связанных таблиц. Однако, есть такая практика, доработки программы отлаживать на тестовой базе sql, и для смены сервера приходится перелинковывать около сотни таблиц через диспетчер связанных таблиц. Из этого следует три проблемы:
1. Надо сделать минимум 100 кликов мышкой в процессе перелинковки (не проблема, но парит)
2. Вторая, вытекающая из первой, среди таблиц встречаются представления, которым требуется принудительно указать первичный ключ. И есть момент когда можно указать "не верный" ключ, просто по запарке (я даже представления не представлю на что это может повлиять)
3. В процессе перелинковки access сбрасывает значения первичных ключей для представлений. Тут я поясню для чего первичный. Львиная доля форм в качестве источника записей используют эти представления и сам access выдает Run-time error '3326': Объект Recordset не является обновляемым, попросту не получается править значения. А вот если назначить первичный ключ, то правки проходят.

Перелинковать можно в ручном режиме и кодом vba поправив таблицу MSysObjects, по при этом первичные ключи "слетают".
Перелинковываю таким образом
+

Public Sub RefreshTablesLinks2
Dim tdf, cDB, strODBC
Dim i As Integer
Set cDB = CurrentDb
Set tdf = cDB.TableDefs
strODBC = "DSN=DSNname;Description=DSNname;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=testdb;"

On Error GoTo Err_handler
For i = 0 To tdf.Count - 1
If tdf(i).Connect <> "" Then
tdf(i).Connect = strODBC
tdf(i).RefreshLink
End If
Next

Err_handler:
'MsgBox Err.Description
Resume Next
End Sub

Собственно вопрос, есть ли способ командовать процессом линковки через vba, как диспетчер связанных таблиц, что бы можно было гибко управлять процессом и вообще достигнуть полной автоматизации?
21 янв 19, 12:25    [21790108]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
AlexDE
Member

Откуда: Bayern, Germany
Сообщений: 236
yoichi730,

Ну во первых в диспетчере есть кнопка Выбрать все.
Во вторых я лично рефрешлинк никогда не пользовал.
Создай таблицу со всеме нужными тебе таблицами и таблицу с возможными серверами или базами.
Отцепай сначала все в цикле, а затем снова прицыпай.
Так ты избавишься от случая, что какая-то таблица сама собой отцепится, как ты ее тогда через рефреш востановишь.
Пока проблем таким образом нисключами ни со связями не было.
21 янв 19, 13:39    [21790216]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
AlexDE

Ну во первых в диспетчере есть кнопка Выбрать все.


Видел, удобно). Не избавляет тот мазахизма тыкать по клавише "ОК" 100 с плюсом раз.
И я же говорю от таких манипуляций у прилинкованных представлений "слетает" первичный ключ, и access переспрашивает где он есть в нем. А спрашивает как минимум потому, что у представления в принципе нету понятия "первичный ключ". И далее, после линкования не удается создать первичный ключ через конструктор таблицы, просто не сохраняет изменения.
21 янв 19, 14:01    [21790248]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
Объявляю благодарность товарищу AlexDE с занесением в личное дело за наводку на мысль с хранением имен таблиц с ключами в отдельной таблице.
Прочтение статьи
https://www.wiseowl.co.uk/blog/s285/linkviewsvba.htm
натолкнуло на некий алгоритм действий. Хоть мне и не нравится использование DAO, но думаю заработает.
21 янв 19, 14:49    [21790303]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2823
yoichi730
...
Перелинковать можно в ручном режиме и кодом vba поправив таблицу MSysObjects, по при этом первичные ключи "слетают".
Перелинковываю таким образом
+

Public Sub RefreshTablesLinks2
Dim tdf, cDB, strODBC
Dim i As Integer
Set cDB = CurrentDb
Set tdf = cDB.TableDefs
strODBC = "DSN=DSNname;Description=DSNname;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=testdb;"

On Error GoTo Err_handler
For i = 0 To tdf.Count - 1
If tdf(i).Connect <> "" Then
tdf(i).Connect = strODBC
tdf(i).RefreshLink
End If
Next

Err_handler:
'MsgBox Err.Description
Resume Next
End Sub

Собственно вопрос, есть ли способ командовать процессом линковки через vba, как диспетчер связанных таблиц, что бы можно было гибко управлять процессом и вообще достигнуть полной автоматизации?


Можно и такой вариант использовать. В строке подключения уберите "DATABASE=testdb", а в самом DSN (у Вас "DSNName" написано) указываете БД по умолчанию как "testdb". А когда надо переадресовать на другую базу, заходите DSN и указываете название боевой базы. И тогда ничего не надо перелинковать вообще, достаточно просто перезапусить Access и всё.
28 янв 19, 07:32    [21795455]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
Тут даже не так). Прямого взаимодействия с MSysObject не идет, эта таблица меняется системой в следствии манипуляций.
DSN это файл, хранимый локально, у каждого пользователя базой его может и не быть. В идеале к нему лучше и не ссылаться, т.к. следить за этим файлом нету возможности. Подозреваю что это можно контролировать через политики, но лучше без него.
Передумал алгоритм линковки тиблиц:
1.Создал локальную таблицу TableNames, где перечисляются все необходимые таблицы. Для необходимых представлений еще и запись присутствует указанием имени поля с "первичным ключем".
2.При перелинковки удаляю все-все залинкованные ранее таблицы, и по таблице TableNames линкую все таблицы по новой, меня только строку подкючения на необходимую.
2.5. Если есть указатель на первичный ключ для таблицы (а точнее для представления), то указываю его по полю, указанному в таблице TableNames
3.Профит.

+
Private Const strConnectionString As String = "ODBC; DRIVER=SQL Server; SERVER=TestServer; DATABASE=testDB; Trusted_Connection=Yes"

Public Sub TablesLinkRefresh(strConnectionString As String)
    Dim strNameInAccess As String
    Dim strNameInServer As String
    Dim strKey As String
    Dim rst As ADODB.Recordset
    Dim tdf As DAO.TableDef
    Dim nTableCount As Integer
    Dim strReportMessage As String
    
    Set rst = New ADODB.Recordset
    rst.Open "TableNames", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
    rst.MoveFirst
    Do While rst.EOF = False
         nTableCount = nTableCount + 1
         strNameInAccess = rst!NameInAccess
         strNameInServer = rst!NameInServer
         If rst!strPKField <> "" Then strKey = rst!strPKField
        
        On Error Resume Next
        DoCmd.RunSQL "drop table " & strNameInAccess
        If Err.Number <> 0 Then
            'MsgBox Err.Description
            strReportMessage = strReportMessage + "error: " & Err.Number & ". " & Err.Description & Chr(13)
            nTableCount = nTableCount - 1
            Err.Clear
        End If
        
        Set tdf = CurrentDb.CreateTableDef(strNameInAccess)     'создание "шаблона" для новой таблицы
        tdf.Connect = strConnectionString
        tdf.SourceTableName = strNameInServer
        CurrentDb.TableDefs.Append tdf  'добавление новой связанной таблицы
        
        On Error Resume Next
        If strKey <> "" Then
            DoCmd.RunSQL "CREATE UNIQUE INDEX UniqueIndex ON " & strNameInAccess & " (" & strKey & ")"     ' создается PK на поле с именем strKey
            If Err.Number = 3371 Then
                strReportMessage = strReportMessage + "error: " & Err.Number & ". " & Err.Description & "(табл.'" & strNameInAccess & "' индекс '" & strKey & "')" & Chr(13)
                Err.Clear
            End If
            strKey = ""
        End If
        rst.MoveNext
    Loop
    Set rst = Nothing
    strReportMessage = strReportMessage + "       Процесс организации связей таблиц завершен. Связано " & nTableCount & " таблиц."
    MsgBox strReportMessage
End Sub


строка strConnectionString определена заранее в константах, и соответственно меняя эту строку при перелинковке я "меня" сервер с ресурсами.

Есть идея как отказаться от локальной таблицы. Если найду возможность считывать информацию прямо с сервера о всех объектах на сервере SQL, то пол дела уже сделано! Ну, на манер той же локальной системной таблицы MSysObject, где уже все указано что надо.
28 янв 19, 13:32    [21795762]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
4d_monster
Member

Откуда: Москва
Сообщений: 1507
yoichi730
Есть идея как отказаться от локальной таблицы. Если найду возможность считывать информацию прямо с сервера о всех объектах на сервере SQL, то пол дела уже сделано! Ну, на манер той же локальной системной таблицы MSysObject, где уже все указано что надо.

Так ?
SELECT * FROM sys.all_objects where is_ms_shipped = 0
28 янв 19, 13:40    [21795779]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
4d_monster, вот так реализовал
select Name , Type from sys.objects where type = 'V' or type = 'U' Order by type

Может и по ваше получится, но мне моего варианта хватает.
29 янв 19, 13:07    [21796756]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
Без уточнения
where is_ms_shipped = 0

дополнительно пролетает таблица "dtproperties", и делаю вывод что ДА!Лучше уточнить выборку)
29 янв 19, 13:17    [21796779]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
AlexDE
Member

Откуда: Bayern, Germany
Сообщений: 236
yoichi730,

Это конечно тоже вариант, но только если тебе все таблицы нужны, у меня база от стороннего проставщика с тысячами таблиц, а моя база просто надстройка которая не достающий функционал дополняет, так за чем мне тысячу таблиц перелинковывать, тут без локальной таблицы не обойтись, да и константы ты похоже в ручную переписываешь, у меня при старте окошко открывается где можно выбрать имя пользователя, пароль и базу. и соответственно, обычным работникам линкуется, то что надо, а разработчику возможность прыгать по базам и полный доступ.
29 янв 19, 15:30    [21796957]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
Не спорю, мне тоже не нравится что все - все подряд подвязывается, но локальную таблицу иметь мне запретили и придумать какой-то алгоритм для выборки только необходимого нету возможности (не хватает мозга), ну просто нету такого однозначного признака. Када таблиц овер 1000 с хвостиком, да и еще надо сортировать "по допуску", то да - локальная таблица это выход
29 янв 19, 15:54    [21797002]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
AlexDE
Member

Откуда: Bayern, Germany
Сообщений: 236
yoichi730,

Ну так загони их в тхт или csv файл, или в ексел табличку и читай от туда, я так понял таблиц не много, так что тормозов не будет, странно что разработчику одну системную таблицу создать не дают.
29 янв 19, 17:11    [21797124]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
yoichi730
Member

Откуда: РФ
Сообщений: 46
AlexDE и все-все-все

Попробую объяснить суть работы пользователей базы. С базой завязано две программы на access, данные таблиц теснено-плотненько так переплетены. Далее пойдет "на пальцах"...Если примерно, то каждая программа подгружает и изменяет до 70-90% данных из общей для них базы SQL. Если сложить оба множество используемых данных этих программ, то общих данных будет 50-60% от всех данных базы. И делить таблицы на "эти для этой", а "эти для другой" в принципе нет смысла. Хранить эти дела в отдельной таблице тоже смысла на самом то деле нету, ибо придется следить за этими записями, поддерживать, корректировать. Проекты до меня тут были на базе access2003 (adp) и после переноса их на mdb суть работы то в общем не поменяется. Как подгружалось все подряд, так и будет. А что касается, рекьюрностей, то права пользователей никто не отменял, вносить правки и читать позволено только тем кому это позволено. Вот такая у нас организация)
И,кстати сказать, хранить локальную базу мне начальник запретил.
30 янв 19, 08:15    [21797416]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
AndrF
Member

Откуда:
Сообщений: 2044
yoichi730
И,кстати сказать, хранить локальную базу мне начальник запретил.


На фиг локальную - неужто с сервера считать трудно, например:

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS

или

SELECT * FROM sys.tables
SELECT * FROM sys.views

И своя функция, которая проверяет действующие линковки и изменяет их при необходимости...
31 янв 19, 13:00    [21798708]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
AndrF
Member

Откуда:
Сообщений: 2044
Набросал простенький примерчик, перелинковывающий при необходимости все таблицы SQL-сервера к MDB-шке.

Как получить свою строку коннекта и добавить вьюшки - это уж сами - оно совсем не сложно...

Const m_sConnect As String = "ODBC;DRIVER=SQL Server;SERVER=MyServer;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=&#192;&#228;&#236;&#232;&#237;&#234;&#224;;LANGUAGE=MyBase"

Public Sub ReLinkAllTables()
    Dim sSqlServer As String, sSqlDatabase As String
    Dim cs As New ADODB.Connection, rs As New ADODB.Recordset, rd As New ADODB.Recordset, rss As New ADODB.Recordset
    Dim db As DAO.Database, tb As DAO.TableDef
    Dim b As Boolean, sTable As String, sNTable As String, mm() As String, m() As String, i As Integer
    
    mm = Split(m_sConnect, ";")
    For i = 0 To UBound(mm)
        m = Split(mm(i), "=")
        Select Case m(0)
            Case "SERVER"
                sSqlServer = m(1)
            Case "DATABASE"
                sSqlDatabase = m(1)
        End Select
    Next i
    
    DoCmd.Hourglass True
    Set db = CurrentDb

    cs.CursorLocation = adUseClient
    cs.Provider = "SQLOLEDB.1"
    cs.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Data Source=""" & sSqlServer & """;Initial Catalog=" & sSqlDatabase
    cs.Open
    
    rs.Fields.Append "SQL_Table", adVarWChar, 128, adFldUnknownUpdatable
    rs.Open
    rd.Fields.Append "MDB_Table", adVarWChar, 128, adFldUnknownUpdatable
    rd.Open
    For Each tb In db.TableDefs
        If Len(tb.Connect) Then
            rd.AddNew
            rd!MDB_Table = tb.Name
            rd.Update
        End If
    Next tb
    
    rss.Open "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE NOT TABLE_NAME='sysdiagrams' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME", cs, adOpenStatic, adLockReadOnly
    Do Until rss.EOF
        sTable = rss!TABLE_SCHEMA & "." & rss!TABLE_NAME
        sNTable = rss!TABLE_SCHEMA & "_" & rss!TABLE_NAME
        rd.Find "MDB_Table = '" & sNTable & "'", , , 1: b = True
        If Not rd.EOF Then
            Set tb = db.TableDefs(sNTable)
            b = tb.Connect <> m_sConnect Or tb.SourceTableName <> sTable
            If b Then db.TableDefs.Delete sNTable
        End If
        If b Then
            Set tb = db.CreateTableDef(sNTable)
            tb.Connect = m_sConnect
            tb.SourceTableName = sTable
            db.TableDefs.Append tb
        End If
        
        rs.AddNew
        rs!SQL_Table = sNTable
        rs.Update
        
        rss.MoveNext
    Loop
    If rd.RecordCount Then
        rd.MoveFirst
        Do Until rd.EOF
            rs.Find "SQL_Table='" & rd!MDB_Table & "'", , , 1
            If rs.EOF Then db.TableDefs.Delete rd!MDB_Table
            rd.MoveNext
        Loop
    End If

    db.TableDefs.Refresh
    DoCmd.Hourglass False
End Sub
31 янв 19, 15:09    [21798864]     Ответить | Цитировать Сообщить модератору
 Re: Связь таблиц MSSQL с базой Access без использования "диспетчера связанных таблиц"  [new]
AndrF
Member

Откуда:
Сообщений: 2044
Вверху ошибка - правильный вариант моего варианта строки подключения:

Const m_sConnect As String = "ODBC;DRIVER=SQL Server;SERVER=MyServer;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MyBase;LANGUAGE=русский"
31 янв 19, 15:13    [21798872]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить