Dave:
Bill,
You would benefit more from posting your questions to the pgsql-odbc@postgresql.org mailling list - there are lots of people there that may be able to help you more than me.
I am having difficulty with VB caching too many records from a PostgresQL database when I open a recordset. I am using serverside cursors. When I establish the connection and open the recordset using
datRS.Open "In_Data", cnPressData, adOpenStatic, adCmdTable, adUseServer
If I remember correctly, server side cursors are not yet implemented in the ODBC driver, so you will actually be running client side.
the command immediately retrieves over 400MB of data from the database. This uses up all available physical and virtual memory and ultimately times out. I have tried MaxRecords, CacheSize, at low settings but neither command affects how many records are initially brought over. Ultimately my program will fail as the size of the database grows.
Please note that I am using this software only to write to the database. It does not re-read any of the data, it merely gathers it and forwards it to the database. I am using ODBC driver ver. no. psqlODBC-07-01-0008
If you are only inserting data, then why not try something like:
datRS.Open "SELECT * FROM " & Chr(34) & "In_Data" & Chr(34) & " WHERE 1 = 2", cnPressData, adOpenStatic, adCmdText, adUseServer
That will open a recordset that will always be empty so you can just insert data (the Chr(34)'s are required to quote your mixed case tablename). Alternatively, if you don't actually need the recordset object for binding or whatever, you could just throw INSERTs at the database:
cnPressData.Execute "INSERT INTO " & Chr(34) & "In_Data" & Chr(34) & " (col1, col2, col3) VALUES (val1, val2, val3)"
Regards, Dave.
