If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Link tables
hello, I have some code for refreshing links. Which is
very slow.. how can I code a refreshing links function only if the connection is broken? The code I have now: Function VerifyLink() As Boolean On Error GoTo Err_Verify 'Verify connection information in linked tables Dim db As DAO.Database Dim tdx As DAO.TableDefs Dim tdf As DAO.TableDef Dim sConnect As String Dim sConnectPrj As String Dim sConnectTR As String Dim SName As String Dim sConnectBC As String Set db = CurrentDb() Set tdx = db.TableDefs sConnect = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\PersonnelInfo02_be.mdb" sConnectPrj = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\Projects02_be.mdb" sConnectTR = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\TimeRecording07_be.mdb" sConnectBC = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\Book_Database_be.mdb" ' Skip system tables For Each tdf In tdx With tdf SName = .Name If .Connect vbNullString Then If Not (VBA.Left$(SName, 4) = "MSys" Or VBA.Left$(SName, 1) = "~") Then If (SName = "tblListOfProjects") Then .Connect = sConnectPrj ElseIf (SName = "tblCTSAPLst" Or SName = "tblDevelopmentActivities" _ Or SName = "tblTRPrj") Then .Connect = sConnectTR ElseIf (SName = "Book Inventory" Or SName = "Category" _ Or SName = "Lenguaje" Or SName = "Location" Or SName = "tblBookRental") Then .Connect = sConnectBC Else .Connect = sConnect End If .RefreshLink End If End If End With Next If Err.Number 0 Then VerifyLink = False Else VerifyLink = True End If Salir_Fun: On Error Resume Next db.Close Set tdf = Nothing Set tdx = Nothing Set db = Nothing Exit Function Err_Verify: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Salir_Fun End Function thx, gr |
#2
|
|||
|
|||
Before you run that code, try to open a recordset on one of the linked
tables. If it works, you don't need to relink. Another trick is to open the database before you relink: Dim dbData As DAO.Database Set dbData = OpenDatabase("I:\01\Drives\PIBDE General\Data Bases\PersonnelInfo02_be.mdb" 'rest of the relinking code here. dbData.Close Set dbData = Nothing You do not actually use this variable, but it forces Access to hold the network connection open until the relinking is done, which is much faster than allowing it to close and reopen between each relink. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "gr" wrote in message ... hello, I have some code for refreshing links. Which is very slow.. how can I code a refreshing links function only if the connection is broken? The code I have now: Function VerifyLink() As Boolean On Error GoTo Err_Verify 'Verify connection information in linked tables Dim db As DAO.Database Dim tdx As DAO.TableDefs Dim tdf As DAO.TableDef Dim sConnect As String Dim sConnectPrj As String Dim sConnectTR As String Dim SName As String Dim sConnectBC As String Set db = CurrentDb() Set tdx = db.TableDefs sConnect = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\PersonnelInfo02_be.mdb" sConnectPrj = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\Projects02_be.mdb" sConnectTR = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\TimeRecording07_be.mdb" sConnectBC = ";DATABASE=I:\01\Drives\PIBDE General\Data Bases\Book_Database_be.mdb" ' Skip system tables For Each tdf In tdx With tdf SName = .Name If .Connect vbNullString Then If Not (VBA.Left$(SName, 4) = "MSys" Or VBA.Left$(SName, 1) = "~") Then If (SName = "tblListOfProjects") Then .Connect = sConnectPrj ElseIf (SName = "tblCTSAPLst" Or SName = "tblDevelopmentActivities" _ Or SName = "tblTRPrj") Then .Connect = sConnectTR ElseIf (SName = "Book Inventory" Or SName = "Category" _ Or SName = "Lenguaje" Or SName = "Location" Or SName = "tblBookRental") Then .Connect = sConnectBC Else .Connect = sConnect End If .RefreshLink End If End If End With Next If Err.Number 0 Then VerifyLink = False Else VerifyLink = True End If Salir_Fun: On Error Resume Next db.Close Set tdf = Nothing Set tdx = Nothing Set db = Nothing Exit Function Err_Verify: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Salir_Fun End Function thx, gr |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Link to Excel problem | Jeff | General Discussion | 0 | July 21st, 2004 02:00 AM |
Still having Web Page Pathing Problems / Relative Links show Netwo | webmaster | Powerpoint | 27 | July 15th, 2004 11:16 PM |
Multiple Many-To-Many Tables | Tom | Database Design | 7 | May 15th, 2004 03:47 AM |