Best way to import badly formatted data

[mkallover]

I have an Access DB that I maintain for my department and that DB links to a couple of tables maintained by another department. Those tables do not have Primary Keys defined and because of that, whenever I create a query that involves that data, the data cannot be updated.

We never really need to update their data, only our own but because of how integral their data is to ours, it would be a design nightmare for me to create separate subforms with the imported data.



Can any of you think of any ways to get around this? I've tried to get in touch with the other department but they have been unresponsive to say the least.

[ajetrumpet]

ways to get around what exactly? why are you importing the data? isn't the db on a network and shared? what is that situation like?

what do you mean exactly when you say the db is linked to a couple of other tables? how many files are you working with? how many FEs and how many BEs?

[mkallover]

The data is shared on a network but their tables are designed in a way that is causing any query I create where I join our data with theirs to be non-updateable. Essentially they are both project databases - ours contains only information relative to projects sponsored by our department and the other database is more enterprise wide. I link the projects by a company project number but on their table they have no primary key defined and I think that is why my queries end up being locked.

We have a front end that links to our back end as well as their back end.

I'm wondering what would be the best way to deal with this situation without having to create a bunch of subforms. The data I pull from their tables is pretty integral to ours so it's not the best design solution to just put their data in a subform.

[NTC]

A query can be non-updateable if there is an uncertain 1:many relationship; or it could be that you simply do not have write capability to their db. The latter you can check simply by opening one of their tables directly and attempting to change a value. If you can do a manual change to their table; set up a relationship between the two tables. But you are right in that with no primary key existing it may not work.

since you are only working with "a couple" of their tables - then there is only "a couple" of subforms that need to exist; so am not sure why that is such a burden - - but will accept you have issues with this approach; so that leaves you with one other alternative which is to write their table to a new local table. Have that occur when the FE opens. Make your query now with this new local table - not their live table. You can add a primary key to the new table - do your join - everything will be updateable; but of course their data is temp (local) so no change in their data goes back to them - - so to be safe you might want to lock those fields in the forms to keep users from thinking they are/can change the other db's data...

hope this helps.