你如何编程方式更新在Access中的链接表，这将刷新的数据类型吗？ [英] How do you programmatically update a linked table in Access that will refresh data types too?
问题描述
我正在与我们最近已经更新为使用链接表/ SQL服务器后端遗留Microsoft Access数据库。
我正在做一些改变的数据结构，并想以编程方式更新链接表引用。
不过，与code我用，做什么，我希望将刷新链接表我没有收到更新的数据类型为特定表后。眼下的数据类型是文本，但如果我使用外部数据>链接表管理器，并通过这一过程，他们更新到日期/时间。
（我想有，将开发和生产之间翻转功能，所以我不认为上面的路线是一个选项。）
访问/ VB是不是我的最强技能了，但是从MSDN（的这和这个）好像 tb.Fields.Refresh 是必需的，但它不工作，我会期望。
我是什么做错了吗？
功能RefreshLinkedTables（）作为布尔
昏暗的分贝作为DAO.DATABASE
昏暗结核病作为DAO.TableDef
昏暗的FLD作为DAO.Field
设置DB = CurrentDb
对于每一个结核病db.TableDefs
跳过系统文件。
（〜TMPMID（tb.Name，1,4）＆LT;＆gt;中MSys的和MID（tb.Name，1,4）＆LT;＆GT）然后如果
Debug.Print tb.Name
Debug.Print tb.Connect
如果（中间（tb.Connect，1,5）=ODBC;）。然后
tb.RefreshLink
如果（tb.Name =乔布斯），然后
Debug.Print刷新字段中的数据
tb.Fields.Refresh
结束如果
结束如果
Debug.Print=== === ===
结束如果
db.TableDefs.Refresh
下一个
设置分贝=什么
RefreshLinkedTables = TRUE
退出功能
端功能
有一对夫妇的事情，导致异常行为。
首先，链接表最初使用了 SQL服务器驱动程序，而不是 SQL Server本机客户端10.0 一。所以，当我刷新表，我选择了不正确一（我知道这不是 11.0 ，但认为这是 10.0 ）。
第二个问题是，当访问表被转换了到SQL Server的日期时间字段均设置为DATETIME2（0）（Access 2010的迁移工具使用）。不幸的是， SQL服务器驱动程序不支持这些。
我们希望用户通过Windows身份验证（同样，旧的应用程序，它会希望获得移动到网页或一天的第三方解决方案）来验证，我们知道是这样工作的。
改变SQL Server表使用的日期时间，而不是DATETIME2后，下面的code工作就好了：
选项比较数据库
显式的选项
功能RefreshLinkedTables（）作为布尔
昏暗的分贝作为DAO.DATABASE
昏暗结核病作为DAO.TableDef
设置DB = CurrentDb
对于每一个结核病db.TableDefs
跳过系统文件。
（〜TMPMID（tb.Name，1,4）＆LT;＆gt;中MSys的和MID（tb.Name，1,4）＆LT;＆GT）然后如果
Debug.Print tb.Name
Debug.Print tb.Connect
如果（中间（tb.Connect，1,5）=ODBC;）。然后
我们只需要刷新一个表。
如果（tb.Name =乔布斯），然后
tb.Connect = tb.Connect和放大器;
现场连线
tb.Connect =ODBC;说明= ___; DRIVER = SQL服务器;服务器= ___; APP =的Microsoft Office 2010; DATABASE = ___
开发连接
tb.Connect =ODBC;说明= ___; DRIVER = SQL服务器;服务器= ___; APP =的Microsoft Office 2010; DATABASE = ___
tb.RefreshLink
结束如果
tb.RefreshLink
结束如果
Debug.Print=== === ===
结束如果
下一个
db.TableDefs.Refresh
设置分贝=什么
RefreshLinkedTables = TRUE
退出功能
端功能
逻辑可以清理有点多，但它的作品。
感谢戈德·汤普森为他有用的意见。
I'm working with a legacy Microsoft Access database that we've recently updated to use linked tables/SQL Server backend.
I'm making some changes to the structure of the data and would like to programmatically update the linked table reference.
However, with the code I'm using, after doing what I would expect would refresh the linked tables I'm not getting updated data types for a particular table. Right now the data type is Text, but if I use External Data > Linked Table Manager and go through that process they update to Date/Time.
(I'd like to have a function that will flip between dev and production, so I don't consider the above route to be an option.)
Access/VB isn't my strongest skill anymore, but from MSDN (this and this) it seems like tb.Fields.Refresh is required, but it isn't working as I'd expect.
What am I doing wrong?
Function RefreshLinkedTables() As Boolean
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
For Each tb In db.TableDefs
' Skip system files.
If (Mid(tb.Name, 1, 4) <> "MSys" And Mid(tb.Name, 1, 4) <> "~TMP") Then
Debug.Print tb.Name
Debug.Print tb.Connect
If (Mid(tb.Connect, 1, 5) = "ODBC;") Then
tb.RefreshLink
If (tb.Name = "Jobs") Then
Debug.Print "Refreshing fields data"
tb.Fields.Refresh
End If
End If
Debug.Print "=== === ==="
End If
db.TableDefs.Refresh
Next
Set db = Nothing
RefreshLinkedTables = True
Exit Function
End Function
There were a couple things that resulted in the odd behavior.
First, the linked table had initially used the SQL Server driver, not the SQL Server Native Client 10.0 one. So, when I refreshed the tables I selected the incorrect one (I knew it wasn't 11.0, but thought it was 10.0).
The second issue is that when the Access tables were converted over to SQL Server the datetime fields were setup as datetime2(0) (Access 2010 migration tool was used). Unfortunately the SQL Server driver doesn't support these.
We want users to authenticate via Windows Authentication (again, legacy app that'll hopefully get moved to the web or a 3rd-party solution one-day), and we know it works this way.
After altering the SQL Server tables to use datetime instead of datetime2, the below code worked just fine:
Option Compare Database
Option Explicit
Function RefreshLinkedTables() As Boolean
Dim db As DAO.Database
Dim tb As DAO.TableDef
Set db = CurrentDb
For Each tb In db.TableDefs
' Skip system files.
If (Mid(tb.Name, 1, 4) <> "MSys" And Mid(tb.Name, 1, 4) <> "~TMP") Then
Debug.Print tb.Name
Debug.Print tb.Connect
If (Mid(tb.Connect, 1, 5) = "ODBC;") Then
'We only need to refresh a single table.
If (tb.Name = "Jobs") Then
tb.Connect = tb.Connect & ""
'Live connection
'tb.Connect = "ODBC;Description=___;DRIVER=SQL Server;SERVER=___;APP=Microsoft Office 2010;DATABASE=___"
'Dev connection
'tb.Connect = "ODBC;Description=___;DRIVER=SQL Server;SERVER=___;APP=Microsoft Office 2010;DATABASE=___"
tb.RefreshLink
End If
'tb.RefreshLink
End If
Debug.Print "=== === ==="
End If
Next
db.TableDefs.Refresh
Set db = Nothing
RefreshLinkedTables = True
Exit Function
End Function
Logic could be cleaned up a bit more, but it works.
Thanks to Gord Thompson for his helpful comments.
这篇关于你如何编程方式更新在Access中的链接表，这将刷新的数据类型吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
