Help with Import from SQL Server 1

[sunmorgus]

Hello, I need a little help with an import I am doing from some SQL Server tables to my Access 2000 database. Using a VBA module, and some ADO, I have a connection to the SQL database working properly, and a select statement that when run in an Access query pulls all the records I need, but when I run my VBA module, the last record in the recordset is not pulled in. Here is the code:

Public Sub GetNewHDHP()
Dim adICCRs As ADODB.Recordset
Dim adHRRs As ADODB.Recordset
Dim appSQL, appSQL1, wrkSQL, delSQL, selSQL As String

'open connections to sql server and hr reports
Set adICCConn = New ADODB.Connection
Set adHRRptsConn = New ADODB.Connection
adICCConn.Open "Provider=SQLOLEDB.1;Password=*******;Persist Security Info=True;" & _
                "User ID=sa;Initial Catalog=icc;Data Source=dmbowman4"
adHRRptsConn.Open CurrentProject.Connection

    appSQL = "SELECT PYMAST.PYSIN, PYDEDH.[DHEMP#], PYMAST.PYFNAM, PYMAST.PYSNAM, PYDEDH.DHTYPE, PYDEDH.DHCODE as DedCodeAll, PYDEDH.DHAMT, PYDEDH.DHCKDT AS CheckDate " & _
                "FROM PYDEDH INNER JOIN PYMAST ON PYDEDH.[DHEMP#] = PYMAST.[PYEMP#]" & _
                " WHERE (((PYDEDH.DHTYPE)='AHC') AND ((PYDEDH.DHCODE)>=36 And (PYDEDH.DHCODE)<=39)) OR (((PYDEDH.DHTYPE)='AHC') AND ((PYDEDH.DHCODE)>=44 And (PYDEDH.DHCODE)<=47)) OR " & _
                "(((PYDEDH.DHTYPE)='AHC') AND ((PYDEDH.DHCODE)>=84 And (PYDEDH.DHCODE)<=88)) OR (((PYDEDH.DHTYPE)='AHC') AND ((PYDEDH.DHCODE)>=93 And (PYDEDH.DHCODE)<=95));"
                
    appSQL1 = "SELECT PYMAST.PYSIN, PYDEDH.[DHEMP#], PYMAST.PYFNAM, PYMAST.PYSNAM, PYDEDH.DHTYPE, PYDEDH.DHCODE as DedCode49, PYDEDH.DHAMT, PYDEDH.DHCKDT AS CheckDate " & _
                "FROM PYDEDH INNER JOIN PYMAST ON PYDEDH.[DHEMP#] = PYMAST.[PYEMP#]" & _
                " WHERE (((PYDEDH.DHTYPE)='AHC') AND ((PYDEDH.DHCODE)=49));"
                
    selSQL = "SELECT * FROM NewHDHPWorkFile WHERE (DHCODE = 49);"
    
    
'delete workfile
Set adHRRs = New ADODB.Recordset
    delSQL = "DELETE NewHDHPWorkFile.* FROM NewHDHPWorkFile;"
adHRRs.Open delSQL, adHRRptsConn, adOpenKeyset, adLockOptimistic
Set adHRRs = Nothing
'delete workfile1
Set adHRRs = New ADODB.Recordset
    delSQL = "DELETE NewHDHPWorkFile1.* FROM NewHDHPWorkFile1;"
adHRRs.Open delSQL, adHRRptsConn, adOpenKeyset, adLockOptimistic
Set adHRRs = Nothing

'update the workfile from icc
Set adICCRs = New ADODB.Recordset
Set adHRRs = New ADODB.Recordset
adICCRs.Open appSQL, adICCConn, adOpenKeyset, adLockOptimistic
    wrkSQL = "SELECT * FROM NewHDHPWorkFile;"
adHRRs.Open wrkSQL, adHRRptsConn, adOpenKeyset, adLockOptimistic
With adICCRs
    Do While Not .EOF
        adHRRs.AddNew
        adHRRs.Fields("PYSIN") = .Fields("PYSIN")
        adHRRs.Fields("DHEMP#") = .Fields("DHEMP#")
        adHRRs.Fields("PYFNAM") = .Fields("PYFNAM")
        adHRRs.Fields("PYSNAM") = .Fields("PYSNAM")
        adHRRs.Fields("DHTYPE") = .Fields("DHTYPE")
        adHRRs.Fields("DedCodeAll") = .Fields("DedCodeAll")
        adHRRs.Fields("DHAMT") = ((.Fields("DHAMT") * 52) / 12)
        adHRRs.Fields("CheckDate") = JulianToDate(.Fields("CheckDate"))
'        .Update
        .MoveNext
    Loop
End With
Set adICCRs = Nothing
Set adHRRs = Nothing

'update workfile1 from icc
Set adICCRs = New ADODB.Recordset
Set adHRRs = New ADODB.Recordset
adICCRs.Open appSQL1, adICCConn, adOpenKeyset, adLockOptimistic
    wrkSQL = "SELECT * FROM NewHDHPWorkFile1;"
adHRRs.Open wrkSQL, adHRRptsConn, adOpenKeyset, adLockOptimistic
With adICCRs
Do Until .EOF
        adHRRs.AddNew
        adHRRs.Fields("PYSIN") = .Fields("PYSIN")
        adHRRs.Fields("DHEMP#") = .Fields("DHEMP#")
        adHRRs.Fields("PYFNAM") = .Fields("PYFNAM")
        adHRRs.Fields("PYSNAM") = .Fields("PYSNAM")
        adHRRs.Fields("DHTYPE") = .Fields("DHTYPE")
        adHRRs.Fields("DedCode49") = .Fields("DedCode49")
        adHRRs.Fields("DHAMT") = ((.Fields("DHAMT") * 52) / 12)
        adHRRs.Fields("CheckDate") = JulianToDate(.Fields("CheckDate"))
'        .Update
        .MoveNext
    Loop
End With
Set adICCRs = Nothing
Set adHRRs = Nothing

CloseConn
End Sub

The records are put in the workfile table via the do until loops towards the end of the code. I got the idea from a microsoft article here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

Any help that can be provided would be greatly appreciated. Thanks!

 

[RoyVidar]

ADO recordsets update whenever one moves to the next record, which will work quite nicely for almost all the records, but since you've commented the .update statement, it doesn't update (add) the last record. Either uncomment it, or fire it once after the loop(s).

Roy-Vidar

 

[sunmorgus]

I am sorry, I should have checked the code more closely before I pasted it in. I have tried running it with the .update statement uncommented, with the same result. I have not however tried it outside of the loop, so I will give that a shot. Thanks!

 

[sunmorgus]

Moving it outside of the loop didn't change anything. Also, I have tried the loop with both 'Do until .eof' and 'Do while not .eof'

 

[RoyVidar]

Ah - but the update is on your "from" recordset, you need

[tt]adHRRs.Update[/tt]

Roy-Vidar

 

[sunmorgus]

Thank you very much! I had not even thought to try that!

STAR FOR YOU!