Inserting and Adding Records to a Quickbase Table from VBScript

Application Note for QuNect ODBC for QuickBase

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

Quickbase 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
    next
    set adoComm = nothing