Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2001
    Posts
    2

    Unanswered: 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,
    Sam

  2. #2
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    4
    Just 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...

  3. #3
    Join Date
    Nov 2001
    Posts
    2
    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?

  4. #4
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    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?

  5. #5
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    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.

  6. #6
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    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.

  7. #7
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    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).

  8. #8
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    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.

  9. #9
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •