locked
SQL in VBA Access 2010 RRS feed

  • Question

  • I've been racking my brains over this for the better part of today.  For the live of me I cannot get to the bottom of this.

    I have a sub called Export.  For the file naming, I must pull information from tables.  The code below is a small part of the function, but it continues to return a blank query.

    Sub Export(tableexportname As String)

    MsgBox tableexportname
    SQLlowgrade = "SELECT [" & tableexportname & "].* INTO [templow] FROM [" & tableexportname & "];"
    MsgBox SQLlowgrade
    SQLhighgrade = "SELECT [" & tableexportname & "].* INTO [temphigh] FROM [" & tableexportname & "];"
    MsgBox SQLhighgrade

    CurrentDb.Execute SQLlowgrade
    CurrentDb.Execute SQLhighgrade

    End Sub

    Call Export ("Running_Import_LA")

    MsgBox tableexportname gives me, exactly, Running_Import_LA so the variable is passing into the sub.

    MsgBox SQLlowgrade gives me, exactly, SELECT [Running_Import_LA.* INTO [templow] FROM [Running_Import_LA];

    This is a good query as I have tested it in the Query design and it does indeed return the full table [Running_Import_LA] into a newly created table [templow].

    The same goes for MsgBox SQLhighgrade.

    When I run the vba code, the query in CurrentDB.Execute returns an empty table for [templow].  I'm so stuck at this point.

    Can someone please help?

    Wednesday, March 6, 2013 9:28 PM

Answers

  • Dirk, I finally figured it out.  When I was troubleshooting, I omitted the wrong call statements based on the data that I was working with.  So basically I was running a query on an empty table.  Part of the business I suppose.  Thank you so much.  I finished this morning and version 1.0.0 is packaged:)  
    • Marked as answer by Zach Lau Thursday, March 7, 2013 9:02 PM
    Thursday, March 7, 2013 9:01 PM

All replies

  • I've been racking my brains over this for the better part of today.  For the live of me I cannot get to the bottom of this.

    I have a sub called Export.  For the file naming, I must pull information from tables.  The code below is a small part of the function, but it continues to return a blank query.

    Sub Export(tableexportname As String)

    MsgBox tableexportname
    SQLlowgrade = "SELECT [" & tableexportname & "].* INTO [templow] FROM [" & tableexportname & "];"
    MsgBox SQLlowgrade
    SQLhighgrade = "SELECT [" & tableexportname & "].* INTO [temphigh] FROM [" & tableexportname & "];"
    MsgBox SQLhighgrade

    CurrentDb.Execute SQLlowgrade
    CurrentDb.Execute SQLhighgrade

    End Sub

    Call Export ("Running_Import_LA")

    MsgBox tableexportname gives me, exactly, Running_Import_LA so the variable is passing into the sub.

    MsgBox SQLlowgrade gives me, exactly, SELECT [Running_Import_LA.* INTO [templow] FROM [Running_Import_LA];

    This is a good query as I have tested it in the Query design and it does indeed return the full table [Running_Import_LA] into a newly created table [templow].

    The same goes for MsgBox SQLhighgrade.

    When I run the vba code, the query in CurrentDB.Execute returns an empty table for [templow].  I'm so stuck at this point.

    Can someone please help?

    You can help yourself by getting Access to tell you if an error occurred in executing the SQL, and the nature of the error.  Change your calls to .Execute so that they apply the dbFailOnError option:

        CurrentDb.Execute SQLlowgrade, dbFailOnError
        CurrentDb.Execute SQLhighgrade, dbFailOnError

    When you run the code with those changes, is an error raised?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 6, 2013 9:50 PM
  • Dirk, I adjusted my code above.  There was no failure.  The tables templow and temphigh both just return empty tables.  This is good query.  Not sure what is happening.
    Wednesday, March 6, 2013 10:04 PM
  • Dirk, I adjusted my code above.  There was no failure.  The tables templow and temphigh both just return empty tables.  This is good query.  Not sure what is happening.

    Just to be double sure, please try this:

        With CurrentDb
            .Execute SQLlowgrade, dbFailOnError
            MsgBox "Low records inserted: " & .RecordsAffected
            .Execute SQLhighgrade, dbFailOnError
            MsgBox "High records inserted: " & .RecordsAffected
        End With

    Note that the target tables must not exist, since you are executing make-table queries.  Are you dropping the tables before running the code?  Still, if you weren't, I'd expect an error to be raised.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 6, 2013 10:11 PM
  • Dirk, my apologies for the delay.  I got caught up in something.  I do always delete the temp tables.  I ran the code above.  It ran with no errors.  The message box that came up said that no records were inserted.
    Wednesday, March 6, 2013 10:59 PM
  • Zach,

    Sometimes Access like currentDB.execute does not work with certain sql syntax and can fail.

    How about DoCmd.RunSQL?

    Maybe that will work.

    Thursday, March 7, 2013 5:48 AM
  • Dirk, my apologies for the delay.  I got caught up in something.  I do always delete the temp tables.  I ran the code above.  It ran with no errors.  The message box that came up said that no records were inserted.

    That is very peculiar indeed.  It sounds like something is preventing the records from being added to the tables, or preventing the tables from being created, but I can't think what that might be.  What version of Access is this?

    If you create a new Access query, paste the SQL from SQLlowgrade or SQLhighgrade into its SQL View, and then have Access run it -- with messages turned on -- what happens?  Is the table created and the records addes, or does Access give you an error message?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, March 7, 2013 3:02 PM
  • Sometimes Access like currentDB.execute does not work with certain sql syntax and can fail.

    How about DoCmd.RunSQL?

    Maybe that will work.

    I'm not aware of any SQL syntax that is supported by RunSQL and not by CurrentDb.Execute.  The DAO method doesn't resolve parameters (including form/control references), which RunSQL does, but for the rest of it, I think the syntax itself is the same.  Are there syntax differences that I'm not aware of?

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, March 7, 2013 3:05 PM
  • Dirk, I finally figured it out.  When I was troubleshooting, I omitted the wrong call statements based on the data that I was working with.  So basically I was running a query on an empty table.  Part of the business I suppose.  Thank you so much.  I finished this morning and version 1.0.0 is packaged:)  
    • Marked as answer by Zach Lau Thursday, March 7, 2013 9:02 PM
    Thursday, March 7, 2013 9:01 PM
  • Dirk, I finally figured it out.  When I was troubleshooting, I omitted the wrong call statements based on the data that I was working with.  So basically I was running a query on an empty table.  

    <Sigh!>

    <g>


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, March 7, 2013 9:22 PM