Troubleshoot Code-generated Queries (1 Viewer)

[mresann]

Just a small hint.

When a query is created in VBA code, the smallest errors or ommissions can cause much time to be lost in troubleshooting, particularly with syntax. My suggestion is to create a string variable to hold the SQL string so it can be tested independently of the actual process. Include a Debug.print statement directly after the SQL variable asignment to display the SQL string. During operation, the sql statement will print in the Debug or Immediate window (depending on Access version). You can usually find any syntax problems faster this way, whether it be VBA or SQL syntax.

Incidentally, It is good practice to create a string variable to hold a code generated string itself in the first place. Rather then


db.execute "SELECT Field1, Field 2 FROM Table1 WHERE Field1 = " & myData

Use

Dim strSQL As String

strSQL = "SELECT Field1, Field 2 FROM Table1 WHERE Field1 = " & myData

debug.print strSQL

db.execute strSQL

Now if your query generates an error, you can easily test the query in a query grid by copying the SQL statement from the Debug/Immediate winto into the SQL box of a new query.

 

[Pat Hartman]

Good advice but it is better practice to use parameter queries rather than generating SQL on the fly. Parameter queries result in less database bloat and faster execution. You should only create SQL strings when the SQL is actually dynamic. Parameters are not dynamic. They can be passed at runtime.

 

[mresann]

That's true, Pat. Of course parameter passed queries are preferable. I'm talking of the times when it is necessary to create dynamic queries, such as when it is necessary to query remote db's, even other types of db's such as Oracle and SQL, or when you need to migrate the function or module to VB6 or .NET. Most of my work involves remote access, but the same principles apply.

 

[Pat Hartman]

Even pass-through queries should be stored as querydefs. When you save a querydef, Jet does some processing even on pass-through queries and that minimizes what needs to be done when the queries are actually run. We're not talking seconds here though, we're talking milliseconds so I'm sure you wouldn't see any dramatic difference. The difference comes from volume. If you do something 1000 times in an hour, you need it to be more efficient than if you do it once.