链接表ms Access 2010更改连接字符串

问题描述

我正在研究一个现有的MS Access 2010项目，该项目具有到Sql Server数据库的链接表链接.

I am working on a existing MS Access 2010 project that has a linked table link to Sql Server database.

当我将鼠标悬停在链接表上时，我可以看到一个连接字符串'ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user'

When I mouse over to the linked table I can see a connection string 'ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user'

这看起来像是一个无dsn的链接表.

This looks like a dsn-less linked table.

问题

1. 连接字符串位于何处?如何更改它(示例数据库名称)?

1. Where the connect string locate at? How to change it (example database name)?

如何创建类似的无dsn链接表?每当我创建链接表时，Access 2010始终会迫使我选择\创建dsn(文件或计算机).

How can I create a similar dsn-less linked table? Anytime when I create a linked table Access 2010 always force me to choose\create a dsn (file or machine).

推荐答案

要打印所有连接字符串:

To print all connection strings:

Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next

要创建链接表，请执行以下操作:

To create a linked table:

With CurrentDb
    ''If the table does not have a unique index, you will need to create one
    ''if you wish to update.
    Set tdf = .CreateTableDef("LocalName")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.SourceTableName = "TABLE_NAME"
    .TableDefs.Append tdf
    .TableDefs.Refresh
End With

更改链接:

    Set db = CurrentDB
    Set tdf = db.TableDefs("MyTable")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.RefreshLink

这篇关于链接表ms Access 2010更改连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！