Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append query (access 2000)

    i have built an apend query to increase the autonumbr qith 1000 but i get no results. Why is it so ? I am attaching the example

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

    Re: Append query (access 2000)

    It does work, but you only set the value of StudentID. You should also transfer the value of the eee field. The SQL becomes

    INSERT INTO TblStudents2 ( studentid, eee )
    SELECT [Studentid]+1000 AS Expr1, tblStudents.eee
    FROM tblStudents;

    You should delete the existing records from tblStudents2 before running the macro. If you try to run it twice in a row, you'll get an error message.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query (access 2000)

    i did it but again i failed.But i am sure i havent done something properly

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

    Re: Append query (access 2000)

    The query you have written will copy records from the table tblStudents into the table tblStudents2, adding 1000 to each StudentID in the process.
    To execute the query, you must either select Query | Run, or click the Run button on the toolbar, that is the button with the red exclamation mark.
    Please note that switching the view will *not* execute the query.

    If this is not what you want, please explain in detail what you want to accomplish.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query (access 2000)

    Thank you for your reply.Please help me because this is so important for me.First i cannot find the Run button.I cannot see it . I also went to view/toolbars/customize and selected everything about query but could not find the Run button..Is there a way to acomplish with with an sql and then use the command CurrentDb.Execute SQL.It will also be much easier for me to convert all the tables.
    I need to begin my tables with a new autonumber, while leaving the old autonumbers as they are.For example i have a table beginning with 5000. Now i want to add a new autonumber beginning with 500000 but not delete the numbers with 5000.So my aim is to place a new autonumber.
    Thank you for everything

    solar

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

    Re: Append query (access 2000)

    Instead of clicking the Run button, you can also select Query | Ruyn, as indicated in my previous reply.

    Do yo want to add the records to a new table or do you want to add them to the original table? Your SQL statement adds records from tblStudents to another table tblStudents2. Is that what you wanted?

    PS An AutoNumber is a meaningless unique ID. It shouldn't be necessary to set it to specific values. If you really need specific values, you shouldn't use an AutoNumber field, but a number field.

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

    Re: Append query (access 2000)

    You can also run the query by double clicking if from the database window, instead of opening it in design view.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query (access 2000)

    yes , yes, i ran with Run and it works ! Can i make an sql and then run the command " Currentd,execute sql " ? How should i do it, since it is not a simple sql ?

  9. #9
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query (access 2000)

    the following command is not effective :
    Dim SQL As String
    SQL = " INSERT INTO TblClients ( Clientid, CompanyName )SELECT [Clientid]+1000 AS Expr1, [TblClients].CompanyName FROM TblClients;"
    CurrentDb.Execute SQL

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

    Re: Append query (access 2000)

    That code should run. Change the last line to

    CurrentDb.Execute SQL, dbFailOnError

    This will cause Access to display an error message if something goes wrong.

    Keep in mind that if you have executed this query (or SQL) once, it will fail the second time because the increased ClientID's already exist.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query (access 2000)

    Thank for your precious help ! And now everything is just fine ! I got everything in order and my code runs smootlhly

  12. #12
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query (access 2000)

    How can i transfer the value of the eee field programatically.? When i have a blank table i have to move by hand from the first position, write eee and only then my code works.I want to do it programatically as :
    Public Function UpdateCompanyname()
    Dim sqlstring As String
    sqlstring = "UPDATE TblClients SET CompanyName = 'eee'WHERE Clientid = 1"
    CurrentDb.Execute sqlstring
    End Function
    Of course with a blank database it will not work, but is it possible to do it by code ?

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

    Re: Append query (access 2000)

    I don't understand what you want to do. Could you explain more clearly?

Posting Permissions

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