QueryDef Question

[funderburgh]

I'm new to DAO and walking through my manual. I want to use the execute method to run a query in VBA (the SQL is really big), but I can only find a description of how to create a QueryDef "from scratch". Can I create a QueryDef from a query which exists in the Access workspace?

Any help is welcome

 

[jdraw]

I'm new to DAO and walking through my manual. I want to use the execute method to run a query in VBA (the SQL is really big), but I can only find a description of how to create a QueryDef "from scratch". Can I create a QueryDef from a query which exists in the Access workspace?

Any help is welcome

Are you asking if you can use the SQL from an existing query, modify it, then execute?

You can certainly define a simple query, then change the SQL for that query and save it.

 

[raskew]

Hi -

Take a look at Allen Browne's solution at http://allenbrowne.com/ser-71.html. This allows you to copy/paste a query's SQL to the form, which then converts it to VBA.

HTH - Bob

 

[funderburgh]

I would use the SQL if I could, but the statement seems to exceed the size parameters in the VBA editor. Here is the statement: (one of three): I tried breaking it up into multiple strings, but I could not get that to work either. I would like to find a way to run the query from the access workspace in VBA.


INSERT INTO [Class Rank] ( StudentID, [First Name], [Last Name], [Grade Level], [Class Name], Expr1, Expr2, [First Quarter GPA], [Second Quarter GPA], [Third Quarter GPA], [Fourth Quarter GPA], [Reported Quarters], GPA )
SELECT Students.StudentID, Students.[First Name], Students.[Last Name], [Grade Level].[Grade Level], Classes.[Class Name], Left([Classes].[Class Name],4) AS Expr1, Right([Classes].[Class Name],3) AS Expr2, IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [First Quarter GPA], IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [Second Quarter GPA], IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [Third Quarter GPA], IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [Fourth Quarter GPA], IIf([Fourth Quarter GPA]>0,4,IIf([Third Quarter GPA]>0,3,IIf([Second Quarter GPA]>0,2,1))) AS [Reported Quarters], ([First Quarter GPA]+[Second Quarter GPA]+[Third Quarter GPA]+[Fourth Quarter GPA])/[Reported Quarters] AS GPA
FROM (Students INNER JOIN Classes ON Students.StudentID = Classes.ID) INNER JOIN [Grade Level] ON Students.StudentID = [Grade Level].ID
WHERE ((([Grade Level].[Grade Level])=12) AND ((Left([Classes].[Class Name],4))<>"Home") AND ((Right([Classes].[Class Name],3))="- M") AND (([Grade Level].[School Year])=[Forms]![Main Navigation]![Print Menu]![SchoolYear]) AND ((Classes.[School Year])=[Forms]![Main Navigation]![Print Menu]![SchoolYear] Or (Classes.[School Year])=Left([Forms]![Main Navigation]![Print Menu]![SchoolYear],4)-"1" & "-" & Right([Forms]![Main Navigation]![Print Menu]![SchoolYear],4)-"1" Or (Classes.[School Year])=Left([Forms]![Main Navigation]![Print Menu]![SchoolYear],4)-"2" & "-" & Right([Forms]![Main Navigation]![Print Menu]![SchoolYear],4)-"2" Or (Classes.[School Year])=Left([Forms]![Main Navigation]![Print Menu]![SchoolYear],4)-"3" & "-" & Right([Forms]![Main Navigation]![Print Menu]![SchoolYear],4)-"3"))
ORDER BY Students.StudentID;

 

[jdraw]

I just created a query called "funderburgh" in a test database.
The query was simply "Select * from Customer" (where Customer is one of my tables.)
I then looked at currentdb.querydefs("funderburgh").sql

I then set currentdb.querydefs("funderburgh").sql = to your SQL
then, looked at the sql for "funderburgh"

?currentdb.querydefs("funderburgh").sql
INSERT INTO [Class Rank] ( StudentID, [First Name], [Last Name], [Grade Level], [Class Name], Expr1, Expr2, [First Quarter GPA], [Second Quarter GPA], [Third Quarter GPA], [Fourth Quarter GPA], [Reported Quarters], GPA )
SELECT Students.StudentID AS Expr1, Students.[First Name] AS Expr2, Students.[Last Name] AS Expr3, [Grade Level].[Grade Level] AS Expr4, Classes.[Class Name] AS Expr5, Left(Classes.[Class Name],4) AS Expr1, Right(Classes.[Class Name],3) AS Expr2, IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [First Quarter GPA], IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [Second Quarter GPA], IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [Third Quarter GPA], IIf([First Quarter Grade]<60,0,IIf([first quarter Grade]<70,0,IIf([First Quarter Grade]<80,1,IIf([First Quarter Grade]<90,2,3)))) AS [Fourth Quarter GPA], IIf([Fourth Quarter GPA]>0,4,IIf([Third Quarter GPA]>0,3,IIf([Second Quarter GPA]>0,2,1))) AS [Reported Quarters], ([First Quarter GPA]+[Second Quarter GPA]+[
Third Quarter GPA]+[Fourth Quarter GPA])/[Reported Quarters] AS GPA
FROM Students, Classes, [Grade Level]
WHERE ((([Grade Level].[Grade Level])=12) And ((Left(Classes.[Class Name],4))<>"Home") And ((Right(Classes.[Class Name],3))="- M") And (([Grade Level].[School Year])=Forms![Main Navigation]![Print Menu]!SchoolYear) And ((Classes.[School Year])=Forms![Main Navigation]![Print Menu]!SchoolYear Or (Classes.[School Year])=Left(Forms![Main Navigation]![Print Menu]!SchoolYear,4)-"1" & "-" & Right(Forms![Main Navigation]![Print Menu]!SchoolYear,4)-"1" Or (Classes.[School Year])=Left(Forms![Main Navigation]![Print Menu]!SchoolYear,4)-"2" & "-" & Right(Forms![Main Navigation]![Print Menu]!SchoolYear,4)-"2" Or (Classes.[School Year])=Left(Forms![Main Navigation]![Print Menu]!SchoolYear,4)-"3" & "-" & Right(Forms![Main Navigation]![Print Menu]!SchoolYear,4)-"3"))
ORDER BY Students.StudentID;

You could now run that query from vba using

docmd.openquery ("funderburgh")

Hope this is helpful.

You may be able to execute it using

currentdb.QueryDefs( "funderburgh").Execute dbfailOnError

 

[funderburgh]

Fantastic! How can I thank you for doing so much work on my behalf.

My process is working perfectly.

Thanks


John

 

[jdraw]

Fantastic! How can I thank you for doing so much work on my behalf.

My process is working perfectly.

Thanks


John

Glad to help.
No thanks necessary.