MS Access 2000 and ODBC 
Author Message
 MS Access 2000 and ODBC

Hi:

I'm working on a 3.0 compliant propriatary ODBC Driver.
We use MS Access to load data into our system through our
ODBC interface. I have a couple of questions that have
come up as I am working to optimize performance.

1) Why does MS Access use the following call sequence for
every row of data that gets loaded?
              SQLPrepare()
              SQLExecDirect()
              SQLFreeStmt()

This is extremely ineffecient. The SQLFreeStmt() was
causing our internal "state of the world" to get cleaned
up. It was then getting re-initialized in SQLPrepare().

The call sequence I would expect to see (and have seen when
we used other tools to load our data) looks like this:
                 SQLPrepare()
                 SQLExecute()
                 SQLFreeStmt()

SQLPrepare() and SQLFreeStmt() gets called once, while
SQLExecute() gets called for each row being inserted.
I was able to programatically "hack" an optimization by
caching some state information.

2) Why does MS Access behave differently on Windows 2000
Server? Instead of the call sequence metioned above in 1),
I see the following results:
              SQLPrepare()
              SQLExecDirect()
              SQLFreeHandle()

I could again program around this, but I'm wondering if
there are some settings in the registry that have an
effect on what gets called.

Thanks,
johnb



Mon, 19 Jul 2004 05:15:40 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. linking Pervasive.SQL 2000 and MS Access 2000 via ODBC - problem

2. replication between ms access 2000 and ms sql sever 2000

3. MS ACCESS AND SQL SERVER 2000 ODBC TIMEOUTS

4. Ms Access 2000 ODBC connection to MSSQL server - need long text field creation

5. SP3 Errors Connecting with ODBC Linking with MS Access 2000

6. MS Access 97, SQL Server 2000, ODBC, Transaction

7. JDBC-ODBC to MS Access 2000

8. Urgent ! MS Access 2000 ODBC Driver

9. Set up ODBC on Windows 2000 for Connecting Java 2 with MS Access 97

10. P.SQL2000 ODBC and MS Access from Office 2000

11. ODBC connection from MS Access to SQL Server 2000

12. strange ODBC problem with MS SQL 2000 Enterprise Edition, MS SQL 7.0


 
Powered by phpBB® Forum Software