Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Red face Unanswered: VB6.0->ACCESS(Adding records into tables which has relationships)

    hi all.i have problem adding records into 2 tables.when i add record into the first table,it works fine.when adding to second one,it says
    run-time error
    you cannot add or change record because a related record is required in table '2'

    each of the tables in my database is connected by primary and foreign keys.let say table1 is connected to table2 by student_id.i am unable to add record in table2 because i need the data student_id from table1.i'm using two Adodc to refer to each table.
    Code:
    With Adodc1
    .Recordset.AddNew
    .........................
    .Recordset.Update
    End With
    
    With Adodc2
    .Recordset.AddNew
    ..............................
    .Recordset.Update
    end with
    how do i exactly make an addition into the second table?
    any help is greatly appreciated..
    thanx in advance..

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In which of the tables is student_id the primary key (parent), and which has student_id as the foreign key (child)?

    You'll have to add the record to the parent table first.
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    5

    Question reply--VB6.0->ACCESS(Adding records into tables which has relationships)

    student_id was only an example,i'm actually doing a reservation system.so the primary key is reservation_id.do u mean i need to perform transaction one by one?which means i can't perform both transaction in a sungle form??
    thanx again for the help.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You're using ADO Data Controls ADODC's are NOT kind to your database.

    By default, data controls use separate ado connections to connect to a database. By design, with the Access Jet Driver, you will have a delay before a change made on one connection can be seen on a different connection. This delay can be up to 5 seconds.

    Although you can force multiple ado data controls to share a common connection, your BEST fix for this problem (And, I HIGHLY recommend it) is to STOP using ADODC's at all, and instead, use ADO directly.


    Remember, "Friends don't let friends use ADODC..."
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Oct 2007
    Posts
    5
    thanx for the info loguin.so it seems that i cant add datas into tables which has relationships using adodc. i used adodc in all other forms in my project and it works fine.only this one im having problem with could not be settled.i need to submit this project in few days time n with this very limited time i dont think i can change everything to 'not using adodc' i have browsed through the links u gave me,downloaded the tutorials,tried learning from it..will it be ok if i use ado directly into only one of my form and let other forms remain using adodc??the only problem im having is to ADD datas into those tables which i was unallowed because those tables have relationships.i tried using these codes:
    Code:
    Private Sub CmdAddNew_Click()
    Dim conn As New ADODB.Connection
    Dim command As New ADODB.command
    Dim recordset As New ADODB.recordset
    
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
          App.Path & "\" & "FRS_DB.mdb;Mode=Read|Write"
          
       
        conn.CursorLocation = adUseClient
        conn.Open
        
        With command
        .ActiveConnection = conn
        .CommandText = "SELECT * FROM CombiQuery;"
        .CommandType = adCmdText
      End With
      
      With recordset
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open conn 'the error stops here
      End With
    it gives me an error that says "arguments are of the wrong type,are out of acceptable range,or are in conflict with one another"

    have any idea why?
    thanx in advance for any help..

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I haven't used straight vb 6 in a while, does it know about the named ADO constants? It may be complaining about using adParamValue when it's expecting a long.

    Don't quote me on that, but I see that happen in asp and vbscript all the time.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2007
    Posts
    5
    mistake.i found the error.the line
    Code:
    .open conn
    is suppose to be
    Code:
    .open command
    but guess what,im getting the same error "you cannot add or change record because a related record is required in table ***"

    so,i tried using adodc n ado directly as told but both dont seem to be working.please helllppp!!

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You can add data into tables with relationships using ADODC. You just have to make sure that any 'parent' tables are populated first, and that the updated data is reflected in any secondary connections, prior to updating the 'child' table(s.) However, IF you use multiple connections, any changes made to the access tables won't be seen on the other connection(s) for up to 5 seconds. Since ADODC use a separate connection for each control by default, you must either wait 5 seconds before attempting to update the child tables, force all of the data controls used in the update to all use the same connection, or at least migrate data controls used with parent/child table updates over to ADO.

    Since data controls are bound to a VERY old version of ADO, I would recommend that you migrate to ADO, v2.8. Your performance will be better, and you'll get rid of several bugs hiding in ADO 2.1 (which ADODC uses.) You may also run into ADO versioning issues if you use ADODC and versions of ADO > ADO 2.1 ...

    ref the links in my earlier post, above.
    Last edited by loquin; 10-17-07 at 12:45.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Oct 2007
    Posts
    5
    i have read the infos from the links u posted earlier.but how do i actually migrate to the 2.8 version?
    i combined fields from table1 and table2 into a single query and refer the adodc to one table only which was the query table.
    is this possible??

    "make sure that any 'parent' tables are populated first"
    "and that the updated data is reflected in any secondary connections, prior to updating the 'child' table(s.)"
    i dont understand..can you explain further on this..? i got even more confused when reading articles about this..

    when using adodc,my codes are as follows:
    Code:
    With Adodc1 'this adodc refers to the query table
        If Check8.Value = vbChecked Then
          .AddNew
          .Fields("reserv_date") = booking_date   'from table1
          .Fields("fac_id") = fac_type                 'from table1
          .Fields("cust_name") = Text1.Text        'from table1
          .Fields("cust_contact") = Text2.Text     'from table1
          .Fields("booking_time") = Text3.Text     'from table1
          .Fields("time_id") = 8                          'from table2
          .Update
        End If
    table1 is the parent table.from the codes,have i populated the parent table?sorry if im beginning to be a pain..im very new with all this..

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    So, you have a single data control that has opened a recordset using a query that joins the two tables?

    You're going to need to update the parent table first, and then update the 'child' table, separately.


    Issue a SQL statement to insert a new record in the parent table. Then issue a SQL statement to update the child table.

    Although I hate data controls (and I haven't used one in years) , you can use the DC's connection to do this.

    something along the lines of

    Code:
    Adodc1.Datasource.Connection.Execute "Insert into Table1 (Field1, Field2, FIeld3...) Values (Value1, 'Value2', #Value3#)"  ' (in this case, field1 is numeric, field2 is text, and field3 is a date field type)
    Repeat the process for the second insert.

    I still recommend dropping the data controls, and using ADO directly.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  11. #11
    Join Date
    Dec 2015
    Posts
    1

    Use ADO Directly

    Yes ADODC its not a good solution specialy under stress work its dely the connection , i recommend you to use ADO directly and specialy use ( SQL Statement ) by ADO its the best way to work with database enjine even under stress work ... you can use Active Connection Object to execute SQL Statement for inserting , updating , deleting and use Recordset Object to make SQL Statement Query OR Execute Stored Procedures OR any data reading you may need .

Posting Permissions

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