asked on

Why MDE file performance is much slower than MDB file?

I have an MDB file that converted into an MDE file. With data the MDE file size is about 150MB. About 90% of the application is driven by VBA and SQL statements. How come it takes me about 10 minutes to process the data from the MDB file and it take about 10X as slow in the MDE file? Is there a way to speed it up?

Thanks,
BT

ASKER CERTIFIED SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

babytam

ASKER

No..it's always the same. I use VBA to write SQL statement and use Docmd.RunSQL to execute it. Could this be the reason?

Thanks,
Tam

stevbe

I am not sure that is causing your problem but if you can use saved queries and parameters you will get better performance.

babytam

ASKER

I think that must be it. I switched from Docmd.RunSQL sqlStatement to Currentdb.Execute sqlStatement and it ran way faster, as fast as the MDB now. What's the difference between Docmd.RunSql and Currentdb.Execute statments?

Thanks,
Tam

stevbe

I have never used Docmd.RunSQL but I *assume* there is lots of extra processing overhead.

if you want to run even faster you use DBEngine.Execute instead of CurrentDB. CurrentDB will force a refresh of all it's collections before executing whereas DBEngine just pops the SQL along. The obnly time you would need to be concerned is if you are creating tables and then you would either .Refresh the tableDefs collection or just use CurrentDB.

babytam

ASKER

Do I need any special Reference file to use DBEngine.Execute? I tried to switch to DBEngine.Execute and it gave me a compile error, Method or data member not found.

Thanks,
Tam

stevbe

You need the Microsoft DAO Object 3.6 Library

Steve

babytam

ASKER

I have that, but I also have ActiveX Data Objects 2.1 Library. If I put DAO Object 3.6 library before ActiveX Data Objects 2.1 Library then I will get error for ADO syntax, and if I put ActiveX Data Objects 2.1 Library before DAO Object 3.6 then can't use DBEngine.Execute statement. Do you have any recommendations?

Thanks,
Tam

babytam

ASKER

Actually, DBEngine.Execute is not a recognized method whether I put DAO Object 3.6 Library before or after ActiveX Data Objects 2.1 Library.

Leigh Purvis

(NOT for points - just popping in :-)

CurrentDb.Execute strSQL
Or
DBEngine(0)(0).Execute strSQL

:-)

babytam

ASKER

DbEngine(0)(0).Execute works.

Thanks,
Tam

stevbe

Nice pickup Leigh ... I was typing too fast.

To address the order in which object libraries are referenced causing stuff to break ... If you fully qualify your object references it does not matter what order they are listed in.

DAO.DBEngine(0)(0)
ADODB.Recordset

etc.

Steve