linking tables between two (or possibly more) a2k databases 1

[uscitizen]

let's say there are two a2k databases --- we'll call them Database 'A' and Database 'B'.

Database 'B' would be easier to use if some of the forms on it could access information which already exists in Database 'A'.

So, I guess the question is 'can fields from one or more tables in one database be selectively used as sources of data for one or more fields, spanning one or more tables, in another database?' In both instances, custom forms were authored to capture the data.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[Golom]

Usually ... yes.

You can of course define a table in a different database as a linked table in this (i.e. the one you're using) database. You then treat it as though it was a local table. That is in fact how Front End / Back End systems work. There are a few limitations about what you can do with linked tables (for example, you can't alter the structure of a linked table) but you can seamlessly reference its data.

The other option is the Select ... From tbl IN path syntax that allows specification of an external database within a select statement.

 

[uscitizen]

hey, thanks

i was kind of hoping that'd be the answer. also, i was wondering, in the hoped for case, how you'd reference the individual tables in a query....i guess they have to have unique names.

in the case you close your reply with, could you use a query design view to generate the SQL? my guess'd be 'no'.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[Golom]

You mean the From tbl IN path syntax?

You can sorta, kinda use query design but you would have to manually key everything. For a local table or a linked table the query designer knows all the field names and other stuff about the table so it can make them available to the designer. For a table that is opened with "IN path", the table is opened only when the query runs ... not when it is being designed.

Here's your chance to write raw SQL! Lucky you!

 

[Golom]

Missed the other part of your question ...

Yes ... tables and queries must have unique names.

When you link a table from another database, you can assign any name to it that you want, provided only that you cannot duplicate the name of a table or query in the current database. If you have access do the linking for you (Tables / New / Link) and you select a table from another database with the same name as a table in this database then Access will add a numeric value to the end of the name to make it unique (e.g. if you have myTable and you link to another table of that name then Access will call it myTable1.)

 

[uscitizen]

hey, you've netted a 2 star response. not bad. wouldn't give up my day job though -- still a bit week in the comedy department. thanks!

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[Golom]

I'm weak this week?

 

[uscitizen]

rubit'n my face ;-)

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[st0p]

i've got a question related to this one...

in many cases, our research-company is supposed to give access-files to our clients. only, most of the time we do not give them all of our data, for example most of the time we have to cut out al the personal details of people.

at this moment this is done manually, which ofcourse is not optimal. what i want is to make a link to the data, but i want to select the fields which are linked, so that only the relevant data is in the 'linked' database

ofcourse i could write a macro or a query to provide the data but since we have many different databases for different projects that would mean writing macros or queries for each database. it would be far more convenient if i could just specify the rows i want to link to.

can anybody tell me if this is possible?

 

[uscitizen]

don't know if this'd actually work for your clients, but why don't you have a look at this web link:

http://www.devguru.com/Products/FieldEncrypt/index.html

it just might.

g'dluck!

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[st0p]

no i don't think that our clients would be happy when they recieve databases with encrypted data, but thanks for the tip.

has anybody else got an idea on how to solve this conventiently?