INSERT INTO with MS ACCESS and VB6.0-VBForums
Results 1 to 2 of 2

Thread: INSERT INTO with MS ACCESS and VB6.0

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2009
    Posts
    7

    Unhappy INSERT INTO with MS ACCESS and VB6.0

    Hi everyone.. ,

    I'm trying to create a simple Patient Information System and I have no idea on how to Add a new patient record in the database.
    I know how to add a record using RS.AddNew and RS.Update like:
    Code:
    RS.AddNew
    RS.Fields("PatientNo").value = txtPatientNo.text
    RS.Fields("FirstName").value = txtFName.text
    RS.Fields("LastName").value = txtLName.text
    'And so on..
    RS.Update
    But what I actually want to do is Insert this new record in a specific page number and book number. Because here, I have tables
    PAGE
    PK - PageNo

    BOOK
    PK - BookNo

    PATIENT
    PK - PatientNo
    - FirstName
    - LastName
    - MiddleName
    - Address
    - Age

    'Junction Table for the Ternary Relationship
    BOOKPAGEPATIENT
    PK - BookNo
    PK - PageNo
    PK - PatientNo

    The program goes like this, I open a Book Number, after opening a book number, I can open a Page from this Book that is currently opened. Once that a Page number is opened, I now have some operations like Add, Delete, Save, Edit and Cancel. But I'm actually done with some of the few buttons here.

    My problem only is how to add a new Patient record in the currently opened Book and Page No.

    Here is my code on the database connection which connects to ms access database:

    Code:
    Global DBS As New ADODB.Connection
    Global patientListRS As New ADODB.Recordset
    
    'Database Connection
    DBS.CursorLocation = adUseClient
    DBS.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\CLINIC 97.mdb;MODE=ReadWrite"
    
    set patientlistrs.activeconnection = DBS
    patientlistrs.locktype = adlockoptimistic
    patientlistrs.source = "PATIENT"
    patientlistrs.open , DBS, , , adcmdTable
    Once that I click on the add button,
    The textboxes are cleared for entry of new records and which will enable the textboxes and the Save and Cancel buttons and disable all the other buttons.

    I don't know what code to use when on the save button. Please help me..

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: INSERT INTO with MS ACCESS and VB6.0

    The save part you showed (using .Update) is valid for the first half.

    The part you need to do is add a record to BOOKPAGEPATIENT, using the PatientNo you used already, and the BookNo and PageNo which you also seem to know the values of. Using an Insert Into statement would be a good way to add it.

    For information on how to do that (and comparison of the various methods), see the article How can I add a record to a database? from our Database Development FAQs/Tutorials (at the top of this forum)


    For the part you currently have, it seems that it would be more efficient to not use a recordset there either - because you are currently loading all of the existing data (rather than just relevant data), when you don't even need any of it for adding more data.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.