How to run an insert query on VB 6.0?

Question

I am New to Visual Basic 6.0 and I don't know how to code SQL queries, specially Insert Into

I have 1 database with 2 tables(tblNames, tblRemarks)..

in tblNames the fields are:

ID, LastName, FirstName, MidName, DateHired, Position, Department

in tblRemarks the fields are:

ID, FullName, txtDate, Remarks, DateHired, Position

What I am doing is like this:

SQL =  "SELECT ID, LastName, FirstName, MidName, DateHired, Position FROM tblNames"
SQL2 = "SELECT * fROM tblRemarks"

and then on my form I have a DTPicker1 and Command1

I want to get all the records in tblNames and put it on tblRemarks, but also get the date from DTPicker1 and in Fields Remarks, the word "HOLIDAY".

What I'm doing is like this: om my Module I have this code:

Option Explicit
Public conn As New ADODB.Connection
Public RS As New ADODB.Recordset
Public cmd As New ADODB.Command
Public SQL As String
Public SQL2 As String

Public Sub connectDB()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\Database.accdb;User ID=admin;Persist Security Info=False;JET OLEDB:Database Password=qqqq"
conn.Open

With RS
    .ActiveConnection = conn
    .Open SQL, conn, 3, 3

End With
End Sub

Public Sub connOpen()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\Database.accdb;User ID=admin;Persist Security Info=False;JET OLEDB:Database Password=qqqq"
conn.Open

With cmd
    .ActiveConnection = conn
    .CommandType = adCmdText
    .CommandText = SQL
    Set RS = .Execute
End With
End Sub

Then I just call that in my forms

my problem is this, I have 2 SQL queries, how can I connect these 2 SQL in my connection? I researched it, and found out I can do 1 QUERY by using INSERT.

I tried, but I cannot make it work. How can I achieve this? I am very new to VB6. Would it be like this?

SQL = "INSERT ID, LastName, FirstName, MidName, DateHired, Position FROM tblNames" _
& "INTO tblRemarks, #" & DatePick & "# as txtDate, 'HOLIDAY' as Remarks"

when you say "connect" - do you want to join the both queries or to union? — Kai Walter, May 27 '20 at 09:46
Im sorry? what do you mean by union? I just want to get the records from tblNames and put it all to tblRemarks. BUT! also add the date from my DTPicker1, put it to the Fields date, and the word HOLIDAY put in Fields Remarks in tblRemakrs — ScoRm, May 27 '20 at 09:52
See https://stackoverflow.com/questions/25969/insert-into-values-select-from — , May 27 '20 at 15:33

score 0 · Answer 1 · answered May 27 '20 at 20:39

Totally untested but perhaps you can try the following

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\Database.accdb;User ID=admin;Persist Security Info=False;JET OLEDB:Database Password=qqqq"
    conn.Open

    SQL = "INSERT INTO tblRemarks " & _
          "(FullName, txtDate, Remarks, DateHired, Position) " & _
          "SELECT CONCAT_WS(' ', FirstName, MidName, LastName), ?, 'HOLIDAY', DateHired, Position " & _
          "FROM tblNames"

    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdText
        .CommandText = SQL

        .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, 100, DatePick)
        .Execute
    End With

Replace 100 with the width of your txtDate column.

You may also want to try `.CommandType = adCmdText And adExecuteNoRecords` — , May 27 '20 at 20:45

How to run an insert query on VB 6.0?

1 Answers1