SQL query works slower than the Access query wizard

Question

I am working on automation of elimination of unmatched rows in two tables in MS Access. As known, Access has a query wizard for this process which named Find unmatched records. This method works fast even for 500k data rows.

But when I execute a query in MS Access VBA it works so slowly. Is there a faster SQL implementation to eliminate data, or does MS Access use a different method? How can I make it fast?

Below is my query in VBA. Table1 and Table2 each have more than 100k rows.

strQuery = SELECT gsmno INTO newtablename FROM table1 WHERE gsmno NOT IN (SELECT gsmno FROM table2)
CurrentDb.Execute strQuery

NOT IN is often a bad idea in MS Access. Can you not do a JOIN on gsmno with a check for nulls? — Fionnuala
– Fionnuala, Commented Mar 13, 2017 at 18:20
BTW it is usually better to use an instance of CurrentDB, eg Set db = CurrentDB, this allows you to check records returned and so forth. — Fionnuala
– Fionnuala, Commented Mar 13, 2017 at 18:34
How to return unmactched data checking for nulls? Can you write the query you mean to help me please.? — Ali Tor
– Ali Tor, Commented Mar 13, 2017 at 18:46
have you tried insert into newtablename select gsmo from table1 t1 where not exists (select 1 from table2 t2 where t2.gsmo = t1.gsmo) — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Mar 13, 2017 at 20:13
Stored queries are generally faster and more efficient than VBA string queries as Access' query optimizer can save best plan with saved queries. But in VBA, the string query is run at once without optimizaiton. The wizard creates a stored query. Simply keep it and call it with DoCmd.OpenQuery. — Parfait
– Parfait, Commented Mar 14, 2017 at 1:54

abraxascarab · Accepted Answer · 2017-03-13 20:39:21Z

1

Use a LEFT OUTER JOIN and check for NULL on the Table2 gsmno. Those are your Non-Matches.

strQuery = & _
"SELECT " & _
"   t1.gsmno " & _
"INTO " & _ 
"   newtablename " & _ 
"FROM " & _ 
"   table1 as t1 " & _
"LEFT OUTER JOIN " & _ 
"   table2 as t2 on " & _
"      t1.gsmno = t2.gsmno " & _ 
"WHERE " & _ 
"   isnull(t2.gsmno) = true;"

CurrentDb.Execute strQuery

edited Mar 13, 2017 at 20:39

answered Mar 13, 2017 at 20:32

abraxascarab

7211 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query works slower than the Access query wizard

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related