Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Query problem (MS ACCESS 2003)

    If only there was an easy way to convert a query grid SQL to a VB query.

    The following errors on the line - FORMS!MAINFORM1!SUBFORM1.FORM.AthleteList.Column(0 ))); "

    strSQL = "DELETE JUNCTION.ID2, JUNCTION.AthleteID "
    strSQL = strSQL & "FROM JUNCTION "
    strSQL = strSQL & "WHERE (((JUNCTION.ID2)=[FORMS]![MAINFORM1]![SUBFORM1].[FORM].[ID2]) "
    strSQL = strSQL & "AND ((JUNCTION.AthleteID)=FORMS!MAINFORM1!SUBFORM1.FOR M.AthleteList.Column(0))); "

    Any suggestions, thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Query problem (MS ACCESS 2003)

    It depends on how you want to use the SQL. Does this work?
    <code>
    strSQL = "DELETE ID2, AthleteID " & _
    "FROM JUNCTION " & _
    "WHERE ID2=" & Forms!MAINFORM1!SUBFORM1.Form.ID2 & _
    " AND JUNCTION.AthleteID=" & Forms!MAINFORM1!SUBFORM1.Form.AthleteList.Column(0 )</code>

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Query problem (MS ACCESS 2003)

    Thankyou Hans, it certainly worked. I will now try and see where I was going wrong. I know it's lazy using the grid first in a query, it's quick, but then I spend hours trying to convert it to VB. It would be a dream to find an extra button in the query SQL marked convert to VB. But that would make people like me look too clever. There are many SQL varients ie MySql etc, where does one start. But thanks again for saving my sanity.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Query problem (MS ACCESS 2003)

    The problem is that queries within the Access interface have extra features that are not part of standard SQL, for example the ability to refer to controls on forms. When running a query or SQL in DAO or ADO, it will fail because they don't understand the reference to controls. This would make it difficult to write a converter.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Query problem (MS ACCESS 2003)

    Thanks again Hans. That answers why my search on google for a converter comes up with nothing. It's a shame MS does not advise on workarounds. Just means I will never become as clever as you, maybe I should cut down on watching TV not that theres much to watch here in the UK. Best regards and thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Query problem (MS ACCESS 2003)

    > I will never become as clever as you

    Just keep on practicing... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Query problem (MS ACCESS 2003)

    ZOR

    Check out

    SQL to VBA Formatter Version: 2000 (9.0)

    Posted on 04/28/06 11:28 AM Attachment (291 downloads)
    Posted by dallr - Utter Access VIP
    Posts: 2399 - Loc: Trinidad & Tobago

    Forum: Access Code Archive

    http://www.utteraccess.com/forums/showflat...&Number=1165308

    If you perfect it, post a copy

    HTH, John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Query problem (MS ACCESS 2003)

    Note: you have to register as a member at Utter Access if you want to download attachments.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Query problem (MS ACCESS 2003)

    Thanks Hans,John. I downloaded the program and threw a pasted Access query grid SQL into the SQKJet window and it did not like it, kept jumping into the form code with errors. Maybe I was being unkind to it, but the user instructions say you can just cut/paste the SQL from the query grid, but as I say was not very happy. Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •