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

For ADO.NET I am using  Npgsql, which you can get from the affiliated project site -- the Npgsql project.
It is just like using the .NET SQL server, and Oledb libraries from the MSDN. The project site has
documentation with examples on how to use Npgsql. I thinks there stuff in the GBorg and the pgfoundry site.

I works for me.
(except for Parameterized stored procedures using an actual Parameters collection,...maybe it's just me.)



linuxgps wrote:
>
> 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
>>
>>
>
>
> ---------------------------(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