How to get the link table DSN detail
- 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...
