Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link table (2003)

    I have big problem on my database now.

    Below is what I currently how to set up my database:

    I create one database "A" only have table inside. Then I have another database "B" have query, forms and report, and the table is link the Database A. If I want to make any changes on query, forms or report, I will do on the copy of database "B". After I done, I paste back the original database B and relink the table of database A. The reason that I am doing because when I doing any update I don't have to effect users to use the database and the data still there since I put data on the database "A". Also most time I do update datase at home. The uses use it at work.

    I recently found out that the relationships was gone after i delete table and relink to database A. So is any suggession to slove my problem?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    What relationships disappear? You stated that you have only one table.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    Sorry for unclear information. Let me try to explain again,

    That database is set up at work for users to use. But most time I do update or change of database at home.

    That's why I put all tables (6 tables) in database A, then I put query, forms and report in databse B and I link the table from database A. If I want to bring it to home, I copy database A & B to my usb drive. When I back to office next day, I copy and paste udpate database B to network drive at work. Then I delete tables of udpate database B and relink it with Database A at work. So in this case users have accruate data.

    But everything when I delete table from udpate database that I work at home, the relatinships also deleted.

    So what I can do with my suitation?

    Also another issue is I want users still can use the database when I doing update or charge it at the same time. That's another reason why I put tables on the datbase A.

    Regards

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    You should define the relationships between the tables in the backend database (Database A), not between the linked tables in the frontend database (Database [img]/forums/images/smilies/cool.gif[/img].

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    If I define the relationships between the tables in the Database A (only have tables). Should I define the relationships in the Database B ( have query, forms and report) again?

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    > Should I define the relationships in the Database B ( have query, forms and report) again?

    No, when you link the tables from Database A in Database B, the relationships between the tables will automatically be included.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    Yes. It is automatically.

    But in database A, I check "Enforce Referential Integrity" & "Cascade Update Related Fields" & "Cascade Delete Related Records," of the relationship of table. After i link it in my database B, the relationship between between the tables only have "Enforce Referential Integrity" checked. I did test three times.

    Is this normal or I did anything incorrect?

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    Don't worry. The "Cascade Update Related Fields" and "Cascade Delete Related Records" properties will still be applied, even if Access doesn't show them to be set in the frontend. If you would delete some records from a table, Access would ask you whether it is OK to remove related records from other tables too.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    Thanks a lot.

    But I have a problem to delete records form the query. After I click yes when Access ask me whether it is ok to delete the records, I reopen the query again the data still there. Can you help me to take a look the attached file?

    Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    You shouldn't delete records from a query based on two tables, because it is unpredictable what will actually happen. In this case, you'll only delete records from tblRequestDetail, not from tblRequestInformation, even though Access seems to do so. (This is a minor bug in Access)
    If you delete a record from tblRequestInformation or a query based only on tblRequestInformation, Access will delete that record and any related records from tblRequestDetail.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    Yes. It deleted now. But I have to use query base on both tables, because I let user to search base on the criteria then delete the records that user want to delete. If like this case, I only can delete records from the query base only tblRequestInforamtion, how can users to look at data have in tblRequestDetial in search result.

    Thanks

    Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    Use a main form bound to tblRequestInformation and a subform based on tblRequestDetail.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    Thanks.

    One more question. I write code to run query, but I want to know how can I delete the record in query by using code???

    Thanks

    Regards

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link table (2003)

    1) In a form bound to a table (or query), you can delete the current record:

    RunCommand acCmdDeleteRecord

    If you want to suppress the usual warnings:

    DoCmd.SetWarnings False
    RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True

    2) In general, you can execute an SQL statement in code:

    Dim strSQL As String
    strSQL = "DELETE * FROM [NameOfTable] WHERE [ID] = " & Me.txtSearch
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    As always, you should substitute the correct names, and you can modify and expand the criteria (the last part of the SQL string) as needed.

  15. #15
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link table (2003)

    Yes. It works.

    This is what I do, I have "Delete" button on the form, and users click "Delete" button, the query pops up with delete confirm message.

    My another question is how can I hide query window? Just confirm message appear. Please see below my code:

    Private Sub DeleteCurrentIssue_Click()

    DoCmd.OpenQuery "qryDeleteData"
    RunCommand acCmdDeleteRecord

    End Sub

    Thanks

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

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