Re: VBA 6 crashes with WIN2K, Postgres 8.1, why ? whichdll??

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