Referencing dates from a different DB

[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.

 

[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.

 

[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.

 

[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.