Make Table Query via VBA

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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.

Code:
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,911
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,911
Office Version
  1. 365
Platform
  1. Windows
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
Code:
Application.DisplayAlerts=False
is
Code:
DoCmd.SetWarnings False
That will suppress the confirmation pop-ups, which you may experience in running Action Queries in VBA code.
 

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,911
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,911
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2009
Messages
1,178
do you have indexes on your group by columns ?

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

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,326
Messages
5,595,519
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top