Dear all.........
I'm sorry for asking this........
But I found a hard for me to merge 2 tables in Access database.
I have Absence.mdb which has 2 tables
1. Table In (the fields are ID=text, DateIn=date, HoursIn=date)
2. Table Out (the fields are ID=text, DateOut=date, HoursOut=date)

I'm using ADODB without Component which like this

On Error Resume Next
    pvpBar1.Visible = True
    Set cnM = New ADODB.Connection
    Set rsM = New ADODB.Recordset
    
    xM = "Select * From In Where (((In.DateIn) Between # " & CDate(dbeginning.Value) & " # And # " & CDate(dend.Value) & " # ));"
    cnM.Open vCnTrans
    cnM.CursorLocation = adUseClient
    rsM.CursorLocation = adUseClient
    rsM.Open xM, cnM, adOpenKeyset, adLockOptimistic

    vRec = 0
    pvpBar1.Value = 0
    pvpBar1.Limit = rsM.RecordCount - 1
    If Not rsM.EOF Then
        Do While Not rsM.EOF
            vRec = vRec + 1
            Data1.Recordset.AddNew
            Data1.Recordset.Fields("ID") = rsM.Fields("ID")
            Data1.Recordset.Fields("DateIn") = rsM.Fields("dateIn")
            Data1.Recordset.Fields("User") = cUserId
            Data1.Recordset.Update
            pvpBar1.Value = vRec
            rsM.MoveNext
            If rsM.EOF Then
                Exit Do
            End If
        Loop
    End If
    rsM.Close
    cnM.Close
    Set cnM = Nothing
    Set rsM = Nothing
    Data1.Refresh

    Data1.Refresh
    Set cnM = New ADODB.Connection
    Set rsM = New ADODB.Recordset
    xM = "Select * From Out Where ID='" & Data1.Recordset!ID & "' And (((Out.TglOut) Between # " & CDate(dbeginning.Value) & " # And # " & CDate(dend.Value) & " # ));"
        
    cnM.Open vCnTrans
    cnM.CursorLocation = adUseClient
    rsM.Open xM, cnM, adOpenKeyset, adLockOptimistic
    
    vRec = 0
    pvpBar1.Limit = rsM.RecordCount - 1
    If Not rsM.EOF Then
        Do While Not rsM.EOF
            vRec = vRec + 1
            If rsM!ID = Data1.Recordset!ID And CDate(rsM!DateOut) = CDate(Data1.Recordset!dateIn) Then
                Data1.Recordset.Edit
                Data1.Recordset!dateOut = rsM!DateOut
                Data1.Recordset.Update
            Else
                Data1.Recordset.AddNew
                Data1.Recordset!ID = rsM!ID
                Data1.Recordset!DateOut = rsM!DateOut
                Data1.Recordset.Update
            End If
            pvpBar1.Value = vRec
            rsM.MoveNext
            If rsM.EOF Then
                Exit Do
            End If
        Loop
    End If
    rsM.Close
    cnM.Close
    Set cnM = Nothing
    Set rsM = Nothing

The point is ID in both tables (In and Out) has value of Date In and Out are the same according to date, and the result is being added in table Join (ID, DateIn, DateOut).

Looking foward to hear from you guys
Thanks

Hi,
What is the issue here? Why not add the data into a third table, or, if the data connection is readonly, use a query instead?
What result do you want?
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Dear stickleprojects,
Thank you for the response......
I think I have told you the point, but I'll tell you again.

I have 3 tables
1. Table In (the fields are ID=text, DateIn=date, HoursIn=date)
2. Table Out (the fields are ID=text, DateOut=date, HoursOut=date)
3.Table Join (the fields are ID=text, DateIn=date, DateOut=date).

Wich I want to added all data in In and Out to add in Join table
where ID and Date are same.

Example : if ID 123 In date 31-08-2007 pick data in Out table where ID 123 and date 31-08-2007 and add it in Join table

I'm sorry for a bad English tenses, I hope your are understand.

Hoping to hearing from you soon

Thank you

I have tried again with Indeks in my DAO connection and it's works

Thank you guys...........

Case closed.........