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

Well Greg, you're  right.
Just the used code snippet is about 4 years old and was shipped with the
distro.
For beginners I think even a slow code  is much better as  having nothing.
Have you any tutorial code using Postgres thru VBA with ADO.NET ?

Campbell, Greg wrote:

> "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
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>