Member Avatar for Athiyarath

Iv 10 Tables consisting of atleast 10 fields each in a single Database. Bt, Im only able to connect 1 table at a time to a form using an ADO Control. Im able to add data from table 'student' to textboxes in my form. Bt, hw can I add data from field(0) of Table 'Student' to Textbox1 and data from field(0) of Table 'Marks' to Textbox2 using VB6??

Private Sub Add_Click()
    If rs.State = 1 Then rs.Close
    rs.Open "select * from student", con, adOpenDynamic, adLockOptimistic
    rs.AddNew
    rs.Fields(0) = (Text1.Text)
    rs.Fields(1) = (Text2.Text)
    rs.Fields(2) = (Text13.Text)
    rs.Fields(3) = (Text4.Text)
    rs.Fields(4) = (Text5.Text)
    rs.Fields(5) = (Text6.Text)
    rs.Fields(6) = (Text7.Text)
    rs.Fields(7) = (Text8.Text)
    rs.Fields(8) = (Text9.Text)
    rs.Fields(9) = (Text10.Text)
    rs.Fields(10) = (Text11.Text)
    rs.Update
    MsgBox " Record Added"
End Sub

What can I do to add fields from 2 different tables to different text boxes within the same form using a single ado control using vb6 and MS Access 2007

Pls email me at : meera.athiyarath@gmail.com

Pls hlp!!

Edited by Reverend Jim because: fixed code formatting
  • 2 Contributors
  • 1 Reply
  • 278 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by G_Waddell

Dani AI

Generated

A concise, practical approach.

’s suggestion to use a JOIN is exactly the right direction if your goal is to display fields from two tables in the same recordset. Use a single SELECT that aliases columns so there are no duplicate names, open that SQL with your ADO connection, and populate the textboxes from the returned fields. If your goal is to insert new rows into two different tables at once, don’t rely on AddNew on a joined recordset — run two INSERTs on the same Connection inside a transaction (so both succeed or both roll back).

Example: read/display fields from both tables with one ADO recordset

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT s.StudentID AS SID, s.StudentName, m.MarkValue AS Mark1 " & _
        "FROM Student AS s LEFT JOIN Marks AS m ON s.StudentID = m.StudentID " & _
        "WHERE s.StudentID = " & CLng(txtID.Text), con, adOpenStatic, adLockReadOnly

If Not rs.EOF Then
    txtStudentName.Text = rs.Fields("StudentName").Value
    If IsNull(rs.Fields("Mark1").Value) Then
        txtMark.Text = ""
    Else
        txtMark.Text = rs.Fields("Mark1").Value
    End If
End If

rs.Close: Set rs = Nothing

Example: add related rows into two tables (use transaction)

On Error GoTo ErrHandler
con.BeginTrans

con.Execute "INSERT INTO Student (StudentName) VALUES ('" & Replace(txtStudentName.Text,"'","''") & "')"
Dim rsID As ADODB.Recordset
Set rsID = con.Execute("SELECT @@IDENTITY")
Dim newID As Long: newID = CLng(rsID.Fields(0).Value)
rsID.Close

con.Execute "INSERT INTO Marks (StudentID, MarkValue) VALUES (" & newID & ", " & CLng(txtMark.Text) & ")"

con.CommitTrans
Exit Sub

ErrHandler:
con.RollbackTrans
MsgBox "Error: " & Err.Description

Quick tips and pitfalls

  • Alias columns to avoid duplicate names (e.g., AS Mark1).
  • A joined query can be non-updatable if it uses DISTINCT, aggregates, or ambiguous keys; then you can read but not AddNew/Update.
  • For safety, prefer parameterized ADODB.Command objects (avoid simple string concatenation) to prevent SQL injection and quoting bugs.
  • If you use the VB6 ADODC on the form, set its RecordSource to the JOIN SQL and bind DataField/DataSource on controls.

This approach keeps one ADO connection/control for display while using explicit, safe statements for multi-table inserts/updates.

Member Avatar for G_Waddell

You could JOIN the tables using SQL Joins in your query...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.