[MavDog]

I have a Access database that can be copied into multiple directories. I have a table in this database that is linked to a file that is in the same directory. Is there a way to generically name the file so it will always look for it in the local directory? Is there a way to have Access prompt you for the location and filename when you open it up? I tried to do this manually with the Linked Table Manager but I get an error becasue the filename that I am selecting is a different name. Thanks

[MavDog]

Does anyone have any ideas? Thanks

[Chris_E]

Hi MavDog

I'm a bit confused by yur requirement.... maybe you could describe it a little more clearly?

In particular "I have a table in this database that is linked to a file that is in the same directory"

So if a database called ABC.MDB file resides in (example) C:\ there is a table in ABC.MDB that is linked to a table in a second database called ZXY.MDB in C:\

But if you have a copy in D:\ABC.MDB, you need to be able to relink the table to the D:\ZXY.MDB file

Is that it?

If so, follow this link to http://www.mvps.org/access/tables/tbl0009.htm it may start you in the right direction.

Its a VBA thing!

Chris

[MavDog]

Sorry my email was not clear. Hopefully this will clear it up.

I have a program called MyProgram.mdb currently this is located in C:\Dir1. Inside of this program I have a linked table to a text file called Dir1File.txt. This all works fine.

Now I want to copy the MyProgram.mdb to a different directory called C:\Dir2. I now need to update the linked table to use Dir2File.txt instead.

I am looking for a way that this link can be updated.

One way would be to update it when you open MyProgram.mdb

The other way would be to rename the text file to File.txt instead and this would be the same name in all the directoruies. In this case the link would just need to look in the same directory as where the MyProgram.mdb is. I have looked at the linked tables and it seems like they always have a full path associated with them and this is what is causing the problems. I just want it to look in the local directory.

Thanks

[Chris_E]

I've never come across use of linked tables to text files.

You say

I have a program called MyProgram.mdb currently this is located in C:\Dir1. Inside of this program I have a linked table to a text file called Dir1File.txt. This all works fine.

By this I can only assume that you have a field in your table that provides a link (HyperLink) to a .TXT file in a directory?

Before proceding more, is that the case or are you REALLY linking a table to a TXT file?

Can you put some dummy data in a copy of the MDB, ZIP it and post it? Then we can all have a look for you!

Cheers

Chris

[OBP]

MavDog, why are you linking to the table rather than importing in to the database?

[MavDog]

Hi Chris,

Yes I am really linking to a .txt file.

I have attached the MDB. The DX_BOM table is the one that needs to be updated. Right now it is pointing to C:\HWE\Projects\A0213-2007\Schematics\A0213-2007_BOM_MAX.txt when I copy the MDB to a different directory I would want to link to C:\HWE\Projects\A0123-2007\Schematics\A0123-2007_BOM_MAX.txt instead.

Does that make sense?

[MavDog]

Because the data in this table could be changing I figured a linked table made more since then doing an import every time I wanted to do a compare with the table.

[MavDog]

Hi Chris,

Is what I am looking for clear now or do I need to explain it further? Thanks

[Chris_E]

Sort of but can you send a copy of the C:\HWE\Projects\A0213-2007\Schematics\A0213-2007_BOM_MAX.txt
as an example.

You say that you are linking to a txt file because the data in this table could be changing".

How will it change?

I think the ultimate question is "why can or would the DB be moved or copied into different folders?

Why not create a folder in "My Documents" and always reference that folder, regardless of where the MDB is located?

Chris

[MavDog]

Hi Chris,

I have attached the txt file.

The text file is a Bill of Materials that is generated from a schematic program. As changes are made to the schematic the BOM gets updated.

The reason that it will be moved is because multiple people will be using it. Each person is working on a different project and that is the reason the A# will be changing. Also people can be working on more than one project at a time and this is another reason they would have multiple copies. One copy in each project directory. I understand that I could limit it to one directory that it would run out of and copy and rename the text file into that directory but that opens up the posibilty that the latest data would not be in there. I am trying ot make this as generic as possible so that it will be foolproof and useful.

Hopefully my explanation is making sense.

Thanks -Kevin

[OBP]

This is very bad database usage, having multiple copies of the database is not a good idea, the whole point of Access databases is to make the same data available to everybody, not everybody have their own copy.
You could have a completely automated Import of the BOM file when the database is opened or at the same time every day.

[MavDog]

I am using Access mainly so that I can link to a ODBC and then do a compare. This is really not a database app in which the generated tables are used for anything else.

Everyone will have access to the same shared database that I am comparing to. Since each person has their own project they are working on there is no database to share for the txt file since they are unique.

An import would possible work but I would still need a way forit to ask the user for the location of their txt file to import. I would need to update the imported data each time I do the compare though to make sure it is the latest data. Wouldn't this be the same whether it is an import or linking ot a table? If not I am glad to go the import way if that is easier.

Maybe I am not explaining my application very good becasue I don't see any issues with what I am trying to implement. As I mentioned I know that my app is not the average way a database is used but it seemed like the best program for what I was trying to do. So if you don't think that Access is the right program for this then what would you suggest? Thanks -Kevin

[OBP]

Kevin, can't the Database and text fiel be stored on a server?
That is the normal way to locate a file so that evryone can use it.
Or does everyone have a different version of the txt file?

[MavDog]

Yes everyone has a different version of the text file. Think of is as one person designing a DVD player and another person designing a TV. Both people with have totally different text files for the parts used on their design. This is the unique part. The unique text files are however compared against a shared database.

Here is a little more of an explanation. One database is the text file that is generated by a schematic program. This data is then entered into a manufacturing software system.

At some point later in time, for example when we need to make a change to the DVD design I want to run this compare utility to verify that the BOM items in the manufacturing software and the schematic are the same before we make any changes. Maybe a resistor value was changed in the manufacturing software but was not changed in teh schematic. This compare that I have written will point that difference out.

Is this a better explanation or am I making it more confusing?