Help needed with VBA SQL conventions

[edsac64]

Hi, I could really use some help here. I'm trying to tighten up a DB that has lots of queries that are fired in VBA by using some like this:

strQuery = "the query to be run"
DoCmd.OpenQuery strQuery

I might be wrong, but this seems rather inefficient and I'd like to use more SQL statements in VBA. It's easy enough to copy the SQL from the saved query, assign it to a string variable and run it. The obstacle I have is the SQL code is often very, very long and I'd like to use line breaks and table aliases to compress the code and neaten it up.

I've attached a text file to show the full extended version of the code.

Any help or suggestion would sincerely be appreciated.

Thanks,

Ed

 

[boblarson]

First off, using SAVED QUERIES is more EFFICIENT than doing them in VBA. The reason? Because Access can determine an execution plan for them and, unless the query changes, it doesn't have to do it again. For queries run from SQL Statements in VBA, it has to do this every time it is run.

As for this statement, one way to break it up for code would be:

Dim strSQL As String
strSQL = "INSERT INTO tblVeryLongTableName ( [Status], Line, [Area], [Division], [Dept], [Unit], [Manager #], " & _
         "[Manager Name], [Customer], [Customer Name], Acct, [Acct Id], [Acct Name], [Acct Type], Cycle, [Frequency], " & _
         "[Month], [Accrual], [Last Trans -1], [Last Trans -2], [Last Trans -3], [Date Modified], [Date Added] ) " & _
         "SELECT tblAnotherVeryLongTableName.[Status], tblAnotherVeryLongTableName.LINE, tblAnotherVeryLongTableName.[Area], " & _
         "tblAnotherVeryLongTableName.[Division], tblAnotherVeryLongTableName.[Dept], tblAnotherVeryLongTableName.[Unit], " & _
         "tblAnotherVeryLongTableName.[Manager #], tblAnotherVeryLongTableName.[Manager Name], tblAnotherVeryLongTableName.[Customer Id], " & _
         "tblAnotherVeryLongTableName.[Customer Name], tblAnotherVeryLongTableName.Acct, tblAnotherVeryLongTableName.[Acct Id], " & _
         "tblAnotherVeryLongTableName.[Acct Name], tblAnotherVeryLongTableName.[Acct Type], tblAnotherVeryLongTableName.Cycle, " & _
         "tblAnotherVeryLongTableName.[Frequency], tblAnotherVeryLongTableName.[Month], tblAnotherVeryLongTableName.[Accrual], " & _
         "tblAnotherVeryLongTableName.[Last Trans -1], tblAnotherVeryLongTableName.[Last Trans -2], tblAnotherVeryLongTableName.[Last Trans -3], " & _
         "tblAnotherVeryLongTableName.[Date Modified], Now() AS [Date Added] " & _
         "FROM tblAnotherVeryLongTableName;"

 

[edsac64]

A million thanks Bob! I see where my misguided intentions would have resulted in me reinventing the wheel! We will leave it as is and move forward, only using the SQL statements in VBA when needed for rarely used events.