Using VBA to amend queries

thedawn

Registered User.
Local time
Today, 21:38
Joined
Mar 29, 2010
Messages
30
Hi hopefully someone will be able to help me

I am using VBA and querydefs to amend some queries that I have created.

I have a table in which I have a list of names that I want to use for fields in these queries and i am opening this recordset and trying to get the code to scroll through and amend the queries - I am hoping I am fairly close but need a final push

below is the code

Code:
Private Sub AmendOPQueries_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim oQuery As QueryDef

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("TableofTrackSafetyNO", dbOpenDynaset)
   
   
   Do Until rs.EOF
   Set oQuery = db.QueryDefs(rs!competency)
   oQuery.SQL = ("SELECT [" & rs!competency & "].[NI Number], IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],'No','Yes') AS Operational, [" & rs!competency & "].[" & rs!competency & "], CStr([" & rs!competency & "]![" & rs!competency & "]) AS TextExpiryDate, Left([TextExpiryDate],6) & CStr(CInt(Right([TextExpiryDate],4))-1) AS ActivityDate, " & rs!competency & " AS MyCourseCode FROM [" & rs!competency & "] LEFT JOIN NonOperational ON [" & rs!competency & "].[NI Number] = NonOperational.NINUMBER WHERE (((IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],'No','Yes'))='No'));")
   
   rs.MoveNext
   Loop
   
   rs.Close
   Set db = Nothing
End Sub

I am getting a syntax error around the oQuery line and i am going cross eyed working out where

Thanks

Thedawn
 
Hi pr2-eugin

I am struggling to see the SQL it generates as when i select debug it is all highlighted

What would be the best way - I use vba quite a bit but I do seem to struggle when I am getting it to run SQL and need to use loads of "" all over the place

Cheers

thedawn
 
thedawn, try the following.. use the Immediate window (Ctrl+G) to see the generated SQL when it hits the error..
Code:
Private Sub AmendOPQueries_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    [COLOR=Red][B]Dim [/B][/COLOR][COLOR=Red][B]tmpSQL[/B][/COLOR][COLOR=Red][B] As String[/B][/COLOR]
    Dim oQuery As QueryDef

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("TableofTrackSafetyNO", dbOpenDynaset)

    Do Until rs.EOF
        Set oQuery = db.QueryDefs(rs!competency)
        [COLOR=Red][B]tmpSQL = [/B][/COLOR]"SELECT [" & rs!competency & "].[NI Number], IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],'No','Yes') AS Operational, [" & rs!competency & "].[" & rs!competency & "], CStr([" & rs!competency & "]![" & rs!competency & "]) AS TextExpiryDate, Left([TextExpiryDate],6) & CStr(CInt(Right([TextExpiryDate],4))-1) AS ActivityDate, " & rs!competency & " AS MyCourseCode FROM [" & rs!competency & "] LEFT JOIN NonOperational ON [" & rs!competency & "].[NI Number] = NonOperational.NINUMBER WHERE (((IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],'No','Yes'))='No'));"
        
        [COLOR=Red][B]Debug.Print tmpSQL
        oQuery.SQL = tmpSQL[/B][/COLOR]

        rs.MoveNext
    Loop
    rs.Close
    Set db = Nothing
End Sub
 
Last edited:
Paul

Many thanks for that - I am currently debugging and how easy is that to see

Every day is a school day :)

Rich
 
Code:
CurrentDb.QueryDefs("YourQueryName").SQL = strSQL

I have used this to change queries. The query must exist first, no matter what it contains it will be changed to the string that strSQL represents.
 
Paul

I have debugged and think i know where the problem is but still can't get the code to work. I have highlighted in red where i think the problem is:

