Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    4

    Exclamation Unanswered: mutiple insert statement problem

    hi there,
    I have a question regarding insert multiple data into multiple table
    The program work fine when i only insert into 1 table but inserting another set of data into another table the program won't do it
    this is what i wrote

    Dim rs As New ADODB.Recordset
    Dim rs1 As New ADODB.Recordset
    Dim path As String

    Private Sub Command2_Click()
    Dim a As String

    path = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\present\vb\maintenance.mdb"

    a = "INSERT INTO MachineStatus(machineCode,machineInfo,expiretype," & _
    "hourCode,expireCode)VALUES('" & machcode & "','" & machinfo & _
    "','" & maintenancetype.Text & "','" & hourcod & " ','" & expirecod & "')"

    rs1.Open a, path, adOpenStatic, adLockOptimistic
    'rs1.Update

    'rs1.Close

    a = "INSERT INTO MachineName(machineName,MachineCode)VALUES('" _ & name1.Text & "','" & machcode & "')"
    rs2.Open a, path, adOpenStatic, adLockOptimistic
    'rs2.Update
    'rs2.Close

    Note: I've try use update and close adding to it but it still won't work
    many thanks
    sun

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: multiple insert statement problem

    Using a recordset with an INSERT statement doesn't make much sense because the recordset will not return any data. Instead, do something like this:

    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim path As String

    Private Sub Command2_Click()
    Dim a As String, n as Long

    path = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\present\vb\maintenance.mdb"

    Set con = New ADODB.Connection
    con.ConnectionString = path
    con.Open

    a = "INSERT INTO MachineStatus(machineCode,machineInfo,expiretype," & _
    "hourCode,expireCode)VALUES('" & machcode & "','" & machinfo & _
    "','" & maintenancetype.Text & "','" & hourcod & " ','" & expirecod & "')"

    Set cmd = New ADODB.Command
    With cmd
    .ActiveConnection = con
    'I might be wrong about adCommandText,
    'I don't have VB on the machine I'm writing this on
    .CommandType = adCommandText
    .CommandText = a
    'After execution n contains the number of records processed
    .Execute n
    End With

    Etc. for the second INSERT statement

    Hope this helps.

Posting Permissions

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