How to transfer new data in ms.access to sql server

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Octo Siburian
    New Member
    • Oct 2010
    • 19

    #1

    How to transfer new data in ms.access to sql server

    I have 2 databases, sql server and ms.access,
    I want to move existing data in database ms.access to database sql server using looping

    Code:
    Dim jlhField As Long
    
    Set rsSIKawan = New ADODB.Recordset
    Set rsDataRAS = New ADODB.Recordset
    Set rsSQL = New ADODB.Recordset
    
     QuerySelect = "SELECT a.DN, b.DIN, c.PIN, " & _
                   "c.UserName, b.Clock, d.ItemName " & _
                   "FROM ((ras_Device a INNER JOIN ras_AttRecord b ON a.DN=b.DN) " & _
                   "LEFT JOIN ras_Users c ON b.DIN=c.DIN) " & _
                   "LEFT JOIN ras_AttTypeItem d ON b.AttTypeId=d.ItemId " & _
                   "WHERE b.DIN=c.DIN"
     Set rsDataRAS = da_GetRs(QuerySelect)
     
     'Mengambil data dari dbSIKawan
     'QuerySelectSIKawan = "SELECT * FROM " _
                        & "finger_data_karyawan"
     'Set rsSIKawan = da_GetRecord(QuerySelectSIKawan)
     
     rsSQL.CursorLocation = adUseClient
     
     With rsSQL.Fields
          .Append "idNo", adInteger
          .Append "device", adInteger
          .Append "idFinger", adInteger
          .Append "nikKary", adVarChar, 50
          .Append "namaKary", adVarChar, 100
          .Append "time", adDate
          .Append "tipeAbsen", adVarChar, 64
     End With
    
     rsSQL.Open
    
        If rsDataRAS.RecordCount > 0 Then
        jlhField = 0
        
           While Not rsDataRAS.EOF Or rsDataRAS.BOF
                 rsSQL.AddNew
                 rsSQL("idNo").Value = jlhField + 1
                 rsSQL("device").Value = rsDataRAS("DN").Value 'field device
                 rsSQL("idFinger").Value = rsDataRAS("DIN").Value 'field finger ID
                 rsSQL("nikKary").Value = rsDataRAS("PIN").Value 'field nik karyawan
                 rsSQL("namaKary").Value = rsDataRAS("UserName").Value 'field nama karyawan
                 rsSQL("time").Value = rsDataRAS("Clock").Value 'field waktu
                 rsSQL("tipeAbsen").Value = rsDataRAS("ItemName").Value 'field tipe waktu
              
                '06.10.2010
                'simpan ke dbSIKawan
                 rsSQL.Update
                 rsDataRAS.MoveNext
                 jlhField = jlhField + 1
    
                        QueryInsertSIKawan = "INSERT INTO finger_data_karyawan " _
                            & "(idNo, Device, idFinger, NIKKary, NamaKary, " _
                            & "TglHadir, TimeIn, TipeAbsen) " _
                            & "VALUES('" & rsSQL("idNo").Value & "', '" & rsSQL("device").Value & "', " _
                            & "'" & rsSQL("idFinger").Value & "', '" & rsSQL("nikKary").Value & "', " _
                            & "'" & rsSQL("namaKary").Value & "', " _
                            & "'" & Format(rsSQL("time").Value, "mm/dd/yyyy") & "', " _
                            & "'" & Format(rsSQL("time").Value, "mm/dd/yyyy h:mm:ss") & "', " _
                            & "'" & rsSQL("tipeAbsen").Value & "')"
                     
              DoEvents
            Wend
           
        End If

    The data made it into database sql server , but when I stop running and then run the application again, the same data/field/row back into the database sql server.
    Hmm, I want each of the data/field/row already entered into a database sql server does not re-signed but the new data/field/row in ms.access that will be into database sql server as a new data/field/row

    Can anyone help me to find solution in my problem?
    please help me..

    Regards,
    Octo
    Last edited by Niheel; Oct 12 '10, 11:44 AM.
Working...