The SQL I am copying is:
Code:
SELECT [PTS AC].[NI Number], IIf([PTS AC]![NI Number]=[NonOperational]![NINUMBER],"No","Yes") AS Operational, [PTS AC].[PTS AC], CStr([PTS AC]![PTS AC]) AS TextExpiryDate, Left([TextExpiryDate],6) & CStr(CInt(Right([TextExpiryDate],4))-1) AS ActivityDate, [COLOR=Red]"PTS AC"[/COLOR] AS MyCourseCode FROM [PTS AC] LEFT JOIN NonOperational ON [PTS AC].[NI Number] = NonOperational.NINUMBER WHERE (((IIf([PTS AC]![NI Number]=[NonOperational]![NINUMBER],"No","Yes"))="No"));
When I copy it and amend it I get the following :

Code:
SELECT [NOCOSS]![NI Number], IIf([NOCOSS]![NI Number]=[NonOperational]![NINUMBER],"No","Yes") AS Operational, [NOCOSS]![NOCOSS], CStr([NOCOSS]![NOCOSS]) AS TextExpiryDate, Left([TextExpiryDate],6) & CStr(CInt(Right([TextExpiryDate],4))-1) AS ActivityDate, [COLOR=Red]NOCOSS[/COLOR] AS MyCourseCode FROM [NOCOSS] LEFT JOIN NonOperational ON [NOCOSS]![NI Number] = [NonOperational]![NINUMBER] WHERE (((IIf([NOCOSS]![NI Number]=[NonOperational]![NINUMBER],"No","Yes"))="No"));

The error I am getting now is Join Expression not Supported. I have tried changing the below code to get Quotes around the NOCOSS but keep getting syntax errors any ideas what I am missing ?

Code I am using is here:

Code:
Private Sub AmendNOQueries_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tmpSQL As String
Dim oQuery As QueryDef

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("TableofTrackSafetyNO", dbOpenDynaset)
   
   
   Do Until rs.EOF
   Set oQuery = db.QueryDefs(rs!competency)
   tmpSQL = "SELECT [" & rs!competency & "]![NI Number], IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],""No"",""Yes"") AS Operational, [" & rs!competency & "]![" & rs!competency & "], CStr([" & rs!competency & "]![" & rs!competency & "]) AS TextExpiryDate, Left([TextExpiryDate],6) & CStr(CInt(Right([TextExpiryDate],4))-1) AS ActivityDate, " & rs!competency & " AS MyCourseCode FROM [" & rs!competency & "] LEFT JOIN NonOperational ON [" & rs!competency & "]![NI Number] = [NonOperational]![NINUMBER] WHERE (((IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],""No"",""Yes""))=""No""));"
   
   Debug.Print tmpSQL
   oQuery.SQL = tmpSQL
   
   rs.MoveNext
   Loop
   
   rs.Close
   Set db = Nothing
End Sub
Thanks

Rich
 
Rich and Paul.

I am a little lost as to what you are attempting to do.

Do you want to change the Fields in the Table?

Or are you trying to change a Query?

Perhaps something completely different. I did originally think that you were trying to change a Query but the code you have used does not give me that indication.

Rich,

In order to make your code more legible I suggest that in your SQL statement you should use the continuation character _
this will allow you to use more lines. This way we can see the whole statement in one view without scrolling to the right.
 
Rainlover

I am changing queries but i am using a table of values to change those queries

I have copied a query using the same table so have created say 20 queries and then I want to amend thos queries using the same table

Cheers

Rich
 
Not very clear as to your approach.

It appears to me that you create a RecordSet. Then you run through a Loop. In that Loop you give the Variable tmpSQL a value which is a string. Then you go to the next record and change tmpSQL to something different. You keep going until you reach the end.

Finally you close everything down with the End Sub and all of your Variables lose their value.

Nothing happens.

So now instead of me being a Little lost I am totally lost as to your method.

I do have one question. If I understand you correctly, you can open your Database window and see 20 new Queries that are exactly the same as each other and the same as the original. These are the Queries you want to change.
 
Rain

If you look at Paul's post from above I have added the extra lines in regarding tmpSQL so that I could debug the line as it posts directly to the immediate window

If the SQL worked it would amend each querydef in order and save them.

