link some data from a table (1 Viewer)

[rainbows]

hi , i have a table that stores all the records for 4 companies. it is possible to link that table to another access database but only the information from one of the companies not all 4

thanks
steve

 

[GPGeorge]

hi , i have a table that stores all the records for 4 companies. it is possible to link that table to another access database but only the information from one of the companies not all 4

thanks
steve

Not directly, no.

There are ways to accomplish the goal, though. Why is this necessary? What will that second Access relational database applications do with that linked data?

For example, you can create a query that filters the records in the source table to one of the companies, and then use that query rather than the table. Does the data need to be updateable in the second Access relational database application, or is this read-only data?

Thanks for adding enough detail to focus on more appropriate suggestions.

 

[Gasman]

hi , i have a table that stores all the records for 4 companies. it is possible to link that table to another access database but only the information from one of the companies not all 4

thanks
steve

Create a query for each of the companies in that DB, then link to the required query?

Edit: Sorry, it is tables only that can be linked, so use Make Table queries?

 

[rainbows]

well i have 2 databases
1 called action requests that has the 4 companies in it
2 the other database is used for sales , purchasing, manufacturing etc

at this time on the second database we have products which have a bill of materials attached to them which if that product is sold it generates works orders etc . but if we need to do some internal work ( testing of products etc) we still have to create products even if no bill of materials is needed. so i dont want this database for doing this type of work. therefore i was thinking how i could do it in database one as i think it is more of an action request , but i want to get reports from database 2 as these reports are more relevent to that database. so i was thinking like you said if i create a query in database 1 to give me all the records for one company how can i get that query into database 2 and create reports or forms etc but both databases must have the link between them
hope this makes sense

thanks

steve

 

[Pat Hartman]

If you convert the BE to SQL Server, you can do this by using a View. You create a View that returns data only for the company that needs to link to the other FE. Then link to the View. It works just lkie linking to a table.

Access doesn't have the ability to link to a query so you can handle this slightly differently. Link to the table but rename the table after you link it. This only changes the name in the linking database. It doesn't affect the name of the table in the BE. Then create a query that selects only company "b". Save that query with the name of the actual table. So.
link tblCompany. Rename it to tblCompany_Linked.
Create a query to select company "b" from tblCompany_Linked. Save the query with the name - tblCompany.

Access uses queries and tables interchangeably for most purposes and that is why you can never duplicate a name in the two sets. So, you could never create a query named tblCompany if a table of that name existed because Access would never know whether you were referring to the table or the query.

Using this renaming "trick" should accomplich your goal with no other effort.

 

[rainbows]

"Access doesn't have the ability to link to a query so you can handle this slightly differently. Link to the table but rename the table after you link it. This only changes the name in the linking database. It doesn't affect the name of the table in the BE. Then create a query that selects only company "b". Save that query with the name of the actual table. "


i linked my table called issues and then renamed it issuestest
i created a query that looked for company b and called it " issues" (all works ok)

now i am confused as to what this means

" So.
link tblCompany. Rename it to tblCompany_Linked.
Create a query to select company "b" from tblCompany_Linked. Save the query with the name - tblCompany."

thanks for your help
steve

 

[Pat Hartman]

Then you use the query in place of the linked table in the second FE.