error with access 2003 - column "ctid" does not exist
От | David P. Lurie |
---|---|
Тема | error with access 2003 - column "ctid" does not exist |
Дата | |
Msg-id | cdfd3n$qrb$1@sea.gmane.org обсуждение исходный текст |
Ответы |
Re: error with access 2003 - column "ctid" does not exist
|
Список | pgsql-odbc |
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
В списке pgsql-odbc по дате отправления: