On Tue, 9 Dec 2003 09:27:59 +0100, "DraguVaso" <pi**********@hotmail.com> wrote:
¤ No, it's not that: what I need is something that refeshes the linked tables
¤ and query's, not that connects to them. I have it in Access, but not in
¤ VB.NET. (See underneath this). I'm just looking for a 'translation' of the
¤ code below:
¤
¤ Option Compare Database
¤ Option Explicit
¤ '-- Global Connection Strings --
¤ Global Const oudeDSN = "ODBC;DSN=OldOdbcName"
¤ Private intLengte As Integer
¤ Global Const iDSN = "OdbcName"
¤ Global Const iSRVR = "ServerName"
¤ Global Const iDATABASE = "DatabaseName"
¤ Global Const iUID = "UserId"
¤ Global Const iPWD = "Password"
¤ Type TableDefinition
¤ name As String
¤ SourceTableName As String
¤ End Type
¤ Global arrTbl() As TableDefinition
¤
¤ Function RefreshAttach()
¤ DoCmd.Hourglass True
¤ '-----------------------------
¤ '-- Function Refresh Attach --
¤ '-----------------------------
¤ On Error Resume Next
¤ Dim ldb As Database
¤ Dim tdf As TableDef
¤ Dim qdf As QueryDef
¤ Dim wrktdf As String
¤ Dim wrksrc As String
¤ Dim r As Integer
¤ Dim s As Integer
¤ Dim varReturn As Variant
¤ ' Return reference to current database.
¤ Set ldb = CurrentDb
¤ s = -1
¤ Erase arrTbl
¤
¤ intLengte = Len(oudeDSN)
¤
¤ For r = 0 To ldb.TableDefs.Count - 1
¤ ' Keep Params
¤ If (ldb.TableDefs(r).Attributes And dbAttachedODBC) And
¤ (Left(ldb.TableDefs(r).Connect, intLengte) = oudeDSN) Then
¤ s = s + 1
¤ ReDim Preserve arrTbl(0 To s)
¤ arrTbl(s).name = ldb.TableDefs(r).name
¤ arrTbl(s).SourceTableName = ldb.TableDefs(r).SourceTableName
¤ End If
¤ Next r
¤ For r = 0 To s
¤ varReturn = SysCmd(acSysCmdSetStatus, "Attaching Table " &
¤ arrTbl(r).name)
¤ ldb.TableDefs.Delete arrTbl(r).name
¤ ldb.TableDefs.Refresh
¤ Set tdf = ldb.CreateTableDef(arrTbl(r).name, dbAttachSavePWD)
¤ tdf.SourceTableName = arrTbl(r).SourceTableName
¤ tdf.Connect = "ODBC;DSN=" & iDSN & ";SRVR=" & iSRVR & ";DATABASE=" &
¤ iDATABASE & ";UID=" & iUID & ";PWD=" & iPWD & ";TABLE=" &
¤ arrTbl(r).SourceTableName & ""
¤ On Error Resume Next
¤ ldb.TableDefs.Append tdf
¤ On Error GoTo RefreshAttach_Err
¤ ldb.TableDefs.Refresh
¤ Set tdf = Nothing
¤ Next r
¤ Erase arrTbl
¤ s = -1
¤ For r = 0 To ldb.QueryDefs.Count - 1
¤ ' Keep Params
¤ If (ldb.QueryDefs(r).Type = dbQSQLPassThrough) And
¤ (Left(ldb.QueryDefs(r).Connect, intLengte) = oudeDSN) Then
¤ s = s + 1
¤ ReDim Preserve arrTbl(0 To s)
¤ arrTbl(s).name = ldb.QueryDefs(r).name
¤ End If
¤ Next r
¤
¤ For r = 0 To s
¤ varReturn = SysCmd(acSysCmdSetStatus, "Attaching Query " &
¤ arrTbl(r).name)
¤ Set qdf = ldb.QueryDefs(arrTbl(r).name)
¤ qdf.Connect = "ODBC;DSN=" & iDSN & ";SRVR=" & iSRVR & ";DATABASE=" &
¤ iDATABASE & ";UID=" & iUID & ";PWD=" & iPWD & ""
¤ ldb.QueryDefs.Refresh
¤ Set qdf = Nothing
¤ Next r
¤
¤ Set ldb = Nothing
¤
¤ RefreshAttach_End:
¤ varReturn = SysCmd(acSysCmdSetStatus, " ")
¤ DoCmd.Hourglass False
¤ Exit Function
¤
¤ RefreshAttach_Err:
¤ ' Display error information.
¤ DoCmd.Hourglass False
¤ MsgBox "Error number " & Err.Number & ": " & Err.Description
¤ ' Resume with statement following occurrence of error.
¤ DoCmd.Hourglass True
¤ Resume RefreshAttach_End:
¤ End Function
¤
¤ Function AttachRemoveDbo() As Integer
¤ On Error GoTo RemoveDbo_err
¤ Dim CurrentDatabase As Database
¤ Dim MyDocument As Document
¤ Dim J As Integer
¤ Set CurrentDatabase = DBEngine(0)(0)
¤ For J = 0 To CurrentDatabase.Containers("Tables").Documents.Cou nt - 1
¤ Set MyDocument = CurrentDatabase.Containers("Tables").Documents(J)
¤ If Left(MyDocument.name, 4) = "dbo_" Then
¤ DoCmd.Rename Mid(MyDocument.name, 5), A_TABLE, MyDocument.name
¤ End If
¤ Next J
¤ AttachRemoveDbo = True
¤ AttachRemoveDbo_exit:
¤ Exit Function
¤ AttachRemoveDbo_err:
¤ MsgBox Error
¤ AttachRemoveDbo = False
¤ Resume AttachRemoveDbo_exit
¤ End Function
¤
You should be able to Refresh linked tables using ADOX (Microsoft ADO Ext 2.x for DDL and Security):
Sub RefreshLinkedTablesWithADOX()
Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection
Dim LinkProviderString As String
Dim LinkDataSource As String
Try
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4 .0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")
ADOXCatalog.ActiveConnection = ADOConnection
For Each ADOXTable In ADOXCatalog.Tables
If ADOXTable.Type = "LINK" Then
LinkProviderString = ADOXTable.Properties("Jet OLEDB:Link Provider
String").Value
LinkDataSource = ADOXTable.Properties("Jet OLEDB:Link Datasource").Value
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value =
LinkProviderString
ADOXTable.Properties("Jet OLEDB:Link Datasource").Value = LinkDataSource
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try
End Sub
Paul ~~~
pc******@ameritech.net
Microsoft MVP (Visual Basic)
