
HELP: Data Access Objects w/ ODBC connected to Oracle Database
I am writing an application in VB4-ProEdition-16bit that will access and
Oracle DBMS (running on a Sun) using ODBC and the VB Data Access Objects. I
am new to programming with DAO's and have come across a problem that I cannot
find a solution to.
Speed is a major consideration in this project. Using SQL Passthrough, I can
connect to the ODBC data source and retrieve a reasonable amount of records
(read only) with no problems.
I am using a snapshot dynaset to this. On our LAN (relatively traffic free),
it takes about 12 seconds to initially connect, and about .5-1 seconds to
perform the SQL query to retrieve records.
However, since the recordset returned is read only, I am unable to add/update
records in the table. It appears, that to do this I must do one of two
things.
1) Not use SQL PassThrough. This causes the Jet engine to do the Queries on
the database. I end up with an access time of 17-20 seconds!!!
2) Use the QueryDef Object. I would love to use this method, but I can't seem
to get it to work. It always returns an error code saying Feature Not
Available.
Any help/suggestions on this would be appreciated.
Thank you very much,
Matthew Appler