时间戳

首页 > 解决方案 > 将链接到 SQL Server 上的表的访问表，当访问数据更改时更新

sql-server - 将链接到 SQL Server 上的表的访问表，当访问数据更改时更新

问题描述

我有一个 Access 数据库，它链接多个 Excel 文件并连接到 Google Analytics。

我创建了一个 Unison Query 以将所有文件和表放入一个表中。

我需要这个表可以被多个其他用户访问和刷新，通常是通过一个 excel powerpivot 表。我设置了一个 Azure SQL 服务器并执行了导出向导来移动数据，这可以工作，但我无法用新数据更新它（我需要每天都这样做）。简单地共享访问数据库是行不通的，因为不是每个人都有相同的文件结构。

如何让 Access 中的表链接到 SQL，所以当它在 Access 中更新时，SQL 中的表会更新？

标签： sql-serverexcelms-accessazure-sql-database

解决方案

手动将表导出到 Azure SQL Server 后，只需将该表链接到 MS Access 应用程序，然后手动或通过代码自动运行操作查询以进行每日更新。

  1. 链接的 SQL Server 表

    这是 MSAccess.exe 的一个众所周知的功能，能够连接到几乎任何符合 ODBC/OLEBD 的数据源，从 Oracle 和 SQL Server 到 Quickbooks 和 Salesforce。要求包括：

    • Azure SQL Server ODBC 驱动程序或以前创建的数据源名称 (DSN)
    • 确保连接的用户具有读/写表权限（在服务器上分配）
    • 完成初始设置向导（请参阅外部数据下的 ODBC 数据库选项）


    在此处输入图像描述

  2. 操作查询

    SQL （另存为MS Access存储查询，可双击对象手动运行）

    INSERT和子句中的列数应该相等，SELECT并且每个相应的类型应该匹配（省略自动编号）。

    INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...)
SELECT e.Col1, e.Col2, e.Col3, ...
FROM myExcelTable AS e

    或者，如果您需要UPDATE在两个表源之间运行需要匹配列的查询。

    UPDATE mySQLServerTable s
INNER JOIN myExceltable e ON e.MatchColumn = s.MatchColumn
SET s.Col1 = e.Col1, s.Col2 = e.Col2, s.Col3 = e.Col3, ...

    VBA （在 MS Access 中自动运行存储的查询）

    ' WARNS USER OF NUMEBR OF ROWS TO BE INSERTED/UPDATED
DoCmd.OpenQuery "myActionQuery"

' DOES NOT WARN USER
CurrentDb.Execute "myActionQuery", dbFailOnError

相反，您可以运行 SQL Server 查询，该查询使用OPENROWSET从外部 MS Access 源插入到您的表中。这假定可以从 SQL Server 所在的位置访问数据库。

INSERT INTO INSERT INTO mySQLServerTable (Col1, Col2, Col3, ...)
SELECT e.Col1, e.Col2, e.Col3, ... 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'C:\path\to\myDatabase.accdb', 'admin', '', myExcelTable) AS e

推荐阅读