dcsimg
Results 1 to 13 of 13

Thread: [Access] How do i create an Autoexec Macro with a specified Query?

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    [Access] How do i create an Autoexec Macro with a specified Query?

    Hello Guys,

    i need a AutoExec Macro that performs a Query if i open the Database.
    I have to Problems:

    1. My Query has a Problem...

    Code:
    INSERT INTO [Daten Sammlung] VALUES([CATS-B].[PersNr], [CATS-B].[Mitarbeiter Name], [CATS-B].[LstArt], [CATS-B].[Status], [CATS-B].[Bezeichnung Projekt], [CATS-B].[Kurztext], [CATS-B].[Datum], 0 as Stunden)
    WHERE[CATS-B].[Status]<>"30"
    UNION
    INSERT INTO [Daten Sammlung] VALUES([CATS-B].PersNr, [CATS-B].[Mitarbeiter Name], [CATS-B].LstArt, [CATS-B].Status, [CATS-B].[Bezeichnung Element], [CATS-B].Kurztext, [CATS-B].Datum, [CATS-B].Stunden)
    WHERE[CATS-B].Status="30";
    Maybe there is someone out there who can help me with this

    2. The Autoexec Macro is not able to perform a Query like this but i need a Table that has some edited Values in it and it has to be Updated everytime i Start the Database. (The Database is just a Buffer for a Tool and not a Archive or Data Storage)

    Code:
    Sub AutoExec()
       Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       
       Dim strSQL As String
       
       Set db = CurrentDb()
       Set qdf = db.QueryDefs("MyQuery")
       
      
       strSQL = "SELECT * FROM [CATS-B];"
       
       qdf.SQL = strSQL
       
       DoCmd.OpenQuery "My Query"
       
       Set db = Nothing
       Set qdf = Nothing
    End Sub
    That's my Macro but unfortunately its not working :P

    If there is someone who has an Idea or maybe has some spare time to think about it i would be the happiest Person on Earth

    All the Best

    Lulu the Frog
    Last edited by Lulufrosch; Sep 6th, 2018 at 04:06 AM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    1) Which Problem? It fails completely? it inserts the wrong values? Do the inserts work directly?
    2) What's not working?

    btw: Why are you mixing ADO and DAO?
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Quote Originally Posted by Zvoni View Post
    1) Which Problem? It fails completely? it inserts the wrong values? Do the inserts work directly?
    There appears an Error that this type of Query can not be performed with this Method... It Inserts no Data at all because its creating an Error in my Macro
    I think i have done a mistake at Connecting my Source Data Sheets. (I connected them using the "normal" function of Access to create a linked Table)

    Quote Originally Posted by Zvoni View Post
    2) What's not working?

    btw: Why are you mixing ADO and DAO?
    Well i am pretty new so i dont know which one to use/prefer... Where are the Main differences between these two Methods?
    My macro isn't starting when i Open the Database.... As you see i called it "Autoexec" and that does not seem to Work (or maybe its missing something).

    All the best

    Lulu the Frog

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Quote Originally Posted by Zvoni View Post
    1) Which Problem? It fails completely? it inserts the wrong values? Do the inserts work directly?
    There appears an Error that this type of Query can not be performed with this Method... It Inserts no Data at all because its creating an Error in my Macro
    I think i have done a mistake at Connecting my Source Data Sheets. (I connected them using the "normal" function of Access to create a linked Table)

    Quote Originally Posted by Zvoni View Post
    2) What's not working?

    btw: Why are you mixing ADO and DAO?
    Well i am pretty new so i dont know which one to use/prefer... Where are the Main differences between these two Methods?
    My macro isn't starting when i Open the Database.... As you see i called it "Autoexec" and that does not seem to Work (or maybe its missing something).

    All the best

    Lulu the Frog

  5. #5
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,294

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Code:
    INSERT INTO [Daten Sammlung] VALUES([CATS-B].[PersNr], [CATS-B].[Mitarbeiter Name], [CATS-B].[LstArt], [CATS-B].[Status], [CATS-B].[Bezeichnung Projekt], [CATS-B].[Kurztext], [CATS-B].[Datum], 0 as Stunden)
    WHERE[CATS-B].[Status]<>"30"
    UNION
    INSERT INTO [Daten Sammlung] VALUES([CATS-B].PersNr, [CATS-B].[Mitarbeiter Name], [CATS-B].LstArt, [CATS-B].Status, [CATS-B].[Bezeichnung Element], [CATS-B].Kurztext, [CATS-B].Datum, [CATS-B].Stunden)
    WHERE[CATS-B].Status="30";
    You cant do two inserts with a union
    You could perhaps try :
    Code:
    INSERT INTO [Daten Sammlung] 
    SELECT 
        cb.[PersNr], 
        cb.[Mitarbeiter Name], 
        cb.[LstArt], 
        cb.[Status], 
        cb.[Bezeichnung Projekt], 
        cb.[Kurztext], 
        cb.[Datum], 
        0 as Stunden
      FROM [CATS-B] AS cb
     WHERE cb.[Status]<>"30"
    UNION
     SELECT 
        cb.[PersNr], 
        cb.[Mitarbeiter Name], 
        cb.[LstArt], 
        cb.[Status], 
        cb.[Bezeichnung Projekt], 
        cb.[Kurztext], 
        cb.[Datum], 
        cb.Stunden
      FROM [CATS-B] AS cb
     WHERE cb.Status='30'
    Note it's a select rather than a value on the assumption [CATS-B] is a table
    Is the status text or a number? (if a number then you wont need the single quotes)

    This perhaps could be made smaller (but not having used access in a while I am unsure whether the following would work.
    Code:
    INSERT INTO [Daten Sammlung] 
    SELECT 
        cb.[PersNr], 
        cb.[Mitarbeiter Name], 
        cb.[LstArt], 
        cb.[Status], 
        cb.[Bezeichnung Projekt], 
        cb.[Kurztext], 
        cb.[Datum], 
        CASE 
          WHEN cb.[Status]='30' THEN cb.Stunden
          ELSE 0
       END as Stunden
      FROM [CATS-B] AS cb

    Code:
    Sub AutoExec()
       Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       
       Dim strSQL As String
       
       Set db = CurrentDb()
       Set qdf = db.QueryDefs("MyQuery")
       
      
       strSQL = "SELECT * FROM [CATS-B];"
       
       qdf.SQL = strSQL
       
       DoCmd.OpenQuery "My Query"
       
       Set db = Nothing
       Set qdf = Nothing
    End Sub
    The line in bold should be DoCmd.OpenQuery "MyQuery"
    But even this may not run with the replaced sql. Why not just
    Code:
    CurrentDB.execute strSQL
    **Again its been a while since I used Access so perhaps similar to this

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Hi Ecniv

    Thank you for taking your time to help me
    I tried both SQL Commands and none of them Work unfortunately.... I think the Problem with the Union will stay. I just got a thought that i might not need a Union if i do 2 consequtive Querys and just add the Data at the End of the Result from the first query.... But there is still a Problem with the Runtime Error 3065. It says my Query type is Wrong......(Still dont know how to solve it because i need to manipulate some Data otherwise the query would be useless)

    Your Correction in my Macro is fine but it was just a Coding Mistake here in the Forum... I tried your Command: CurrentDB.Execute but the Error still occurs...

    Maybe you have an Idea how to solve my Problem so i will describe it:

    I have two Excel Sheets with Data in it. The first Sheets Contains Data Records with a Status (30,50 and 60).
    The ones with the Status "30" are fine and just need to be uploaded in a Local Database Table.
    The other ones with Status "50" and "60" should be modified (Set the hours in the Recordset to "0") and then add them to the Database Table filled before.
    And all that has to be performed every single time i start the Database (thats why i need an AutoExec Macro).
    The two Datasheets are Linked to the Database because they are filled with new data regularly.

    If you have enough spare time or someone else who reads this then i would be very happy if you could give me some Tips/Ideas.

    All the best

    Lulu the Frog

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    SendStelle PersNr Datum Stunden LstArt Belegnummer PSP-Element Bezeichnung Element Bezeichnung Projekt Belegnummer CO Kurztext Status Mitarbeiter Name
    ASP001 | 00600054 | 15.02.2018 | 5,00 | BW | 0009888612 | H-S-B04-AB009 0051/18/9001-Fehlerbehebung | SINN 0051 DepBest Lw | 004100504249 | INC4795648 Prüf. Schnittst. 0049 | 30 | Birgit Mustermann
    ASP002 | 00600057| 15.02.2018 | 4,50 | BW | 0009888612 | H-S-B04-AB009 0051/18/9001-Fehlerbehebung | SINN 0051 DepBest Lw | 004100504249 | INC4795648 Prüf. Schnittst. 0049 | 30 | Birgit Mustermann
    ASP003 | 00600052 | 15.02.2018 | 9,00 | BW | 0009888612 | H-S-B04-AB009 0051/18/9001-Fehlerbehebung | SINN 0051 DepBest Lw | 004100504249 | INC4795648 Prüf. Schnittst. 0049 | 30 | Birgit Mustermann

    These are some Examples for the Data Records.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Right!
    https://www.w3schools.com/sql/sql_insert.asp
    There!
    It says:
    Code:
    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...); 

    But:
    If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

    Where in your INSERT is e.g. PSP-Element or [Bezeichnung Element]?
    btw: Don't use special characters in Field-Names. A BLANK is a special character in this case
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Code:
    INSERT INTO [Daten Sammlung] VALUES([CATS-B].[PersNr], [CATS-B].[Mitarbeiter Name], [CATS-B].[LstArt], [CATS-B].[Status], [CATS-B].[Bezeichnung Projekt], [CATS-B].[Kurztext], [CATS-B].[Datum], 0 as Stunden)
    WHERE[CATS-B].[Status]<>"30"
    UNION
    INSERT INTO [Daten Sammlung] VALUES([CATS-B].[PersNr], [CATS-B].[Mitarbeiter Name], [CATS-B].[LstArt], [CATS-B].[Status], [CATS-B].[Bezeichnung Projekt], [CATS-B].[Kurztext], [CATS-B].[Datum],[CATS-B].[Stunden])
    WHERE[CATS-B].[Status]="30"
    Actually i posted the wrong one sorry..... This one here is right. I found that link before but if the Syntax is correct i get this Runtime Error 3065.
    And why is my Macro not starting when i open the Database? (i tried it with several Code Blocks that work)
    How can i replace the Union Query? (I know there must be a way without a Union)

    Thank you for taking your time again Zvoni

    All the Best

    Lulu the Frog

  10. #10
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,294

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    As a question - if you open the MS Access DB. Create a new query, change to SQL entry and paste the following
    Code:
     SELECT 
        cb.[PersNr], 
        cb.[Mitarbeiter Name], 
        cb.[LstArt], 
        cb.[Status], 
        cb.[Bezeichnung Projekt], 
        cb.[Kurztext], 
        cb.[Datum], 
        cb.Stunden
      FROM [CATS-B] AS cb
    Does that run and bring back data or error?

    Change the view to tables and check the table names (connection) are pointing to the spreadsheet sheets correctly.
    You should be able to poen them with a double click to ensure they have data and that the column names are correct to the sql you posted.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    15

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    This one Works perfectly. It gives me the Data but i need the modified Data...
    is it easier to just put it in a Table with this Query and then doin another Query that eliminates the hours?

    The SELECT, INSERT and UNION Function works perfectly if i code them one by one but the combination Ends up in an Error

    Maybe you have so more Ideas of how to fix/combine them into One query.

    Only the best

    Lulu the Frog

  12. #12
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,294

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Ok, so select and union works but at the time of an insert it fails?

    Try this to see if it runs...
    Code:
    SELECT 
        cb.[PersNr], 
        cb.[Mitarbeiter Name], 
        cb.[LstArt], 
        cb.[Status], 
        cb.[Bezeichnung Projekt], 
        cb.[Kurztext], 
        cb.[Datum], 
        SWITCH(
          cb.[Status]='30' , cb.Stunden ,
          true, 0) as Stunden
      FROM [CATS-B] AS cb
    Found online as the case statement wont work with ms access sql (apparently - i havent tried it)
    If switch doesnt work and you only have one check then change the whole switch to
    Code:
    Stunden = iif(cb.[Status]='30' , cb.Stunden , 0)
    If it does then try adding
    Code:
    INSERT INTO [Daten Sammlung] ( [PersNr], [Mitarbeiter Name], [LstArt], [Status], [Bezeichnung Projekt], [Kurztext], [Datum], [Stunden] )
    (Assuming the same named fields)

    Also check the error message to see if it says something specific.
    In theory it should run in a query which you can save it, with a name you can see and understand. example qryInsertCatsB.

    As to the code you dont want to open query as this returns a recordset. Try with Exceute like I mentioned before.

    If it errors with cannot run sql, then there is a problem with the sql. usually to do with fields that need to be inserted/updated and one is a primary key, or is required. But usually the error message points at what is wrong.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,435

    Re: [Access] How do i create an Autoexec Macro with a specified Query?

    Scrap the UNION-INSERT.
    Just do 2 separate INSERTS and be done with the hassle
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width