lack of errormessage

Discussion in 'Microsoft Access External Data' started by mi, Aug 16, 2011.

  1. mi

    mi Guest

    I am running a SQLquestion in VB. This does not work and the
    errormessage I get is poor:
    "cannot execute a select query"

    SQLstr = "SELECT * FROM tblMag1;"
    CurrentDb.Execute SQLstr

    I know it's possible to get moore information than that but I have
    forgotten how.

    Can anyone please help me.

    Thanks in advance
    /
     
    mi, Aug 16, 2011
    #1
    1. Advertisements

  2. mi

    Tony Toews Guest

    On Tue, 16 Aug 2011 07:52:47 -0700 (PDT), mi
    <> wrote:

    >I am running a SQLquestion in VB. This does not work and the
    >errormessage I get is poor:
    >"cannot execute a select query"
    >
    >SQLstr = "SELECT * FROM tblMag1;"
    > CurrentDb.Execute SQLstr


    add ", dbfailonerrors" to the currentdb line.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Aug 18, 2011
    #2
    1. Advertisements

  3. Tony Toews <> wrote in
    news::

    > On Tue, 16 Aug 2011 07:52:47 -0700 (PDT), mi
    ><> wrote:
    >
    >>I am running a SQLquestion in VB. This does not work and the
    >>errormessage I get is poor:
    >>"cannot execute a select query"
    >>
    >>SQLstr = "SELECT * FROM tblMag1;"
    >> CurrentDb.Execute SQLstr

    >
    > add ", dbfailonerrors" to the currentdb line.


    SELECT statements cannot be .Executed.

    --
    David W. Fenton http://www.dfenton.com/
    contact via website only http://www.dfenton.com/DFA/
     
    David-W-Fenton, Aug 19, 2011
    #3
  4. mi

    Tony Toews Guest

    On Fri, 19 Aug 2011 22:17:07 +0000 (UTC), "David-W-Fenton"
    <> wrote:

    >>>I am running a SQLquestion in VB. This does not work and the
    >>>errormessage I get is poor:
    >>>"cannot execute a select query"
    >>>
    >>>SQLstr = "SELECT * FROM tblMag1;"
    >>> CurrentDb.Execute SQLstr

    >>
    >> add ", dbfailonerrors" to the currentdb line.

    >
    >SELECT statements cannot be .Executed.


    Duhhhh. You're correct.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Aug 20, 2011
    #4
  5. mi

    mi Guest

    On Aug 20, 12:17 am, "David-W-Fenton" <>
    wrote:
    > Tony Toews <> wrote innews::
    >
    > > On Tue, 16 Aug 2011 07:52:47 -0700 (PDT), mi
    > ><> wrote:

    >
    > >>I am running a SQLquestion in  VB. This does not work and the
    > >>errormessage I get is poor:
    > >>"cannot execute a select query"

    >
    > >>SQLstr = "SELECT * FROM tblMag1;"
    > >>     CurrentDb.Execute SQLstr

    >
    > > add ", dbfailonerrors" to the currentdb line.

    >
    > SELECT statements cannot be .Executed.
    >
    > --
    > David W. Fenton                  http://www.dfenton.com/
    > contact via website only    http://www.dfenton.com/DFA/



    I changed the selction to:
    SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1 ; "
    CurrentDb.Execute SQLstr,dbfailonerrors

    The result is the same as before: 'cannot execute the query'.
    Does dbfailonerrors needs a definition?

    /Michelle
     
    mi, Aug 21, 2011
    #5
  6. mi

    mi Guest

    On Aug 18, 3:27 am, Tony Toews <> wrote:
    > On Tue, 16 Aug 2011 07:52:47 -0700 (PDT), mi
    >
    > <> wrote:
    > >I am running a SQLquestion in  VB. This does not work and the
    > >errormessage I get is poor:
    > >"cannot execute a select query"

    >
    > >SQLstr = "SELECT * FROM tblMag1;"
    > >     CurrentDb.Execute SQLstr

    >
    > add ", dbfailonerrors" to the currentdb line.
    >
    > Tony
    > --
    > Tony Toews, Microsoft Access MVP
    > Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
    > Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
    > For a convenient utility to keep your users FEs and other files
    >   updated seehttp://www.autofeupdater.com/


    I try to explanation a little better what I'm doing.

    Public Sub importTmpTb()
    On Error GoTo Err_importTmpTb

    Dim bild As Integer
    Dim magasin As String
    Dim beskrivning As String

    I have tried select

    SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1
    WHERE ] tbMag1].[bild] = 3 ; "

    SQLstr = "SELECT [datum],[bild] FROM tblMag1 WHERE [bild] = 3; "

    as well as two differen execute

    CurrentDb.Execute SQLstr,dbfailonerrors
    CurrentDb.Execute SQLstr


    And nothing works.

    Can please anyone give me some idea what I'm doing wrong.
    Thanks in advance.

    /Michelle
     
    mi, Aug 21, 2011
    #6
  7. mi <> wrote in
    news:
    :

    > On Aug 20, 12:17 am, "David-W-Fenton"
    > <> wrote:
    >> Tony Toews <> wrote
    >> innews:ljqo4792gd7lrdnj4rglr31b

    > :
    >>
    >> > On Tue, 16 Aug 2011 07:52:47 -0700 (PDT), mi
    >> ><> wrote:

    >>
    >> >>I am running a SQLquestion in  VB. This does not work and the
    >> >>errormessage I get is poor:
    >> >>"cannot execute a select query"

    >>
    >> >>SQLstr = "SELECT * FROM tblMag1;"
    >> >>     CurrentDb.Execute SQLstr

    >>
    >> > add ", dbfailonerrors" to the currentdb line.

    >>
    >> SELECT statements cannot be .Executed.

    >
    > I changed the selction to:
    > SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1 ;
    > "
    > CurrentDb.Execute SQLstr,dbfailonerrors
    >
    > The result is the same as before: 'cannot execute the query'.
    > Does dbfailonerrors needs a definition?


    Only DML statements (i.e., what are referred to in Access as "action
    queries," i.e., queries that MODIFY data) can be EXECUTED.

    What are you trying to do with your SELECT statement? Do you want to
    open a recordset? Display the data to a user?

    --
    David W. Fenton http://www.dfenton.com/
    contact via website only http://www.dfenton.com/DFA/
     
    David-W-Fenton, Aug 21, 2011
    #7
  8. mi

    Tony Toews Guest

    On Sun, 21 Aug 2011 06:16:42 -0700 (PDT), mi
    <> wrote:

    >Can please anyone give me some idea what I'm doing wrong.


    As David points out, and I missed, is that you can't run a SELECT
    query using an Execute statement because there is no action, such as
    update, insert or delete to perform.

    We need to know what you want to do with the query to help you
    further. You likely want a recordset variable so you can loop
    through the recordset and do something.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Aug 22, 2011
    #8
  9. mi

    mi Guest

    It makes me really glad that someone wants to help me.

    I have excel-files that contain 4 columns which in the end shall
    inserts in another table with 12 columns.

    I’ve transformed the excel-files to tables.
    So now I want to read the new tables and add them to the main table
    but depending of the data in the 4 columns I will add different data
    in the rest 8 columns.

    That is why I’m trying to do select in the table.

    Is that clear enough?

    /Michelle
     
    mi, Aug 22, 2011
    #9
  10. mi

    mi Guest

    On Aug 22, 5:53 pm, mi <> wrote:
    > It makes me really glad that someone wants to help me.
    >
    > I have excel-files that contain 4 columns which in the end shall
    > inserts in another table with 12 columns.
    >
    > I’ve transformed the excel-files to tables.
    > So now I want to read the new tables and add them to the main table
    > but depending of the data in the 4 columns I will add different data
    > in the rest 8 columns.
    >
    > That is why I’m trying to do select in the table.
    >
    > Is that clear enough?
    >
    > /Michelle


    Noone go un idea how to solve this?

    /Michelle
     
    mi, Aug 23, 2011
    #10
  11. mi

    Tony Toews Guest

    On Mon, 22 Aug 2011 08:53:52 -0700 (PDT), mi
    <> wrote:

    >It makes me really glad that someone wants to help me.
    >
    >I have excel-files that contain 4 columns which in the end shall
    >inserts in another table with 12 columns.
    >
    >I’ve transformed the excel-files to tables.
    >So now I want to read the new tables and add them to the main table
    >but depending of the data in the 4 columns I will add different data
    >in the rest 8 columns.


    Ok, then you either want an append query with logic in it to insert
    the appropriate data. Or a recordset with a loop reading through all
    the records doing recordset adds.

    For sample recordset logic see
    http://www.granite.ab.ca/access/email/recordsetloop.htm

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Aug 24, 2011
    #11
  12. mi

    mi Guest

    On 21 Aug, 15:16, mi <> wrote:
    > On Aug 18, 3:27 am, Tony Toews <> wrote:
    >
    >
    >
    >
    >
    > > On Tue, 16 Aug 2011 07:52:47 -0700 (PDT), mi

    >
    > > <> wrote:
    > > >I am running a SQLquestion in  VB. This does not work and the
    > > >errormessage I get is poor:
    > > >"cannot execute a select query"

    >
    > > >SQLstr = "SELECT * FROM tblMag1;"
    > > >     CurrentDb.Execute SQLstr

    >
    > > add ", dbfailonerrors" to the currentdb line.

    >
    > > Tony
    > > --
    > > Tony Toews, Microsoft Access MVP
    > > Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
    > > Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
    > > For a convenient utility to keep your users FEs and other files
    > >   updated seehttp://www.autofeupdater.com/

    >
    > I try to  explanation a little better what I'm doing.
    >
    > Public Sub importTmpTb()
    > On Error GoTo Err_importTmpTb
    >
    > Dim bild As Integer
    > Dim magasin As String
    > Dim beskrivning As String
    >
    > I have tried select
    >
    > SQLstr = "SELECT  [tbMag1].[datum],[tbMag1].[bild] FROM tblMag1
    > WHERE ] tbMag1].[bild]   = 3 ; "
    >
    >  SQLstr = "SELECT [datum],[bild] FROM tblMag1 WHERE  [bild]  = 3; "
    >
    > as well as two differen execute
    >
    >  CurrentDb.Execute SQLstr,dbfailonerrors
    >  CurrentDb.Execute SQLstr
    >
    > And nothing works.
    >
    > Can please anyone give me some idea what I'm doing wrong.
    > Thanks in advance.
    >
    > /Michelle- Dölj citerad text -
    >
    > - Visa citerad text -


    Public Sub importTmpTb()

    fI've tried that aswell
    Dim Datum As String
    Dim bild As String
    Dim magasin As String
    Dim beskrivning As String


    SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild],[tbMag1].
    [beskrivning],[tbMag1].[magasin] FROM tblMag1 ; "
    CurrentDb.Execute SQLstr, dbfailonerrors


    I g've got the message as befor.:
    cannot execute a select query

    /Michelle
     
    mi, Sep 2, 2011
    #12
  13. mi

    Tony Toews Guest

    On Fri, 2 Sep 2011 10:21:05 -0700 (PDT), mi <>
    wrote:

    > SQLstr = "SELECT [tbMag1].[datum],[tbMag1].[bild],[tbMag1].
    >[beskrivning],[tbMag1].[magasin] FROM tblMag1 ; "
    > CurrentDb.Execute SQLstr, dbfailonerrors
    >
    >
    >I g've got the message as befor.:
    > cannot execute a select query


    That error message is by design. You can't execute a SELECT query.
    There is nothing there to execute. No INSERTs, UPDATEs or DELETEs.

    What is your objective with the query in the code? What are you
    trying to do?

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Sep 2, 2011
    #13
  14. mi

    mi Guest

    On Sep 2, 11:53 pm, Tony Toews <> wrote:
    > On Fri, 2 Sep 2011 10:21:05 -0700 (PDT), mi <>
    > wrote:
    >
    > >  SQLstr = "SELECT  [tbMag1].[datum],[tbMag1].[bild],[tbMag1].
    > >[beskrivning],[tbMag1].[magasin] FROM tblMag1 ; "
    > >   CurrentDb.Execute SQLstr, dbfailonerrors

    >
    > >I g've got the message as befor.:
    > > cannot execute a select query

    >
    > That error message is by design.  You can't execute a SELECT query.
    > There is nothing there to execute.  No INSERTs, UPDATEs or DELETEs.
    >
    > What is your objective with the query in the code?  What are you
    > trying to do?
    >
    > Tony
    > --
    > Tony Toews, Microsoft Access MVP
    > Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
    > Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
    > For a convenient utility to keep your users FEs and other files
    >   updated seehttp://www.autofeupdater.com/


    I have excel-files that contain 4 columns which in the end shall
    inserts in another table, we can call it table X, wich contanes 12
    columns.

    I’ve transformed the excel-files to tables.
    So now I want to read the new tables and add them to the to table X
    but depending of the data in the 4 columns I will add different data
    in the rest 8 columns, in table X.

    That is why I’m trying to do select in the table.

    Anyone got an idea how that can be done?

    Please can anyone help me?
    Thanks in advance.
    /Michelle
     
    mi, Sep 3, 2011
    #14
  15. mi

    Tony Toews Guest

    On Sat, 3 Sep 2011 06:03:54 -0700 (PDT), mi <>
    wrote:

    >I’ve transformed the excel-files to tables.
    >So now I want to read the new tables and add them to the to table X
    >but depending of the data in the 4 columns I will add different data
    >in the rest 8 columns, in table X.
    >
    >That is why I’m trying to do select in the table.
    >
    >Anyone got an idea how that can be done?
    >
    >Please can anyone help me?


    See the Sample Code illustrating looping through a DAO recordset page
    at the Access Email FAQ at
    http://www.granite.ab.ca/access/email/recordsetloop.htm

    Then you can add records to tables by using

    dim rsadd as dao.recordset

    and inside the loop

    rsadd.addnew
    rsadd!field1 = "something"
    rsadd!field2 = 123
    rsadd.update

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Sep 3, 2011
    #15
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page