How to get the link table DSN detail

Wed, 13 May 2009 09:55:04 Z

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

How to get the link table DSN detail

Sat, 08 Aug 2009 16:59:22 Z

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

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 Sub

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