I am building a media management tool and starting it in Access 2003.
I have various linked tables, and have sufficient code to loop through my list of linked tables to verify that the data file specified in the link is available in the specified path.
I am trying to finish this portion of my code to handle the table(s) whose links are broken because the specified file no longer exists.
Can someone suggest how I might "re-link" a table or several table (within the same data source) through VBA? Right now the links are to other MDB data sources.
What I am trying to avoid is running the re-link routine that I have every time the program starts even when there is nothing to do, and also I do not want to re-link tables whose links are perfectly good. Doing this, wastes a lot of startup time. I have about 11 tables now, but with the prospect if that list growing, I am trying to build my program to have a more intelligient way to check each link before "re-creating" it without having to redo all of them every time.
Thanks,
CJ
7 5516
Hard to see exactly what you are doing from the description, but one thought is to arrange things so that the relinking routine runs
only when you capture an error that occurs within some VBA code because of a broken link. Hope my meaning in that statement is clear?
Need more detail to give you an idea on how to go about doing that.
OK, here is the code I have so far, this lists the tables that are linked into a collection each is referenced by an index, and the code which will extract the table name, and its current link from that indexed entry.
Then what I do is run a check to see if the referenced file exists.
This is where I start to run into trouble. If the file exists, then I would simply keep going through the collection until I came to a table which had a link to a different back-end database. Then I would test to see if that file existed and so on. If each of the different back-end files exist, then no re-linking should occur. This should allow, my program to start relatively quickly. If one or more of the back-end tables did not exist based on what the current link says it should be, then that table should be re-linked, test the next entry and so on.
Here is my code (kind of a collection of different things), there may be stuff in here, I really do not need once this is done. - Function DataSourceTest()
-
'This function is a test function built to test the back-end data connectivity code
-
'This will be the code that will list the tables, see where they are connected and whether or not the data source is actually available
-
-
Dim x
-
Dim TableDataPath As String
-
Dim PathToData As String
-
Dim NewFile As String
-
-
'Get list of linked tables, and their data source
-
fGetLinkedTables
-
-
'Loop through each item in the collection of linked tables to decide what to do with it
-
For x = 1 To LinkedTables.Count
-
Debug.Print LinkedTables.Item(x)
-
-
TableDataPath = Left$(LinkedTables.Item(x), InStrRev(LinkedTables.Item(x), ";"))
-
TableDataPath = Left$(TableDataPath, Len(TableDataPath) - 1)
-
Debug.Print TableDataPath
-
-
PathToData = GetDataPath(TableDataPath)
-
Debug.Print PathToData
-
-
If FileOrDirExists(PathToData) = True Then
-
'Available, do nothing
-
Else
-
'Not available, locate the data file
-
MsgBox "The back-end data source for: " & TableDataPath & " is not available." & vbCrLf & "Please locate the data source."
-
NewFile = fGetMDBName(TableDataPath)
-
End If
-
Next
-
-
End Function
-
-
Function fGetLinkedTables()
-
'Returns all linked tables
-
Dim collTables As New Collection
-
Dim tdf As TableDef, db As Database
-
Set db = CurrentDb
-
db.TableDefs.Refresh
-
For Each tdf In db.TableDefs
-
With tdf
-
If Len(.Connect) > 0 Then
-
If Left$(.Connect, 4) = "ODBC" Then
-
' collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
-
'ODBC Reconnect handled separately
-
Else
-
collTables.Add Item:=.Name & .Connect, Key:=.Name
-
End If
-
End If
-
End With
-
Next
-
Set fGetLinkedTables = collTables
-
Set LinkedTables = collTables
-
Set collTables = Nothing
-
Set tdf = Nothing
-
Set db = Nothing
-
End Function
-
-
Public Function GetDataPath(strTable As String) As String
-
'On Error GoTo Err_Handler
-
-
'Purpose: Return the full path of the file from the Connect property of this tabledef.
-
'Return: Full path and file name for attached MDB.
-
' Just the path for some other types (e.g. attached text.)
-
' Zero-length string for local table (not attached), or of argument is zero-length.
-
' "#Error" on error, e.g. table not found.
-
'Requires: Split() function for Access 97 or earlier.
-
-
Dim varArray As Variant
-
Dim i As Integer
-
-
If Trim$(strTable) <> vbNullString Then
-
varArray = Split(CurrentDb.TableDefs(strTable).Connect, ";")
-
For i = LBound(varArray) To UBound(varArray)
-
If varArray(i) Like "DATABASE=*" Then
-
GetDataPath = Trim$(Mid$(varArray(i), 10))
-
Exit For
-
End If
-
Next
-
End If
-
-
Exit_Handler:
-
-
Exit Function
-
-
Err_Handler:
-
-
'Call LogError(Err.Number, Err.Description, conMod & ".GetDataPath", strTable, False)
-
'GetDataPath = "#Error"
-
Resume Exit_Handler
-
-
End Function
-
-
'This function will make sure the file specified in the linked property is available
-
'If available, then nothing should happen, if not, then a request to locate to data files will be presented to the user
-
-
Function FileOrDirExists(PathName As String) As Boolean
-
-
'Macro Purpose: Function returns TRUE if the specified file
-
' or folder exists, false if not.
-
'PathName : Supports Windows mapped drives or UNC
-
' : Supports Macintosh paths
-
'File usage : Provide full file path and extension
-
'Folder usage : Provide full folder path
-
' Accepts with/without trailing "\" (Windows)
-
' Accepts with/without trailing ":" (Macintosh)
-
-
Dim iTemp As Integer
-
-
'Ignore errors to allow for error evaluation
-
On Error Resume Next
-
iTemp = GetAttr(PathName)
-
-
'Check if error exists and set response appropriately
-
Select Case Err.Number
-
Case Is = 0
-
FileOrDirExists = True
-
Case Else
-
FileOrDirExists = False
-
End Select
-
-
'Resume error checking
-
On Error GoTo 0
-
-
End Function
-
-
Function fRefreshLinks() As Boolean
-
-
Dim strMsg As String, collTbls As Collection
-
Dim i As Integer, strDBPath As String, strTbl As String
-
Dim dbCurr As Database, dbLink As Database
-
Dim tdfLocal As TableDef
-
Dim varRet As Variant
-
Dim strNewPath As String
-
-
Const cERR_USERCANCEL = vbObjectError + 1000
-
Const cERR_NOREMOTETABLE = vbObjectError + 2000
-
-
'On Local Error GoTo fRefreshLinks_Err
-
-
'If MsgBox("Are you sure you want to reconnect all Access tables?", _
-
vbQuestion + vbYesNo, "Please confirm...") = vbNo Then Err.Raise cERR_USERCANCEL
-
-
'First get all linked tables in a collection
-
Set collTbls = fGetLinkedTables
-
-
'now link all of them
-
Set dbCurr = CurrentDb
-
-
strMsg = "Do you wish to specify a different path for the Access Tables?"
-
-
If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then
-
strNewPath = fGetMDBName("Please select a new datasource")
-
Else
-
strNewPath = vbNullString
-
End If
-
-
For i = collTbls.Count To 1 Step -1
-
strDBPath = fParsePath(collTbls(i))
-
strTbl = fParseTable(collTbls(i))
-
varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
-
If Left$(strDBPath, 4) = "ODBC" Then
-
'ODBC Tables
-
'ODBC Tables handled separately
-
' Set tdfLocal = dbCurr.TableDefs(strTbl)
-
' With tdfLocal
-
' .Connect = pcCONNECT
-
' .RefreshLink
-
' collTbls.Remove (strTbl)
-
' End With
-
Else
-
If strNewPath <> vbNullString Then
-
'Try this first
-
strDBPath = strNewPath
-
Else
-
If Len(Dir(strDBPath)) = 0 Then
-
'File Doesn't Exist, call GetOpenFileName
-
strDBPath = fGetMDBName("'" & strDBPath & "' not found.")
-
If strDBPath = vbNullString Then
-
'user pressed cancel
-
Err.Raise cERR_USERCANCEL
-
End If
-
End If
-
End If
-
-
'backend database exists
-
'putting it here since we could have
-
'tables from multiple sources
-
Set dbLink = DBEngine(0).OpenDatabase(strDBPath)
-
-
'check to see if the table is present in dbLink
-
strTbl = fParseTable(collTbls(i))
-
If fIsRemoteTable(dbLink, strTbl) Then
-
'everything's ok, reconnect
-
Set tdfLocal = dbCurr.TableDefs(strTbl)
-
With tdfLocal
-
.Connect = ";Database=" & strDBPath
-
.RefreshLink
-
collTbls.Remove (.Name)
-
End With
-
Else
-
Err.Raise cERR_NOREMOTETABLE
-
End If
-
End If
-
Next
-
fRefreshLinks = True
-
varRet = SysCmd(acSysCmdClearStatus)
-
MsgBox "All Access tables were successfully reconnected.", _
-
vbInformation + vbOKOnly, _
-
"Success"
-
-
fRefreshLinks_End:
-
Set collTbls = Nothing
-
Set tdfLocal = Nothing
-
Set dbLink = Nothing
-
Set dbCurr = Nothing
-
Exit Function
-
fRefreshLinks_Err:
-
fRefreshLinks = False
-
Select Case Err
-
Case 3059:
-
-
Case cERR_USERCANCEL:
-
MsgBox "No Database was specified, couldn't link tables.", _
-
vbCritical + vbOKOnly, _
-
"Error in refreshing links."
-
Resume fRefreshLinks_End
-
Case cERR_NOREMOTETABLE:
-
MsgBox "Table '" & strTbl & "' was not found in the database" & _
-
vbCrLf & dbLink.Name & ". Couldn't refresh links", _
-
vbCritical + vbOKOnly, _
-
"Error in refreshing links."
-
Resume fRefreshLinks_End
-
Case Else:
-
strMsg = "Error Information..." & vbCrLf & vbCrLf
-
strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
-
strMsg = strMsg & "Description: " & Err.Description & vbCrLf
-
strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
-
MsgBox strMsg, vbOKOnly + vbCritical, "Error"
-
Resume fRefreshLinks_End
-
End Select
-
End Function
-
-
Function fIsRemoteTable(dbRemote As Database, strTbl As String) As Boolean
-
Dim tdf As TableDef
-
On Error Resume Next
-
Set tdf = dbRemote.TableDefs(strTbl)
-
fIsRemoteTable = (Err = 0)
-
Set tdf = Nothing
-
End Function
-
-
Function fGetMDBName(strIn As String) As String
-
'Calls GetOpenFileName dialog
-
Dim strFilter As String
-
-
strFilter = ahtAddFilterItem(strFilter, _
-
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
-
"*.mdb; *.mda; *.mde; *.mdw")
-
strFilter = ahtAddFilterItem(strFilter, _
-
"All Files (*.*)", _
-
"*.*")
-
-
fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
-
OpenFile:=True, _
-
DialogTitle:=strIn, _
-
Flags:=ahtOFN_HIDEREADONLY)
-
End Function
-
-
Function fParsePath(strIn As String) As String
-
If Left$(strIn, 4) <> "ODBC" Then
-
fParsePath = Right(strIn, Len(strIn) _
-
- (InStr(1, strIn, "DATABASE=") + 8))
-
Else
-
fParsePath = strIn
-
End If
-
End Function
-
-
Function fParseTable(strIn As String) As String
-
fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
-
End Function
Also, I do realize that the fRelinkTables function does actually to what my datsource test function is doing in part, but I am trying to get this down to just what I need.
Appreciate your help,and looking forward to your input.
Thanks,
CJ
Also, this may not be quite as clean as it should or will be in the end, but the core functionality is what I am trying to do.
I suspect from your answer, you may have a better idea of how to handle this.
NeoPa 32,556
Expert Mod 16PB
I'm working on something similar myself today, so I'll post something when I've worked it out if you like.
NeoPa 32,556
Expert Mod 16PB
I found what I needed in other threads here, but I'll just link a couple of threads that got me going ( Checking linked tables on startup & linked table).
The fundamental concept of relinking is to change the TableDef's .Connect string to reflect your new requirement, then call .RefreshLink for the TableDef.
The following code is a little routine I'm now using that returns the current address of the linked tables (Assumes all AccessLinked tables refer to the same database), and will optionally set them too, if a parameter is passed. - 'LinkTo() Returns the name of the database that AccessLinked tables link to.
-
'Assumes all AccessLinked tables refer to the same database.
-
'Also allows caller to specify a location to change the links to (if necessary).
-
Public Function LinkTo(Optional ByVal strLinkDest As String = "") As String
-
Dim db As DAO.Database
-
Dim tdf As DAO.TableDef
-
Dim intDB As Integer
-
Dim strLink As String
-
Dim varLinkAry As Variant
-
-
Set db = CurrentDb
-
For Each tdf In db.TableDefs
-
With tdf
-
If .Attributes And dbAttachedTable Then
-
varLinkAry = Split(.Connect, ";")
-
For intDB = LBound(varLinkAry) To UBound(varLinkAry)
-
If Left(varLinkAry(intDB), 9) = "DATABASE=" Then Exit For
-
Next intDB
-
strLink = Mid(varLinkAry(intDB), 10)
-
If LinkTo = "" Then LinkTo = strLink
-
If strLinkDest = "" Or strLinkDest = strLink Then Exit For
-
varLinkAry(intDB) = "DATABASE=" & strLinkDest
-
.Connect = Join(varLinkAry, ";")
-
Call .RefreshLink
-
End If
-
End With
-
Next tdf
-
End Function
NeoPa 32,556
Expert Mod 16PB
As I found I needed to add some error handling code into this, for all but very well defined environments (IE All calls checked thoroughly beforehand for invalid links), I include the updated version. The extra length is due to the error handling code. - 'LinkTo() Returns the name of the database that AccessLinked tables link to.
-
'Assumes all AccessLinked tables refer to the same database.
-
'Also allows caller to specify a location to change the links to (if necessary).
-
Public Function LinkTo(Optional ByVal strLinkDest As String = "") As String
-
Dim db As DAO.Database
-
Dim tdf As DAO.TableDef
-
Dim intParam As Integer
-
Dim strLink As String
-
Dim varLinkAry As Variant
-
-
Set db = CurrentDb
-
For Each tdf In db.TableDefs
-
With tdf
-
If .Attributes And dbAttachedTable Then
-
varLinkAry = Split(.Connect, ";")
-
For intParam = LBound(varLinkAry) To UBound(varLinkAry)
-
If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
-
Next intParam
-
strLink = Mid(varLinkAry(intParam), 10)
-
If LinkTo = "" Then LinkTo = strLink
-
If strLinkDest = "" Or strLinkDest = strLink Then Exit For
-
varLinkAry(intParam) = "DATABASE=" & strLinkDest
-
.Connect = Join(varLinkAry, ";")
-
On Error Resume Next
-
Call .RefreshLink
-
Select Case Err.Number
-
Case 3011, 3024, 3044, 3055, 7874
-
varLinkAry(intParam) = "DATABASE=" & strLink
-
.Connect = Join(varLinkAry, ";")
-
strLinkDest = "Database file (" & _
-
strLinkDest & _
-
") not found"
-
Call MsgBox(strLinkDest, _
-
vbOKOnly Or vbExclamation, _
-
"LinkTo")
-
Exit For
-
End Select
-
End If
-
End With
-
Next tdf
-
End Function
NeoPa 32,556
Expert Mod 16PB
By the way, while researching this I came across a very useful function AccessError(), which takes an error number and returns the associated text. It helped me select a buch of error codes that may cause this to fail due to files/objects not being where they should be. If anyone sees any others just flag them up & I'll update.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used...
|
by: deko |
last post by:
How to run action query against linked table?
I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table.
When I attempt to run an action query against the linked table I get this...
|
by: Bruce |
last post by:
Greetings!
After finally figuring out that the Linked Table manager is no longer
an add-in in Access 2003 I now discover that it does not display any
of my linked tables in the 'select linked...
|
by: elie chucrallah |
last post by:
I am developping an application using vb6. this application connects to
a microsoft access database using Microsoft DAO 3.51 in order to connect
to access i have to convert it to access 97, before...
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For...
|
by: Lewis Veale |
last post by:
I have an Access 2000 front-end pointing at a SQL Server backend, with
around 80 linked tables and views. I frequently need to point the
front-end at different versions of the back-end, and achieve...
|
by: Wayne |
last post by:
I've just performed a reformat and reinstalled Access 2000 and 2003 and
as usual the linked table manager in Access 2003 is broken ie. it
doesn't show any tables. I've gone through the process of...
|
by: Jill Elaine |
last post by:
I am building an Access 2002 frontend with linked tables to an
encrypted Paradox 7 database. When I first create these linked tables,
I'm asked for the password to the encrypted Paradox database,...
|
by: bubbles |
last post by:
Using Access 2003 front-end, with SQL Server 2005 backend.
I need to make the front-end application automatically refresh the
linked
SQL Server tables.
New tables will be added dynamically in...
|
by: sparks |
last post by:
We have an older database done in access 97 and some of its reports
use linked tables to another database.. it works fine when opened in
access 97.
One of the people using the database only has...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |