Application Note for QuNect ODBC for QuickBase

Inserting and Adding Records to a QuickBase Table from VBScript

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. In order to make adding many records efficent you need to tell QuNect ODBC for QuickBase to cache all the inserted records and then write them all at once. This is accomplished by putting QuNect ODBC for QuickBase into a special mode. This can be accomplished with and INSERT statement into a virtual table called "QUNECT CONNECTION CONFIG". There is no such table in QuickBase. The QuNect ODBC for QuickBase driver recognizes this as a special virtual table with two columns "qPARAMETER" and "qVALUE". If you set the qPARAMETER column to 'SAVE_WRITES_UNTIL_CONNECTION_CLOSE' and the 'qVALUE' column to 'TRUE' then all writes to all QuickBase tables will be cached until either the connection is closed or another special command is issued telling QuNect ODBC for QuickBase to flush its cache.

    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    
    
    'This causes QuNect ODBC for QuickBase to cache all inserts until the connection is closed
    sSQL = "INSERT INTO ""QUNECT CONNECTION CONFIG"" (qPARAMETER, qVALUE) VALUES ('SAVE_WRITES_UNTIL_CONNECTION_CLOSE', 'TRUE')"
    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
    msgbox "No records have actually been added to QuickBase yet."
    set adoComm = nothing
    msgbox "Now all records have been written to QuickBase."