Changing Linked Table path

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database which is linked to an XML table. I prefer it being a separate table as opposed to just importing it for memory purposes. I would like to be able to set up the table as such so that if I move the directory containing both the database and the XML file to another location I do not have to manually change the link between the database and the XML file using the Linked table manager. Is this possible?
 
It's not possible to avoid having to change the linkage, although you can do
it in code, rather than using the Linked Table Manager.

What you'll need to do is delete the existing linked table, and recreate it
with the correct Connect property.

Check what the current Connect property is, and you should be able to tell
quite easily what it needs to be changed to if the source table location
changes.

And just in case your next question was going to be "Can I use a relative
path?", the answer is no. However, you can determine where your front end is
located, and determine the full path to the source table from that.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Stevkev said:
I have a database which is linked to an XML table. I prefer it being a
Click to expand...
separate table as opposed to just importing it for memory purposes. I would
like to be able to set up the table as such so that if I move the directory
containing both the database and the XML file to another location I do not
have to manually change the link between the database and the XML file using
the Linked table manager. Is this possible?
 
Thank you. Your advice is quite helpful. One more question. How do I determine in code what link is for the existing table. I would only want to run this code if the table does not exist in it's current designated location. How would I got about finding out in code what the "old" location is before it gives me an error that the table is not there. Hope that makes sense. Thank you again
 
=?Utf-8?B?c3Rldmtldg==?= said:
Thank you. Your advice is quite helpful. One more question. How do I
determine in code what link is for the existing table. I would only want to
run this code if the table does not exist in it's current designated
location. How would I got about finding out in code what the "old" location
is before it gives me an error that the table is not there. Hope that makes
sense. Thank you again
Click to expand...

You can examine the TableDef's Connect property. If the table is linked from an
Access database, then the actual path to the database will be:

Mid(CurrentDb.TableDefs(strTable).Connect, 11)

or for other types:

Mid(CurrentDb.TableDefs(strTable).Connect,
InStr(CurrentDb.TableDefs(strTableName).Connect, ";DATABASE=") + 10)

You can then check if the database exists by using:

If Len(Dir(strDBName))>0 Then
' Database exists
Else
' Database doesn't exist
End If
 
Back
Top