VBA To Create Query

[Jaye7]

I have the following info from my SQL view in access, I want to automate a button to create the query, can someone please help with the vba code.

SELECT CustomerT.CustomerID, CustomerT.FirstName, CustomerT.LastName, CustomerT.State
FROM CustomerT INNER JOIN ContactT ON CustomerT.CustomerID = ContactT.CustomerID;

 

[Galaxiom]

Why would you want to do this?

 

[Jaye7]

Because I want to create temporary queries when I press buttons, this is just an example to use and once someone provides the VBA then I will be able to modify it to suite my needs, I use vba in excel often but am not familiar with access vba.

 

[Galaxiom]

Easiest way is to create a saved query to use as a template. Then in code of your button:

Currentdb.QueryDefs("MyQuery").SQL = "SELECT blah"
DoCmd.OpenQuery "MyQuery"

 

[namliam]

Extending on what Galaxiom said... it is usually a good idea to keep SQL in code readable.
Something along the lines of...

Dim MySQL as string
mySQL = ""
mySQL = mySQL & " Select a "
mySQL = mySQL & "      , b "
mySQL = mySQL & " from thattable "

Currentdb.QueryDefs("MyQuery").SQL = mySQL

Or slightly faster but limitted in use, using the line continuations:

mySQL = " Select a " & _
        "      , b " & _
        " from thattable "

The line continuations is limited at a maximum of 20 lines in your code, so if you put to many lines you will get some error which means you have to chop the sql anyway and split it into seperate parts.

Notice in both examples the leading and trailing space in front and back of each line. This to prevent things like "bfrom" from happening.

 

[Jaye7]

Thanks for your help,

I will give the script a try, I know that I can store temp queries etc... but I like to use vba rather than to have a database with 100's of queries etc... that may only be used once in a while.