Referencing dates from a different DB (1 Viewer)

[SteveF]

Hi Guys,

I hope I can explain this properly.

I have a stock and audit database setup with a folder called 'Outlets', within the outlets folder there are many sets of tables storing data for the various sites we work on.

In a separate database I have a scheduling setup which holds site details for all the outlets and I use this for scheduling work.

Currently, I manually enter the last date that the job was done, taken from the site table within the outlet folder, into the scheduling database and then the scheduling database produces a report which I use to fill my diary up.

Would it be possible to have the scheduling database reference the date held in the table of the site database?

The two databases have no relationship at all and are in different folders on the same computer. Can I persuade Access to reference a field in a table held in a different folder?

Thanks in advance for help.

 

[pbaldy]

Have you considered linking the table?

 

[SteveF]

I did yes, but I don't think that's the answer. The problem is that the date I want to reference is held in around 100 different databases and is called the same thing 'Date Done'.

All the databases I'm trying to reference have unique names but the same structure and table names. When I tried to link it failed.

 

[Pat Hartman]

Is the date in 100 tables or in 100 databases? How do you decide which source has the date you are looking for? What error message are you getting when you attempt to link to the database and table in question?

 

[SteveF]

Hi Pat, thanks for looking.

It's in 100 databases but all the databases have the same structure. In every case the date i'm trying to reference is in a table called 'tblJob' and the field is called 'Date Done'.

I will report back on specific wording of the error message, but it's 'could refer to more than one' and I assumed that this was because the data I was trying to link to had the same name in each case.

 

[Pat Hartman]

You still haven't said how you would determine which database to link to. Surely you don't need to link to 100 of them.

I also might add that this sounds like a design problem and you may want to revisit why you have a hundred databases. Access is not Excel. You should probably have one database and the table should have a column indicating the "source" of the data.

 

[SteveF]

Well, the date that I want to extract is different in each case so yes I need 100. It's a front end / back end split so the back ends are just sets of tables.

This is something of an add on to my database and not part of the original concept but as we get busier I find more things that I think Access may be able to help with, scheduling is another one of those things you know?

I don't know if it's 'do-able' or not, just trying to explore the options.

 

[Pat Hartman]

You can write code that loops through the site directories, links to the specific table in the database found in that directory, does something and moves on.

Let me say again. This set up is very bad. You are not working with Excel. You are working with Access. There is no need to maintain a hundred different databases.

 

[SteveF]

Thanks for looking Pat, all thoughts appreciated.

I'm sure from a design perspective my application is all wrong, but it does everything that the market leading packages do and a few things that they don't as well. It's a triumph of optimism, ignorance and determination; enough to make a purist weep I suspect

 

[gemma-the-husky]

steve

obviously i know where you are coming from.

simply link to the table in the outlet you require, and do a dlookup on the table.
when you want to use a different connection again, drop the link, and build another one.

i think you will want createtabledef in the dao library.

 

[SteveF]

Thanks Dave, I will give that a go this evening.