Copying Records

[mavtcr]

Hello friends..please help me
I have two identical Tables in my Database (Access) Table 1.Salup , Table 2.Salup1 .
All the fields are identical
My problem is:- I want to copy/append
1. All the records
2. selected Records agreeing certain criteria(eg."Sex=Male") From Table 1 to Table 2. through VB6 code.I started my coding as follows,But I could not succed with it. Set ACRS = New ADODB.Recordset If ACRS.State = adStateOpen Then ACRS.Close ACRS.Open "SELECT * FROM Salup1", CN, adOpenStatic, adLockOptimistic

[Reverend Jim]

Try

Dim qry As String
Dim con As New ADODB.Connection
con.Open("Driver={Microsoft Access Driver (*.mdb)};" _
       & "Dbq=database;Uid=user;Pwd=password;")
qry = "INSERT INTO table2 SELECT * FROM table1" 
con.Execute(qry)
con.Close()

If you want specific records then try

qry = "INSERT INTO table2 SELECT * FROM table1 " _
    & " WHERE Sex = 'Male'"

Substitute appropriate values for database, user, password, table1 and table2.

[mavtcr]

Thank you Reverend Jim.. Many times You helped me much..Thanks a lot
But now I have another problem Please see my code

If ACRS.State = adStateOpen Then ACRS.Close
ACRS.Open "SELECT * FROM Salup1", CN, adOpenStatic, adLockOptimistic
Do Until ACRS.EOF
    ACRS.MoveFirst
    Sempid = ACRS("Empid")
    Sname = ACRS("Name")
    Sdsgn = ACRS("Dsgn")
    Sdoj = ACRS("Doj")
    Sbp = ACRS("Bp") * (Sndm - ACRS("Npd")) / Sndm
    If ACRS.State = adStateOpen Then ACRS.Close
    ACRS.Open "SELECT * FROM Salary ", CN, adOpenStatic, adLockOptimistic
    ACRS.AddNew
    ACRS("Month") = Sfm
    ACRS("Empid") = Sempid
    ACRS("Name") = Sname
    ACRS("Dsgn") = Sdsgn
    ACRS("Doj") = Format$(Sdoj, "dd/mm/yyyy")
    ACRS("Bp") = Sbp
    ACRS.Update
    ACRS.Requery
    If ACRS.State = adStateOpen Then ACRS.Close
    ACRS.Open "SELECT * FROM Salup1", CN, adOpenStatic, adLockOptimistic
    ACRS.MoveFirst
    ACRS.Delete
Loop
MsgBox "SALARY CALCULATION OVER" + Chr$(13) + Chr$(13) + "DO YOU WANT TO APPLY?", vbYesNoCancel, "Pebble"

My problem is when the EOF is reached instead of quitting the do-loop
an error message is shown-Either BOF or EOF is true,or the current record has been deleted.Requested operation requires a current reecord

I want the programme to quit and go to the message box,when all the records from Salup1 is deleted after the operation.

Please help me

[Reverend Jim]

Modifying the recordset within a loop that terminates on EOF is like modifying a loop index within a For loop. It's generally a bad idea. If you can explain what you are trying to do perhaps I can suggest an alternative method.

[mavtcr]

Ok dear.. I want to calculate the monthly salary of staff (say 5 numbers) whose details are in the table Salup.I copied all the records to Salup1 table only for calculation,after calculation this table will be empty. I have no idea whether I can use directly the Table Salup itself for calculation.I have some knowledge in Foxpro language.There I used this method and I succeeded. I was trying that style.My idea is ,select the first recod,calculate,and update the calculated results in a table -Salary,then go to the next record and repeat. After calculation the table Salary must contain the salary details and the basic details for calclation must be in tact in Salup table
I hope, you got my task.

[Reverend Jim]

What are the fields in the tables Salup and Salary? And when you say monthly salary do you mean total or per employee? Please provide an explanation as to what the fields are. The names Dsgn, Doj and Bp are not descriptive.

[mavtcr]

Ok ..I mentioned only some sample fields to save space and time
Any way I experimented with my code and I am happy to inform you that I succedded to some extend.If I find any difficulty in proceeding from here I will come back to you otherwise I will mark it as solved.
I changed my code as follows. Only the relevant portion..

If ACRS.State = adStateOpen Then ACRS.Close
 ACRS.Open "SELECT * FROM Salup1", CN, adOpenStatic, adLockOptimistic
  Do Until ACRS.EOF
  If ACRS.State = adStateOpen Then ACRS.Close
 ACRS.Open "SELECT * FROM Salup1", CN, adOpenStatic, adLockOptimistic
    If ACRS.RecordCount = 0 Then
  MsgBox "SALARY CALCULATION OVER" + Chr$(13) + Chr$(13) + "DO YOU WANT TO APPLY?", vbYesNoCancel, "Pebble"
     Else
    ACRS.MoveFirst
   Sempid = ACRS("Empid")
   Sname = ACRS("Name")
   Sdsgn = ACRS("Dsgn")
   Sdoj = ACRS("Doj")
   Sbp = ACRS("Bp") * (Sndm - ACRS("Npd")) / Sndm
  ACRS.Delete
  If ACRS.State = adStateOpen Then ACRS.Close
ACRS.Open "SELECT * FROM Salary ", CN, adOpenStatic, adLockOptimistic
  ACRS.AddNew
   ACRS("Month") = Sfm
   ACRS("Empid") = Sempid
   ACRS("Name") = Sname
   ACRS("Dsgn") = Sdsgn
   ACRS("Doj") = Format$(Sdoj, "dd/mm/yyyy")
   ACRS("Bp") = Sbp
    ACRS.Update
    ACRS.Requery
   End If
   Loop

When I succeeded, Immediately I wanted to tell you without losing any time.
Ok thank you ...I will come back