Application Note for QuNect ODBC for Quick Base

Inserting and Adding Records to a Quick Base Table from VBScript

This will insert several records into the table with a DBID of bce33vr7t. You can find the DBID of your Quick Base table by reading the How to Find the DBID of a Quick Base Table application note.

Quick Base behaves a little differently from most databases when doing inserts. If you specify the value of the key field when doing an insert (which you have to do if the key field is not the built-in field initially called Record ID#), and a record already exists with that key value then no new record is created. Instead the record with the matching key field value is updated with the values in the insert statement.

This example uses a parameterized query.

    dim connString
    Dim rs
    set rs = CreateObject("ADODB.RecordSet")
    rs.CursorLocation = adUseClient
    Dim OStr
    dim fStr
    Set adoComm = CreateObject("ADODB.Command")
    adoComm.CommandType = adCmdText
    connString = "DSN=QuickBase via QuNect;"
    adoComm.ActiveConnection = connString    
    adoComm.CommandText = sSQL
    adoComm.Execute , , adExecuteNoRecords
    sSQL = "INSERT INTO bce33vr7t"
    sSQL = sSQL & " (""A Text Field"", ""A Number Field"")"
    sSQL = sSQL & " VALUES (?, ?)"
    adoComm.Parameters.Append adoComm.CreateParameter("p1", adChar, adParamInput, 255)
    adoComm.Parameters.Append adoComm.CreateParameter("p2", adDouble, adParamInput, 15)
    adoComm.CommandText = sSQL
    'Now you can loop and insert multiple records
    dim i
    for i = 1 to 100
        With adoComm.Parameters
          .Item("p1").Value = "This is record " & i & " of 100 that were inserted with VB Script"
          .Item("p2").Value = i
        End With
        adoComm.Execute , , adExecuteNoRecords
    set adoComm = nothing