VBA Functions for working with Pervasive SQL (PSQL)

The following function will set up your connection to a Pervasive SQL database. You will need to set a reference to the Microsoft Active X Data Objects library. This function creates a DSN-less connection that will allow you to execute pass through queries using raw SQL. Pass through queries require more knowledge of SQL than using linked tables and the Access query builder. But linked tables can be a hassle to deal with and create numerous maintenance headaches. And pass-through queries are more versatile and run more efficiently than linked table queries.

Public Function psqlConn(dbName as String) As ADODB.Connection

    Dim conn As New ADODB.Connection
    Dim strConn As String
    strConn = "Driver={Pervasive ODBC Client Interface}; ServerName=SomeServer.1583;ServerDSN=" & dbName & "; UID=Uname; PWD=Pass; ArrayFetchOn=1; ArrayBufferSize=8; TransportHint=TCP:SPX;"
    conn.Open strConn
    Set psqlConn = conn
 
End Function


The next function is for SQL SELECT queries. You'll use the previous function to set up the connection and pass it as the conn argument to the function. The function returns and ADODB record set. Remember to close the connection when you are done with it (conn.Close). 

Dim conn As New ADODB.Connection
Set conn = psqlconn(dbName)
dim strSQL as String

strSQL = "SELECT * FROM SomeTable WHERE id = '" & varID & "'"

Public Function psqlQuery(conn As ADODB.Connection, strSQL As String) As ADODB.Recordset

    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandTimeout = 120 ' Keep queries from timing out after the default of 30 seconds
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    Set rs = cmd.Execute
    
    Set psqlQuery = rs
    

End Function


The next function is for Insert, Update, and Delete queries. 

Public Function psqlNonQuery(conn As ADODB.Connection, strSQL As String) As Boolean

    Dim cmd As New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandTimeout = 120 ' Keep queries from timing out after the default of 30 seconds
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText
    cmd.Execute
    
    psqlNonQuery = True
    
End Function