Make Table Query via VBA

[Mike Blackman]

Hi All,

I'm trying to code a Make Table Query and assumed what I was doing was correct but I get an error stating that "Query Input must contain one Table or Query", don't know what the problem is, the SQL is as per the Query in Access.

And help is greatly appreciated.

Sub RunQry()

Dim StrSql As String

StrSql = "SELECT IP_Flow_0_Tbl.SrcIp, Lokationen.[Location 1], IP_Flow_0_Tbl.DstIp, Lokationen.[Location 2], IP_Flow_0_Tbl.Application, Sum(IP_Flow_0_Tbl!BytSent) AS SumOfBytSent INTO Source_Locations_Tbl" _
    & "FROM Lokationen, IP_Flow_0_Tbl" _
    & "GROUP BY IP_Flow_0_Tbl.SrcIp, Lokationen.[Location 1], IP_Flow_0_Tbl.DstIp, Lokationen.[Location 2], IP_Flow_0_Tbl.Application" _
    & "HAVING (((Lokationen.[Location 1]) Is Not Null));"

With DoCmd
    .SetWarnings False
    .RunSQL StrSql
    .SetWarnings True
End With

End Sub

 

[Joe4]

It looks like you are breaking up the query into multiple lines, which is fine, but make sure that you leave a space at the beginning or end of each line (so that FROM, GROUP, and HAVING don' run into the previous lines).

A good trick that I often do is after I create my SQL statement, I return it back in a Message Box, i.e.

MsgBox StrSql

Then you can view it and compare it to the SQL code of the query if you built it manually to see if there are any differences/issues.

 

[Mike Blackman]

Hi Joe,

That makes a lot of sense and works perfectly. Are there any tricks to get better performance as there are in Excel such as ScreenUpdating, I read somewhere the Echo worked in the same manner but I didn't notice any benefit.

 

[Joe4]

There really isn't the same issue with ScreenUpdating in Access as there is in Excel (the programs don't quite work the same way).

However, one tip that may be useful is the Access equiavalent of Excel

Application.DisplayAlerts=False

is

DoCmd.SetWarnings False

That will suppress the confirmation pop-ups, which you may experience in running Action Queries in VBA code.

 

[Mike Blackman]

But no ways to improve the speed the code runs? Is there any benefit from running the code via VBA as aposed through Access other than people not being able to change the queries?

 

[Joe4]

The biggest factor in performance here will probably not be your VBA code, but more your Query and Database Design (your SQL code is doing most of the work here -- and as long as your the SQL code of your query is designed correctly, there isn't much of a way to speed up that piece of it).

If you have a normalized database, with proper indexes, and relationships, this will give you the most "bang for the buck". Also, be sure to regularly "Compact & Repair" you database to keep the size down and keep it running efficiently.

 

[Mike Blackman]

Thanks for your help Joe, Its such a simple database but so slow.

 

[Joe4]

The size of your data tables will have an impact too. Be sure to Compact & Repair the database regularly and use indexes (but not overuse them).

Here is a link of rules of database normalization. Usually, the main objective is to meet at least the first 3 rules:
http://datamodel.org/NormalizationRules.html

 

[james_lankford]

do you have indexes on your group by columns ?

if your table is small it shouldn't matter too much though

 

[Richard Schollar]

Hi

You realise you have a cross join situation there which could potentially be making an absolutely HUGE dataset depending on how many records are in the two source tables? You could also move the predicate (restriction in the Having clause) to the WHERE clause which would make the SQL more efficient.

You say it's a make table query, but at the moment it isn't - but I suspect you know that Mikey and are just testing at the moment?