Access Projects, SQL Server, and 1-to-1 joins

Hello everyone,

I'm working on porting an Access Application with linked tables to an Access Project, to make it faster. The backend is (unsurprisingly) MS SQL Server.

My problem is thisL:

I have a form (well, several forms) which are sourced from a 1 - 1 join
Person <-> Candidate

It seems that I can only edit one of these tables at a time, the one specified by the UniqueTable property. I want to have access to the whole record at once. In the Access documentation, it doesn't seem to mention this case, which seems a little odd.

Adding records is not a problem, it's only updating, and it can be guaranteed that a Clienta and Person record both exist.

I don't think I can enforce referential integrety, as Person also links to Client, so a Person record might not match to a Candidate record.

How can I set up the database or form to permit the editing of both tables simulatenously?

Thanks,
SamJust a suggestion but while you are porting this thing over why not de-normalize these tables into one table with a Type field (person, client, candidate)?

This will make life much easier...|||The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Hi,
I was searching and found your thread. I am having the exact same problem. I have a "Person" table and other subtypes of it like "Contatc".
On my contact form I have a record source with a 1-1 join on tblContact and tblPerson (when a user is inserting a contact a VB code would insert the new assigned number from the Person table to the Contact table)

Now that I'm trying to use Access projects it won't work. Its driving me nuts. I've tried every trick I know and it won't work.

By the way I am very suprised that my ODBC connection is much faster than my Access project, at least for data entry. When I'm using Access project and when I start typing in a field it pause for a second and then the characters apear. Thats wierd, do you know any thing about it?

Originally posted by Digitaleus
The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||You mean instead of having a join query as the record source of the form, us a wizard to sync them (we can also drag and drop)?

Thanks

Originally posted by chrisp_999
Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||Assume you have frm_person and frm_contact as subform of frm_contact.

Datasource for frm_contact is
select * from tbl_person

Assumed you have a field ID in tbl_person, datasource for frm_contact is

select * from tbl_contact where personID = @.ID

and the Input Parameter property for frm_contact is

@.ID int = forms!frm_person!ID
(or txt_ID or whatever the name of the field is)

Anytime the record on frm_person is changed, issue (in VBA)
forms!frm_contact.requery.

That's the basic scheme. Details vary depending on wether you have an endless form or not and so on.

BTW, with MSSQL you can use a trigger to create the entry in tbl_contact instead of VBA. So you ensure data integrity within the database (e.g. if you insert manually).|||correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.|||Thanks

Originally posted by chrisp_999
correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.