ambiguous join error (1 Viewer)

[lution]

I'm needing to pull information from one access database into another. The database that I am pulling from has undergone some changes so I can no longer do a straight join to get all the information that I need.

Basic layout:
Primary Table: PRD_Header (key field PRDKEY)
Linked table: NTC_Vehicle (has PRDKEY to link to PRD_Header, and CollKey to link to the Vehicles table)
Vehicles table: has CollKey and some other fields about vehicles

I need to get all the rows from PRD_Header and all the related data from Vehicles if there is any there but not every PRD_Header row has a link to a NTC_Vehicle/Vehicle.

To figure out how to get the data I ended up creating a GetVehicles query that joined NTC_Vehicle and Vehicle and then used that query with PRD_Header but I can't leave the query in the 2nd database and I'm not sure how to do it through vba since I don't have a persistent connection to the tables in the 2nd database.

Make sense?

 

[lution]

I was thinking I could use some queries to pull the data into my primary database where I have the join query for the 2 vehicle tables but I'm concerned about performance if there is a lot of data.

 

[Monardo]

I'm needing to pull information from one access database into another. The database that I am pulling from has undergone some changes so I can no longer do a straight join to get all the information that I need.

Basic layout:
Primary Table: PRD_Header (key field PRDKEY)
Linked table: NTC_Vehicle (has PRDKEY to link to PRD_Header, and CollKey to link to the Vehicles table)
Vehicles table: has CollKey and some other fields about vehicles

I need to get all the rows from PRD_Header and all the related data from Vehicles if there is any there but not every PRD_Header row has a link to a NTC_Vehicle/Vehicle.

To figure out how to get the data I ended up creating a GetVehicles query that joined NTC_Vehicle and Vehicle and then used that query with PRD_Header but I can't leave the query in the 2nd database and I'm not sure how to do it through vba since I don't have a persistent connection to the tables in the 2nd database.

Make sense?

Hi,

Not sure if I got you right, but what do you mean by "...I don't have a persistent connection...". To pull data from one Access file to another you have to link tables. If you have intermittent connection (lets say once a day) then you could import table or just make local copies of the second database file and still use links.

So what exactly is your situation? Did you try linking? Or maybe I totally misunderstood?

 

[lution]

I use Set db = DAO.OpenDatabase(TicketDB, False, True) to open the connection and then build a query in VBA to submit to the database.

 

[Monardo]

Why don't you first Link table(s) from one database to another and than build normal query between linked and local tables?

 

[lution]

Hadn't thought about linking to the tables temporarily. Will give that a shot. I can't keep them as linked permanently though because the database name changes and they won't let me keep the connection open permananetly.

 

[Monardo]

If you have access to the database only once in a while then why don't you import (instead of linking) the table into your file. You can always "re-import" later with newer data when you get access again.