We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Why MDE file performance is much slower than MDB file?

babytam
babytam asked
on
Medium Priority
507 Views
Last Modified: 2013-12-05
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
Comment
Watch Question

Commented:
Often times when you compile into an MDE Access also decompiles your queries so the fierst time the queries are run the optimizers needs to do an anaysis. Do you get better performance the second time you process with an MDE?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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

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

Author

Commented:
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

Commented:
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.

Author

Commented:
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

Commented:
You need the Microsoft DAO Object 3.6 Library

Steve

Author

Commented:
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

Author

Commented:
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 PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
(NOT for points - just popping in :-)

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

:-)

Author

Commented:
DbEngine(0)(0).Execute works.  

Thanks,
Tam

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.