Changing the data files my tables are linked to...
I know I asked for, and got, an answer to this some time ago (where, I can't recall -- on the senet newsgroup comp.databases.ms-access, here on experts' exchange, or somewhere else entirely). Unfortunately, the evidence (some module(s)) that I did get the answer have been foolheartedly deleted and now I need them again.
What I'm looking for is some code that some kind soul offered to allow me to change the data files (created by/for a Visual Basic application for which I never purchased the source code) that are linked into the table structures
of my MS-Office97 database.
On my work system, I've got MS-Office97 and do most of my report/query development there. The data files existed originally on my Novell server, in a directory accessed on my work machine via a path containing a drive letter
X: and a string of directories. Under MS-Office95 I did an import of the structures (?) and then when I upgraded to MS-Office97 everything just, well,.... worked.
Then I wanted to take the stuff home. I installed MS-Office97 on my machine at home but there I don't have a Novell server. I've got several hard drives,
and I can easily recreate the same directory structure and did, but the drive letters just didn't reach that high.
Somebody (here, on Experts Exchange, or somewhere) provided some references to code fragments which allowed me to build a MODULE which let me tweak the linked tables paths to reference the same path/filename but on my D: drive.
Now I've got another machine at home. Built the same directory structure on that machine's D: drive, copied the data files over. But since I had done the original tweaks, two things have happened:
1) lots of new reports and queries have been developed at work on the version of the .MDB that expects the data files linked to the X: Novell drive, necessitating a new copy of the .MDB file
and
2) no sign of the MODULE that does the tweak from X: to D:...
Does anybody here remember this thread, from usenet or here or elsewhere (it's got to be at a year old, I think, and
I looked as far back as I can here in EE -- 1024 previously
answered questions (even though there are 3187 questions, the "next 100>" link when 1024 is at the bottom just returns
no further previously answered questions -- a bug for EE, I guess).
What I'm looking for is some code that some kind soul offered to allow me to change the data files (created by/for a Visual Basic application for which I never purchased the source code) that are linked into the table structures
of my MS-Office97 database.
On my work system, I've got MS-Office97 and do most of my report/query development there. The data files existed originally on my Novell server, in a directory accessed on my work machine via a path containing a drive letter
X: and a string of directories. Under MS-Office95 I did an import of the structures (?) and then when I upgraded to MS-Office97 everything just, well,.... worked.
Then I wanted to take the stuff home. I installed MS-Office97 on my machine at home but there I don't have a Novell server. I've got several hard drives,
and I can easily recreate the same directory structure and did, but the drive letters just didn't reach that high.
Somebody (here, on Experts Exchange, or somewhere) provided some references to code fragments which allowed me to build a MODULE which let me tweak the linked tables paths to reference the same path/filename but on my D: drive.
Now I've got another machine at home. Built the same directory structure on that machine's D: drive, copied the data files over. But since I had done the original tweaks, two things have happened:
1) lots of new reports and queries have been developed at work on the version of the .MDB that expects the data files linked to the X: Novell drive, necessitating a new copy of the .MDB file
and
2) no sign of the MODULE that does the tweak from X: to D:...
Does anybody here remember this thread, from usenet or here or elsewhere (it's got to be at a year old, I think, and
I looked as far back as I can here in EE -- 1024 previously
answered questions (even though there are 3187 questions, the "next 100>" link when 1024 is at the bottom just returns
no further previously answered questions -- a bug for EE, I guess).
Several ways to do so:
1. Try this
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
td.RefreshLink 'Update the link to point to the new location
2. On your system, you can use the lastdrive command (config.sys I believe) to specify the last available letter for a drive. (Works great with CD's). Specify a last drive (lastdrive = x) and then partition and format a new drive. I would think this would come in as drive z. (You might have to use a logical drive.
3. On NT server, it's much easier. Just use the disk administrator to reassign a drive letter to a drive. (You'll have to reboot.) Works like a charm. You can get an eval copy of NT from Microsoft (http://www.microsoft.com)
2.
1. Try this
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
td.RefreshLink 'Update the link to point to the new location
2. On your system, you can use the lastdrive command (config.sys I believe) to specify the last available letter for a drive. (Works great with CD's). Specify a last drive (lastdrive = x) and then partition and format a new drive. I would think this would come in as drive z. (You might have to use a logical drive.
3. On NT server, it's much easier. Just use the disk administrator to reassign a drive letter to a drive. (You'll have to reboot.) Works like a charm. You can get an eval copy of NT from Microsoft (http://www.microsoft.com)
2.
ASKER
I like suggestion 2 the best, but I have Win98 on this new machine. And I'm not going to
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
ASKER
I like suggestion 2 the best, but I have Win98 on this new machine. And I'm not going to
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
ASKER
I like suggestion 2 the best, but I have Win98 on this new machine. And I'm not going to
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
ASKER
I like suggestion 2 the best, but I have Win98 on this new machine. And I'm not going to
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
repartition existing drives, either.
Suggestion 3 is really off the wall -- I'm a Novell user; why would I want to do anything to
promote NT, even at home ? ;-) Seriously, Win98 came preinstalled, and WinNT would
cost $$$ to use it legally beyond the eval period.
So... suggestion 1. I guess I need more detail, since this is in an area of Access that I flounder in.
Dim db As Database
Dim td As TableDef
Set db = CurrentDb()
.... I presume these are literals? I type it exactly as shown here?
Set td = db.TableDefs("Sample Table") 'linked table
td.database = "P:\Access\Sample.mdb" 'new path to database
.... I presume here I substitute my own database's table names for the string
"Sample Table" ?? Do I quote "tblActivities" or just tblActivities?
The filenames involved are .REF, .ORI and .SCB, not .MDB. Shouldn't
make a difference, I guess.
td.RefreshLink 'Update the link to point to the new location
.... I presume this is a literal? I type it exactly as shown here?
There are close to 60 tables in this database... Is there anything to allow me to just loop
thru them? How about, since only the drive letter is changing, a way I can change JUST the
drive letter? There are 6 different files where the table data reside, and no convention in
table name that would provide a hint as to the correct file.
Frankly, if there were a quick and guaranteed way I could do this all by hand (there doesn't
seem to be, I've tried) rather than programatically, I'd choose to do that for the time being.
If you don't want to use code proposed by obregoru,
you can just use Linked Table Manager add-in.
Here is help for this:
------------
View, refresh, or change the file name and path for linked tables
Use this procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.
To view or refresh links
1 Open the database that contains links to tables.
2 On the Tools menu, point to Add-ins, and then click Linked Table Manager.
3 Select the check box for the tables whose links you want to refresh.
4 Click OK to refresh the links.
Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of <table name> dialog box in which you specify its new location.
If several selected tables have moved to the new location you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step.
To change the path for a set of linked tables
1 Open the database that contains links to tables.
2 On the Tools menu, point to Add-ins, and then click Linked Table Manager.
3 Select the Always Prompt For A New Location check box.
4 Select the check box for the tables whose links you want to change, and then click OK.
5 In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK
Notes
· The Linked Table Manager doesn't move database or table files. If you want to move database or table files to a new location, use the Windows Explorer, My Computer, or the MS-DOS copy or move commands. Once you have moved a database or table, you can use the Linked Table Manager to refresh links to its linked tables.
· The Linked Table Manager can't refresh links to Microsoft Access tables whose names were changed after they were linked. You must delete the current link, and then link such tables again.
----------
That's all.
Dedushka
you can just use Linked Table Manager add-in.
Here is help for this:
------------
View, refresh, or change the file name and path for linked tables
Use this procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.
To view or refresh links
1 Open the database that contains links to tables.
2 On the Tools menu, point to Add-ins, and then click Linked Table Manager.
3 Select the check box for the tables whose links you want to refresh.
4 Click OK to refresh the links.
Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of <table name> dialog box in which you specify its new location.
If several selected tables have moved to the new location you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step.
To change the path for a set of linked tables
1 Open the database that contains links to tables.
2 On the Tools menu, point to Add-ins, and then click Linked Table Manager.
3 Select the Always Prompt For A New Location check box.
4 Select the check box for the tables whose links you want to change, and then click OK.
5 In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK
Notes
· The Linked Table Manager doesn't move database or table files. If you want to move database or table files to a new location, use the Windows Explorer, My Computer, or the MS-DOS copy or move commands. Once you have moved a database or table, you can use the Linked Table Manager to refresh links to its linked tables.
· The Linked Table Manager can't refresh links to Microsoft Access tables whose names were changed after they were linked. You must delete the current link, and then link such tables again.
----------
That's all.
Dedushka
ASKER
Rejected only because I need some more information. I like this answer, but I can't tell if I have this feature on my version of Access at home. It is NOT installed in the networked copy we use at my workplace, so I can't evaluate the suggestion right now.
The Access97 installed on my systems at home have two origins. The one on my Win95
box where I'd used the module I was hoping to recover here probably doesn't have it, since when I installed Access there I opted for no special additional features (short on disk there).
The Access97 on the new Win98 box I'm trying to move stuff over to was factory installed so I have no idea if (1) the add-in is already installed or, (2) if it isn't if I've got the right stuff in the cabinets on the hard drive to install it myself.
If I can get it, or already have it, installed, I'll try the suggestion tonight and respond back. Thanks!
The Access97 installed on my systems at home have two origins. The one on my Win95
box where I'd used the module I was hoping to recover here probably doesn't have it, since when I installed Access there I opted for no special additional features (short on disk there).
The Access97 on the new Win98 box I'm trying to move stuff over to was factory installed so I have no idea if (1) the add-in is already installed or, (2) if it isn't if I've got the right stuff in the cabinets on the hard drive to install it myself.
If I can get it, or already have it, installed, I'll try the suggestion tonight and respond back. Thanks!
Below is a function using the method similar to what obregoru eluded to. You can use in a routine that loops through all of your tabledefs.
Dedushka's answer is the easiest in my opinion though.
'sLink=name of tabledef
'sTableName=name of table in backend database
'sDBPath =path to backend database
Function ReconnectLink(sLink As String, _
sTableName As String, sDBPath As String) As Boolean
On Error GoTo ReconnectLink_err
dim db as database
Dim MyTbl As TableDef
Set db = CurrentDb()
db.TableDefs.Delete (sLink)
Set MyTbl = db.CreateTableDef(sLink)
MyTbl.Connect = ";Database=" & sDBPath
MyTbl.SourceTableName = sTableName
db.TableDefs.Append MyTbl
db.TableDefs.Refresh
Set MyTbl = Nothing
db.Close
RecconnectLink = True
ReconnectLink_exit:
Exit Function
ReconnectLink_err:
If Err.number = 3265 Then
'tabledef does not exist
Resume Next
Else
'other error occurred, return false
RecconnectLink = False
End If
End Function
Wayne
Dedushka's answer is the easiest in my opinion though.
'sLink=name of tabledef
'sTableName=name of table in backend database
'sDBPath =path to backend database
Function ReconnectLink(sLink As String, _
sTableName As String, sDBPath As String) As Boolean
On Error GoTo ReconnectLink_err
dim db as database
Dim MyTbl As TableDef
Set db = CurrentDb()
db.TableDefs.Delete (sLink)
Set MyTbl = db.CreateTableDef(sLink)
MyTbl.Connect = ";Database=" & sDBPath
MyTbl.SourceTableName = sTableName
db.TableDefs.Append MyTbl
db.TableDefs.Refresh
Set MyTbl = Nothing
db.Close
RecconnectLink = True
ReconnectLink_exit:
Exit Function
ReconnectLink_err:
If Err.number = 3265 Then
'tabledef does not exist
Resume Next
Else
'other error occurred, return false
RecconnectLink = False
End If
End Function
Wayne
or you could just type
subst x: d:
in a command window
subst x: d:
in a command window
ASKER
I've been able to follow the recommendations of Dedushka after locating and adding the Link Manager add-in. While somewhat time consuming, it still took less time than the code I thought I'd need to recover would have taken to type in and debug.
While Poddy's answer seems simplest, it also is not something I want to have to remember
to do EVERY TIME. Dedushka, please repost your suggestion so I can grade it.
While Poddy's answer seems simplest, it also is not something I want to have to remember
to do EVERY TIME. Dedushka, please repost your suggestion so I can grade it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In paragraph 1, that's a "usenet newsgroup" not a "senet newsgroup." I can see lots of kids searching valiantly for a new service called "senet" right about now ... ;-)
No, I didn't buy the source code for the VB application. It wasn't offered. I DID buy the application, however. So this is legal.