Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? whichdll ?? - Mailing list pgsql-odbc

"Interesting" example.
1. Using PostgreSQL is basically a client-server relationship,..so adOpenDynamic (updated cursors) is a
bad choice. I recommend adOpenForwardOnly for fast one-way reading, and adOpenStatic to pull a client side
recordset so that you can move forward and back. In either case declare the recordset Cursor to be client
side (rs.CursorLocation = adUseClient).

This also means no rs.AddNew and no rs.Refresh.
Outside of MS Jet, I think of them as abominations.

I have come to think of the connection between my app and the database as a potentially slow narrow pipe
across a vast distance. Updateable cursors hold open the connection while I cursor up and down, always
tying up more database side resources and network resources. I always send commands, and pull back
recordsets. The only exception that makes sense is the browsing of mammoth datasets (millions of rows).
How practical is your application if you're visually browsing millions of rows.

2. ADODB library version 2.1 is mildly ancient. I'm use to seeing 2.5 on older installations, and lately
2.6,2.7 and maybe a 2.8. At any rate, the latest is always a free download from the MSDN MDAC area
(Microsoft Data Access Components).

3. Again this is a personal preference, but I always use connection.Execute for INSERTS,UPDATE, and
DELETES. Your example used on AddNew and one cnn.Execute.

4. I find a object referencing style and stick with it. rs.Fields("fieldname") and rs!fieldname are both a
little dated. rs("fieldname"), or rs(variable_holding_field_name) or rs(integer_field_ndx) seem to be
cultural standards now.

5. I loathe to admit it, ADO.NET is also a much stronger tool for web/client apps for things like
databinding. With it data grids are bound to disconnected recordsets, which execute discrete INSERTS,
UPDATES, AND DELETE underneath,...again avoiding the updateable cursor.

Oh, and PostgreSQL via ODBC,...
it has never been strong on the updateable cursor/dynamic cursor thing.



linuxgps wrote:
> Hi all,
>
> I'm a beginner to MS Windows.
> I began to to test VBA 6.0(SP6) with Postgresql 8.1 -with odbc- on
> WIN2K(SP4).
> For the test (accessing postgres db thru visual basic) I use the code,
> given by the distro (psqlODBC HOWTO- Visual Basic).
> The first problem I've encounted that the code in the given test snippet
> is not full functioning:
> "rs.Refresh"  doesn't implemented ?
> I use in the "Project Preferences" "Microsoft ActiveXData Objects 2.1
> library (msado21.tlb)
>
> I've installed some other MS stuff on the machine, and now the Postgres
> database call makes a memory exception and crashes.
>
> Q 1.:  Is this general, or some libraries are wrong on my system ?
> Q 2.: Which MS tools are to control/update the msado-libraries
> Q.3.: Which msado or similars are necessary to connect with visual basic
> to postgres ?
>
> thank  you in advance
> testuser vba
>
> -----------------
>
> 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>"
>
>  '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 FROM vbtest", cn,
> adOpenDynamic
>
>  '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.Refresh
>
>  '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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match