Append query (access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote

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

Reply | Quote