如何获取链接表DSN详细信息 [英] How to get the link table DSN detail

问题描述

嗨。
。美好的一天。

我有一个Microsoft Access应用程序，我使用DSN使用链接表（SQL Server数据库）。是否可以使所有表格链接详细信息并使用新的DSN链接刷新上一个链接。

感谢

---

Md。 Marufuzzaman

解决方案

是的，您可以使用以下方法：

''' <摘要>

'''刷新MS Access链接

< span style ="font-family：'Courier New'; font-size：10pt"> ''' < /总结>

''' < param name =" ; dbs">< / param>

''' < param name =" strLocalTableName">< / param>

''' < param name =" NewPathname">< / param>

''' < remarks>< / remarks>

公共 Sub RelinkTables（ ByVal dbs As dao.Database，< span style ="color：blue"> ByVal strLocalTableName 作为 字符串，_

ByVal NewPathname 作为 字符串）

Dim tbl As dao。 TableDef

尝试

tbl = dbs。 TableDefs（strLocalTableName）

如果 tbl.SourceTableName<> "" 然后 '如果表源是除了基表

tbl.Connect = ""

'Tdf.SourceTableName = strSourceTableName

tbl.Connect = " ;; DATABASE =" & NewPathname '设置新来源

tbl.RefreshLink（） '刷新链接

结束 如果

Catch ex 作为除外离子

投掷 ex

最后

tbl = 没什么

结束 尝试

结束 Sub

< span style ="font-family：'Courier New';颜色：蓝色; font-size：10pt">

' '' < summary>

' ''创建一个新的MS Access链接

''' < / summary>

< span style ="color：green">''' < param name =" con">< / param>

''' < param name =" sLinkFromDB">< ; / param>

''' < param name =" sLinkToDB">< / param>

''' < param name =" sLinkToTable">< / param>

''' < param name =" sNewLinkTableName"> ;< / param>

''' < returns>< / returns>

''' < remarks>< / remarks>

功能 AccessLinkToTable（ ByVal con 作为 ADODB.Connection， ByVal sLinkFromDB As 字符串，_

ByVal sLinkToDB 作为 字符串， ByVal sNewLinkTableName 作为 字符串，_

ByVal sLinkToTable As String ）作为 布尔

Dim CatDB 作为 ADOX.Catalog

Dim tbl 作为 ADOX.Table

尝试

如果 Len（Dir

（sLinkFromDB））> 0 和 Len（Dir

（sLinkToDB））> 0 然后

< span style ="font-family：'Courier New'; font-size：10pt"> CatDB = 新 ADOX。目录

CatDB.ActiveConnection = con

tbl = 新 ADOX.Table

带 tbl

'命名新表

如果 Len（sNewLinkTableName）那么

。 Name = sNewLinkTableName

其他

.Name = sLinkToTable

结束 如果

'将ParentCatalog属性设置为打开的目录。

'这允许访问Properties集合。

.ParentCatalog = CatDB

'设置属性以创建链接。

.Properties（"Jet OLEDB：Create Link"）。Value = True

.Properties（" Jet OLEDB：Link Datasource" ).Value = sLinkToDB

.Properties（"Jet OLEDB：远程表名称"）。值= sLinkToTable

结束 使用

'将表附加到Tables集合。

CatDB.Tables.Append（tbl ）

'设置返回成功

AccessLinkToTable = True

结束 如果

退出 功能

< span style ="color：blue"> Catch ex As 例外

投掷 ex

最后

CatDB = 没什么

tbl = 没什么

结束 尝试

结束 功能

''' < summary>

'''检查表格是否已存在

''' < / summary>

''' < param name =" strTblName">< / param>

< span style ="font-family：'Courier New'; font-size：10pt"> ''' < param name =" db">< / param>

''' < returns>< / returns>

''' < remarks>< /备注>

功能 TableExists（ ByVal strTblName 作为 字符串， ByVal db As dao.Database） As 布尔

昏暗 tbl As dao.TableDef

尝试

用于 每个 tbl 在 db.TableDefs

如果 tbl.Name = strTblName 然后

TableExists = True

退出 功能

结束 如果

下一页

TableExists = False

Catch ex As 异常

投掷 ex

结束 尝试

结束 功能

''' < summary>

' ''创建/或刷新指向sql server数据库的链接

''' < / summary>

< span style ="font-family：'Courier New'; font-size：10pt"> ''' < param name =" strTblName">< / param>

''' < p aram name =" SourceTableName">< / param>

''' < param name =" db">< / param>

''' <备注>< / remarks>

私有 Sub LinkToSQL（ ByVal strTblName As Strin g ， ByVal SourceTableName As String ， ByVal db As dao.Database）

Dim tbl As dao.TableDef

< p class = MsoNormal style ="line-height：normal; margin：0in 0in 0pt"> Dim strConn As String = < span style=\"color:#a31515\">""

 

        If (TableExists(strTblName, db) = False) Then ’for new tables

            ’create new link

 

            strConn = "ODBC;"

            strConn = strConn & "DSN=MYDSN;"

            strConn = strConn & "APP=Microsoft Access;"

            strConn = strConn & "SERVER=[server name];" ’server name

            strConn = strConn & "DATABASE=[database name];"

            strConn = strConn & "UID=[UID];"

            strConn = strConn & "PWD=[PWD];"

            strConn = strConn & "TABLE=[SourceTableName]"

 

            tbl = db.CreateTableDef(strTblName, _

                            dao.TableDefAttributeEnum.dbAttachSavePWD, SourceTableName, _

                            strConn)

 

            db.TableDefs.Append(tbl)

        Else

            ’refresh link for existing tables

            tbl = db.TableDefs(strTblName)

            If tbl.SourceTableName <> "" Then

                tbl.Connect = strConn

                tbl.RefreshLink()

            End If

        End If

    End Sub

 

