Author: Dave Page (firstname.lastname@example.org)
Release Date: 13 November 2001
Description: Example based Mini-Howto on Accessing PostgreSQL from Visual Basic
This document provides some sample code to get you started with Visual Basic & PostgreSQL.
Requirements to get the subroutines to work:
CREATE TABLE vbtest(
Sub Main() Dim cn as New ADODB.Connection Dim rs as New ADODB.Recordset 'Open the connection cn.Open "DSN=<MyDataSourceName>;" & _ "UID=<MyUsername>;" & _ "PWD=<MyPassword>;" & _ "Database=<MyDatabaseName>" 'Clear the table cn.Execute "DELETE FROM vbtest;" 'For updateable recordsets we would typically open a Dynamic recordset. 'Forward Only recordsets are much faster but can only scroll forward and 'are read only. Snapshot recordsets are read only, but scroll in both 'directions. rs.Open "SELECT id, data, accessed FROM vbtest", cn, adOpenDynamic, adLockOptimistic 'Loop though the recordset and print the results 'We will also update the accessed column, but this time access it through 'the Fields collection. ISO-8601 formatted dates/times are the safest IMHO. While Not rs.EOF Debug.Print rs!id & ": " & rs!data rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss") rs.Update rs.MoveNext Wend 'Add a new record to the recordset rs.AddNew rs!id = 76 rs!data = 'More random data' rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss") rs.Update 'Insert a new record into the table cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random data');" 'Refresh the recordset to get that last record... rs.Requery 'Get the record count rs.MoveLast rs.MoveFirst MsgBox rs.RecordCount & " Records are in the recordset!" 'Cleanup If rs.State <> adStateClosed Then rs.Close Set rs = Nothing If cn.State <> adStateClosed Then cn.Close Set cn = Nothing End Sub
' The escapeString function can be used to fix strings for us in INSERT and ' UPDATE SQL statements. It will take a value, and return it ready for ' use in your statement as NULL, or quoted with backslashes and single quotes ' escaped. Function escapeString(vValue As Variant) As String If IsNull(vValue) Then escapeString = "NULL" else escapeString = "'" & Replace(Replace(vValue, "", ""), "'", "''") & "'" end if End Function