Access 2003
psqlodbc 7.03.02
postgresql 7.4.3 (cygwin)
I can successfully use tables and views as linked tables in access. Views
can be updated with access forms after writing appropriate update, delete
and insert rules for each view.
ADO pass-through queries will be needed for some reports, in order to pass
runtime parameters.
A test subroutine to create an ADO recordset for a pass-through query on a
view fails on recordset open with the error: column "ctid" does not exist.
Substitution of the SQL statement used to create the view runs without
error.
The docs list ctid as a system column with the current location of the row.
The same problem occurs with single or multitable views.
Is there something additional needed to access a view via ADO, or is this a
limitation of the odbc driver?
The test subroutine follows; I just comment out one of the two recordset
open statements before execution. The mailing list will likely break up the
query string assignment statement into multiple lines. It should just be two
lines, with a continuation character at the end of the first line:
Sub TestODBC()
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim strqry As String
'Open recordset
cnn1.Open "Provider=MSDASQL;DSN=PostgreSQL30;database=emr;UID=postgres;"
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenDynamic
rst1.LockType = adLockOptimistic
rst1.CursorLocation = adUseServer
strqry = "SELECT (patients.acctmaj::text ||
patients.acctmin::text)::character varying AS acctnum, patients.acctmaj,
patients.acctmin, patients.lastname, patients.firstname,
patients.middleinit, allergies.allergiesid, allergies.drug,
allergies.reaction, allergies.comment" & _
" FROM public.patients JOIN public.allergies ON patients.acctmaj::text =
allergies.acctmaj::text AND patients.acctmin::text = allergies.acctmin::text
ORDER BY (patients.acctmaj::text || patients.acctmin::text)::character
varying, allergies.drug;"
rst1.Open strqry, cnn1
'rst1.Open "select * from public.vwpatientallergies", cnn1
Do Until rst1.EOF
Debug.Print rst1.Fields(3).Value, rst1.Fields(4).Value,
rst1.Fields(5).Value, rst1.Fields(7).Value, rst1.Fields(8).Value,
rst1.Fields(9).Value, vbNewLine
rst1.MoveNext
Loop
End Sub