|
链接表实际上很类似视图(查询),本身没有数据,而是把目标数据库中的数据表映射到当前数据库中,因此,链接表是否能正常使用最关键的就是链接表的配制信息。
一般链接表的属性包括:
1、数据库类型,如:ODBC、ACCESS...
2、数据库,如:...
3、用户名和密码
其中,2 中数据库是很灵活的。像你说的分拆出来的后台数据库,那么,数据库类型,就是ACCESS,而数据库则是后台数据库文件的存储路径,如:本地 c:\Data\backend.mdb,远程\\DataSvr\Data\backend.mdb。也就远程是共享文件夹,那么在使用以前,你必须要能正常访问这个文件夹,并具有对这个文件中的文件有读写的操作权限,而这个东西的设置不属于Access的东西,因此,在你使用前必须达到上述条件才能使用。
重新设置链接表的属性,你需要用到 DAO 中的 TableDef.RefreshLink 方法。
示例:- Sub RefreshLinkX()
- Dim dbsCurrent As Database
- Dim tdfLinked As TableDef
- ' Open a database to which a linked table can be
- ' appended.
- Set dbsCurrent = OpenDatabase("DB1.mdb")
- ' Create a linked table that points to a Microsoft
- ' SQL Server database.
- Set tdfLinked = _
- dbsCurrent.CreateTableDef("AuthorsTable")
-
- ' Note: The DSN referenced below must be configured to
- ' use Microsoft Windows NT Authentication Mode to
- ' authorize user access to the Microsoft SQL Server.
- tdfLinked.Connect = _
- "ODBC;DATABASE=pubs;DSN=Publishers"
- tdfLinked.SourceTableName = "authors"
- dbsCurrent.TableDefs.Append tdfLinked
- ' Display contents of linked table.
- Debug.Print _
- "Data from linked table connected to first source:"
- RefreshLinkOutput dbsCurrent
- ' Change connection information for linked table and
- ' refresh the connection in order to make the new data
- ' available.
-
- ' Note: The DSN referenced below must be configured to
- ' use Microsoft Windows NT Authentication Mode to
- ' authorize user access to the Microsoft SQL Server.
- tdfLinked.Connect = _
- "ODBC;DATABASE=pubs;DSN=NewPublishers"
- tdfLinked.RefreshLink
- ' Display contents of linked table.
- Debug.Print _
- "Data from linked table connected to second source:"
- RefreshLinkOutput dbsCurrent
- ' Delete linked table because this is a demonstration.
- dbsCurrent.TableDefs.Delete tdfLinked.Name
- dbsCurrent.Close
- End Sub
- Sub RefreshLinkOutput(dbsTemp As Database)
- Dim rstRemote As Recordset
- Dim intCount As Integer
- ' Open linked table.
- Set rstRemote = _
- dbsTemp.OpenRecordset("AuthorsTable")
- intCount = 0
- ' Enumerate Recordset object, but stop at 50 records.
- With rstRemote
- Do While Not .EOF And intCount < 50
- Debug.Print , .Fields(0), .Fields(1)
- intCount = intCount + 1
- .MoveNext
- Loop
- If Not .EOF Then Debug.Print , "[more records]"
- .Close
- End With
- End Sub
复制代码 上述是帮助中链接到SQL Server数据库的例子
你可以在本论坛上搜索一下“链接表”,可以找到很多这个方面的例程。
如:zhengjialon做的自动链接表,我等菜鸟的必备利器! |
|