’’’’’’’’’’’’’’’’’’’’’’’’’’’’
’Hope this helps...

Hi.
Good day.

I have an Microsoft Access Application where I using Link table(SQL Server database) using DSN. Is that possiable to get all the tables link detail and refresh the previous link with a new DSN link.

Thanks
  

---

Md. Marufuzzaman

解决方案

Yes you can using the following methods:

 

    ''' <summary>

    ''' Refresh MS Access link

    ''' </summary>

    ''' <param name="dbs"></param>

    ''' <param name="strLocalTableName"></param>

    ''' <param name="NewPathname"></param>

    ''' <remarks></remarks>

    Public Sub RelinkTables(ByVal dbs As dao.Database, ByVal strLocalTableName As String, _

                            ByVal NewPathname As String)

        Dim tbl As dao.TableDef

        Try

            tbl = dbs.TableDefs(strLocalTableName)

            If tbl.SourceTableName <> "" Then 'If the table source is other than a base table

                tbl.Connect = ""

                'Tdf.SourceTableName = strSourceTableName

                tbl.Connect = ";DATABASE=" & NewPathname 'Set the new source

                tbl.RefreshLink() 'Refresh the link

            End If

 

        Catch ex As Exception

            Throw ex

        Finally

            tbl = Nothing

        End Try

    End Sub

 

    ''' <summary>

    ''' Create a new link to MS Access

    ''' </summary>

    ''' <param name="con"></param>

    ''' <param name="sLinkFromDB"></param>

    ''' <param name="sLinkToDB"></param>

    ''' <param name="sLinkToTable"></param>

    ''' <param name="sNewLinkTableName"></param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Function AccessLinkToTable(ByVal con As ADODB.Connection, ByVal sLinkFromDB As String, _

                               ByVal sLinkToDB As String, ByVal sNewLinkTableName As String, _

                                ByVal sLinkToTable As String) As Boolean

        Dim CatDB As ADOX.Catalog

        Dim tbl As ADOX.Table

        Try

            If Len(Dir

(sLinkFromDB)) > 0 And Len(Dir

(sLinkToDB)) > 0 Then

                CatDB = New ADOX.Catalog

 

                CatDB.ActiveConnection = con

 

                tbl = New ADOX.Table

 

                With tbl

                    'Name the new Table

                    If Len(sNewLinkTableName) Then

                        .Name = sNewLinkTableName

                    Else

                        .Name = sLinkToTable

                    End If

 

                    'Set ParentCatalog property to the open Catalog.

                    'This allows access to the Properties collection.

                    .ParentCatalog = CatDB

 

                    'Set the properties to create the link.

                    .Properties("Jet OLEDB:Create Link").Value = True

                    .Properties("Jet OLEDB:Link Datasource").Value = sLinkToDB

                    .Properties("Jet OLEDB:Remote Table Name").Value = sLinkToTable

                End With

 

                'Append the table to the Tables collection.

                CatDB.Tables.Append(tbl)

 

                'Set return as success

                AccessLinkToTable = True

            End If

            Exit Function

        Catch ex As Exception

            Throw ex

        Finally

            CatDB = Nothing

            tbl = Nothing

        End Try

    End Function

 

 

 

   ''' <summary>

    ''' Check if the table is already exists

    ''' </summary>

    ''' <param name="strTblName"></param>

    ''' <param name="db"></param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Function TableExists(ByVal strTblName As String, ByVal db As dao.Database) As Boolean

        Dim tbl As dao.TableDef

        Try

 

            For Each tbl In db.TableDefs

                If tbl.Name = strTblName Then

                    TableExists = True

                    Exit Function

                End If

            Next

 

            TableExists = False

 

        Catch ex As Exception

            Throw ex

        End Try

    End Function

 

''' <summary>

    ''' creates/or refreshes links to sql server database

    ''' </summary>

    ''' <param name="strTblName"></param>

    ''' <param name="SourceTableName"></param>

    ''' <param name="db"></param>

    ''' <remarks></remarks>

    Private Sub LinkToSQL(ByVal strTblName As String, ByVal SourceTableName As String, ByVal db As dao.Database)

        Dim tbl As dao.TableDef

        Dim strConn As String = ""

 

        If (TableExists(strTblName, db) = False) Then 'for new tables

            'create new link

 

            strConn = "ODBC;"

            strConn = strConn & "DSN=MYDSN;"

            strConn = strConn & "APP=Microsoft Access;"

            strConn = strConn & "SERVER=[server name];" 'server name

            strConn = strConn & "DATABASE=[database name];"

            strConn = strConn & "UID=[UID];"

            strConn = strConn & "PWD=[PWD];"

            strConn = strConn & "TABLE=[SourceTableName]"

 

            tbl = db.CreateTableDef(strTblName, _

                            dao.TableDefAttributeEnum.dbAttachSavePWD, SourceTableName, _

                            strConn)

 

            db.TableDefs.Append(tbl)

        Else

            'refresh link for existing tables

            tbl = db.TableDefs(strTblName)

            If tbl.SourceTableName <> "" Then

                tbl.Connect = strConn

                tbl.RefreshLink()

            End If

        End If

    End Sub

 

''''''''''''''''''''''''''''
'Hope this helps...

这篇关于如何获取链接表DSN详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！