Резюме: У меня есть локальная база данных Access, которая содержит заказы и обновляется ежедневно утром автоматической процедурой VBA, запускаемой задачей Windows. Более подробно, каждый день будут добавляться новые заказы, а существующие заказы будут обновляться новой информацией с использованием локального .csv файла, который будет удален в среду общей папки в 5:30 утра.

Теперь у меня есть SQL Server, который должен быть обновлен и точная копия локальной таблицы, в лучшем случае сразу после локального обновления. Это необходимо, потому что я использую SQL Server в качестве серверной части для инструмента автоматической рассылки заказов, который в будущем будет использоваться примерно 50 сотрудниками.

Что я пробовал: я запускаю процедуру локального обновления полностью в локальной таблице, которая является и всегда будет локальной, чтобы обеспечить плавный процесс обновления. Чтобы обновить таблицу SQL, я попытался реализовать это, создав связанную таблицу, однако наборы данных не передаются на SQL Server. Это странно, потому что, когда я вручную изменяю отдельные поля в связанной таблице, изменения немедленно отражаются в таблице SQL Server. На сервере SQL не обновляются только процессы на основе VBA.

Код:

 Sub Sync_SQL()

Dim myDB As DAO.Database
Set myDB = CurrentDb

Dim strSQL As String

Dim qdf As QueryDef

' Erstellt einen temporären Query mit einem ODBC-Connection String zur Verbindung mit dem SQL Server
Set qdf = CurrentDb.CreateQueryDef("SyncDB")
qdf.Connect = "ODBC;Driver={SQL Server};server=XX.X.XXX.XX;database=OPM;uid=USID;pwd=PWD;"

' Löscht alle noch offenen Aufträge aus dem SQL-Server
qdf.SQL = "DELETE FROM [OPM].[dbo].[ReiseMaster] WHERE NOT [Name of Employee] = '---------';"
qdf.ReturnsRecords = False

' Führt den Query aus und misachtet dabei jegliche Dialogfelder
DoCmd.SetWarnings False
DoCmd.OpenQuery "SyncDB"
DoCmd.SetWarnings True

' Fügt aktuell offene Aufträge in den SQL-Server ein
myDB.Execute "INSERT INTO [dbo_ReiseMaster] SELECT * FROM [ReiseMaster] WHERE NOT [Name of Employee] = '---------';"

' Leert den Statistik-Table (sollte in Zukunft anders funktionieren)
qdf.SQL = "DELETE FROM [OPM].[dbo].[Statistics];"
qdf.ReturnsRecords = False

' Führt den Query aus und misachtet dabei jegliche Dialogfelder
DoCmd.SetWarnings False
DoCmd.OpenQuery "SyncDB"
DoCmd.SetWarnings True

' Lädt den Statistik-Table mit den neuen Werten hoch (sollte in Zukunft nur noch für neue Aufträge erfolgen, um Performance auch nach längerer Laufzeit der Datenbank gering zu halten)
myDB.Execute "INSERT INTO [dbo_Statistics] SELECT * FROM [Statistics];"

' Setzt die aktive Verbindung zurück und löscht den temporären Query
Set qdf = Nothing
myDB.QueryDefs.Delete "SyncDB"
myDB.QueryDefs.Refresh

End Sub
  

Результат: В частности, следующие изменения в связанной таблице не будут отражены в SQL Server:

 myDB.Execute "INSERT INTO [dbo_ReiseMaster] SELECT * FROM [ReiseMaster] WHERE NOT [Name of Employee] = '---------';"
  

Хотя связанная таблица dbo_ReiseMaster в файле Access содержит обновленные значения, они не загружаются на SQL Server.

Как я могу гарантировать, что локальная таблица Access (в каких бы вариантах я ни манипулировал данными) всегда будет передавать изменения на SQL Server?

Вы не можете использовать один объект подключения, а затем «надеяться», что access знает, какую таблицу использовать. Вы выполняете sql для одного объекта connection, поэтому таблицы insert и source будут находиться в этом одном удаленном соединении. Вы не можете этого сделать.

Что бы произошло, если бы исходная таблица существовала на стороне sql Server?

Однако сбросьте все эти данные о соединении с запросом. Просто свяжите таблицы с сервером.

Теперь вы можете писать и использовать стандартный sql из access и использовать обе таблицы так, как если бы они были локальными.

Итак, вы можете использовать: dim strSQL как строку

 strSQL = "INSERT INTO [ReiseMasterLinked] " amp; _
        "SELECT * FROM [ReiseMaster] WHERE NOT [Name of Employee] = '---------';"

currentdb.Execute strSQL
  

Таким образом, вы можете использовать sql для двух таблиц, даже если одна из них связана с sql Server, но вы должны использовать связанные таблицы. Вы не имеете дело с объектом connection, не видите и не используете его, поскольку он всегда будет применяться к одному объекту connection. Однако Access является ОЧЕНЬ особенным в том смысле, что вы можете написать sql, который включает связанные таблицы и локальные таблицы, и это разрешит соединение, на котором основаны две таблицы (или фактически, что одна таблица не связана, является локальной, а другая — связанной таблицей.

Редактировать

Вот еще один пример. Но ОБРАТИТЕ ВНИМАНИЕ, как я прокомментировал .Выполнить команду и заменить ее docmd.RunSQL.

 Sub AppendTest()

  Dim strSQL     As String
  
  strSQL = "INSERT INTO tblHotelsSQL " amp; _
           "SELECT * FROM tblHotelsLOCAL WHERE NOT tblHotelsLOCAL.HotelName = '-----'"

  'CurrentDb.Execute strSQL, dbFailOnError
  DoCmd.RunSQL strSQL
  
End Sub
  

Это выполняется медленнее и завершает команду транзакцией, поэтому вы можете ответить «да» или «нет» на приглашение, но выдает ГОРАЗДО более подробное сообщение об ошибке, объясняющее, почему запрос не удалось выполнить или не выполнялся. И снова обратите внимание, что строки подключения не требуются. Чтобы все вышеперечисленное сработало, можно быстро дважды щелкнуть связанную таблицу с SQL Server, чтобы убедиться, что все связанные таблицы работают нормально (и если в ней отображаются данные — возможно, попробуйте отредактировать одну строку — отключите, и это обеспечит чтение / запись связанной таблицы.