Converting DAO to ADO for SQL Linked Tables

[larrydavid]

Hello, I have successfully converted Access 2003 tables to SQL Server 2005 and the Access db is working good. The only thing I'm having a problem with is now that the back end tables are SQL, the DAO.Recordset isn't working. It is breaking on: Set prs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset):

Public Function ReNumberProjectPriority() As Boolean
Dim prs As DAO.Recordset
Dim strSQL As String
Dim i As Integer

On Error GoTo err_Renumber

strSQL = "SELECT tblProjects.* " & _
"FROM tblProjects " & _
"WHERE (((tblProjects.numPortfolioPriority) <> 0) And ((tblProjects.numProjID) <> 1)) " & _
"ORDER BY tblProjects.numRatingScore DESC , tblProjects.strProjName;"


Set prs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'* return false and exit function when no records are found
If prs.BOF And prs.EOF Then
ReNumberProjectPriority = False
Set prs = Nothing
Exit Function
End If

prs.MoveFirst

i = 0

Do While Not prs.EOF
i = i + 1
With prs
.Edit
!numPortfolioPriority = i
.Update
.MoveNext
End With
Loop

Set prs = Nothing
ReNumberProjectPriority = True

exit_renumber:
Exit Function

err_Renumber:
ReNumberProjectPriority = False
Resume exit_renumber

End Function

Can someone show me how I should recode for this so it doesn't skip at this point into the err_Renumber handler?

Thank you,
Lawrence

 

[larrydavid]

OK, I figured it out:

Public Function ReNumberProjectPriority() As Boolean
'Dim prs As DAO.Recordset
Dim prs As Recordset 'Me - 10/14/2010
Dim strSQL As String
Dim i As Integer

Call openTheDatabase 'Me - 10/14/2010
Set prs = New ADODB.Recordset 'Me - 10/14/2010

On Error GoTo err_Renumber

strSQL = "SELECT tblProjects.* " & _
"FROM tblProjects " & _
"WHERE (((tblProjects.numPortfolioPriority) <> 0) And ((tblProjects.numProjID) <> 229)) " & _
"ORDER BY tblProjects.numRatingScore DESC , tblProjects.strProjName;"


'Set prs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
prs.Open strSQL, cnPMO, adOpenDynamic, adLockOptimistic 'Me - 10/14/2010

'* return false and exit function when no records are found
If prs.BOF And prs.EOF Then
ReNumberProjectPriority = False
Set prs = Nothing
Exit Function
End If

prs.MoveFirst

i = 0

Do While Not prs.EOF
i = i + 1
With prs
'.Edit
!numPortfolioPriority = i
.Update
.MoveNext
End With
Loop

Set prs = Nothing
ReNumberProjectPriority = True

exit_renumber:
Exit Function

err_Renumber:
ReNumberProjectPriority = False
Resume exit_renumber

End Function

Thank you,
Lawrence