SQL vs Query

Topic

New Reply

I am writing a number of append queries to import new data into my tables, but I am wondering if this is the most efficient way to do so. I have looked at these queries’ SQL views, and the code required would be short enough to use in a RunSQL macro command. These queries, once all is in place, would not be run separately, as the entire update process would be macro-controlled.

Would it be more efficient to run this operation using OpenQuery or RunSQL macro commands, or is there any real difference.

Reply | Quote

Replies

No, it wouldn’t be better to use RunSQL. Saved queries are optimized in Access 2000, while the select statement itself is not. However, there’s nothing wrong with using the OpenQuery action in a macro to run a series of append queries. I often do this when I’m prototyping an application before I build the code itself.

Reply | Quote

Would it make any difference if the database is a base .mdb file or if it has been converted to a .mde file?

Reply | Quote

RunSQL is the slowest available method, and I would never recommend using macros in an MDE. You don’t convert a database to an MDE unless it is thoroughly error trapped, and of course, you can’t error trap macros, only code. In code, you’re better off using the Execute method to run a query than using RunSQL.

Reply | Quote