I have done exactly the same for another set of queries and using this method it amends 209 queries so it is just the SQL that I need to get right and the problem seems to be that in the original Query the highlighted text has Quotation Marks round it but in the created SQL it doesnt

Cheers

Rich
 
I got it now.

The code I posted in post # 6 does the same as your

" oQuery.SQL = tmpSQL"

Thanks for helping me with that.

I will look at your SQL to see if I can find the problem.

It would be a lot easier if you made a special copy of your Database that has the code and tables in it. Post it here in Access 2003 and we could give it a proper test.
 
Woah, how did I miss this thread? Well seems like a lot has been going on here.. Well now that Rain is cleared of his doubt.. I think we can benefit from his knowledge too.. :)

Rich, as the Error has been thrown there is a Problem with your JOIN.. I agree, as you are performing a LEFT JOIN, only the [NI Number] form the [PTS AC] that are Equal will be pulled Along with Non Equal (which would be Null).. So the WHERE can be disregarded, by simply using INNER JOIN.. Does that make sense?
 
Rain

I do think the problem in the SQL is to get the NOCOSS with quotes round it

If you look at the SQL for the original Query and then the one that is generated

the original has "PTS AC" and the generated one is NOCOSS without quotes and I can't seem to get the quotes around the NOCOSS or the wrong ones so the course name doesnt come up correctly

Rich
 
Paul

Thanks for that but you do overstate.

Paul

If you saw a query that drew information from a Table or two then that would be normal.

If you saw 100 queries or more that drew information from the same Table or two then that would raise questions, would it not.

The question that has not yet been asked is "Why"
 
If you saw 100 queries or more that drew information from the same Table or two then that would raise questions, would it not.
I happened to think the same when the OP stated 209 Queries.. :eek:

It also puzzles me why the real need for changing majority of the saved Queries at run time..

Thanks for that but you do overstate.
I disagree. I don't think that is an overstatement Rain, you have helped me to get on my foot so many times.. So has many valuable members.. After all I am a still newbie to Access.. :)
 
Rain

If you look at the SQL for the original Query and then the one that is generated
Rich

Rich

The problem is that because your code is on one line it is difficult to decipher.

Can you display both before and after so that everything fits in this window.
 
Rain

I am importing a spreadsheet that has dates in around 200 columns I need to create initial queries from this and then i then need to create queries from those queries into a specific format

It is to make data conversion easier and automatic

Cheers

Rich
 
Rich

Thanks for that.

I will leave it to Paul to help with the SQL for the moment.

It is good night time for me.
 
Rain

I import a spreadsheet and then use a query to populate my main table using the column names of that spreadsheet.

I then copy a template query however many times dependant on the number of columns that are imported using the following code:

Code:
Private Sub CopyQueries_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strqryname As String


   Set db = CurrentDb()
   Set rs = db.OpenRecordset("TableofCompetencies", dbOpenDynaset)

   Do Until rs.EOF
      DoCmd.CopyObject , rs!competency, acQuery, "zzzBLANK"
      rs.MoveNext
      
   Loop
   
     

   rs.Close: Set rs = Nothing
   Set db = Nothing
End Sub

I then amend these queries using the same data table and the following code:

Code:
Private Sub AmendQueries_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim oQuery As QueryDef

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("TableofCompetencies", dbOpenDynaset)
   
   
   Do Until rs.EOF
   Set oQuery = db.QueryDefs(rs!competency)
   oQuery.SQL = ("SELECT NCCACompetency.[NI Number], NCCACompetency.[" & rs!competency & "] FROM NCCACompetency" & " WHERE NCCACompetency.[" & rs!competency & "] Is Not Null;")
   rs.MoveNext
   Loop
   
   rs.Close
   Set db = Nothing
   
End Sub

I then continue copying the next set of queries from a template and it is these I am now attempting to amend

Hope that is a bit more explanation
 

Users who are viewing this thread

Back
Top Bottom