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 5538 Delerna 1,134
Recognized Expert Top Contributor
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,572
Recognized Expert Moderator MVP
I'm working on something similar myself today, so I'll post something when I've worked it out if you like.
NeoPa 32,572
Recognized Expert Moderator MVP
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,572
Recognized Expert Moderator MVP
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,572
Recognized Expert Moderator MVP
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 the SQL Profile to watch the T-SQL-Command which Access ( who
creates the commands?) creates and noticed:
|
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
error:
Deleting data in a linked table is not supported by this ISAM.
From what I understand, indexed sequential access method (ISAM) drivers are
used to update "non-Microsoft" file formats. So why doesn't Access
|
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 tables to be updated' list.
Although I have about 30 linked tables in my application, absolutely
none of them are showing up in the list. I compacted and repaired,
but that didn't have any effect. Has anyone else seen this problem?
If so, how is...
|
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 converting it, i
made some linked tables that point to tables in another database.i was
using office 2002. now with office 2003, when i make linked tables then
convert the database to access 97, the linked tables become tables and
point no more...
|
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 tables that involve a number
field as the primary key, the data is returned successfully. For tables
that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary
key, I have the correct number of rows returned, but the data displayed...
| |
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 this by
relinking the tables and views to use a different DSN.
I use the Linked Table Manager > Select All > and tick the 'always
prompt for a new location' box, then click OK. It correctly pops up
the 'select data source' box, from which I...
|
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
re-registering Accwiz.dll as outlined in the MS Knowledge Base and
although this process has worked for me before, it hasn't worked this
time. There are still no tables showing.
The article in the knowledge base includes this line: "If the...
|
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, and the
linked tables are successfully created. I use the data from these
linked tables in several forms.
All works great until I close the Access frontend and open it again.
When I try to use the forms, I get an error message: "Could not...
|
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 the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).
|
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 access 2003 installed
and when they try to open the reports it says that it can't find the
tables and they are not listed in the tables.
how can I correct this without converting everything to 2003 or some
such...it has to remain a 97 database.
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |