how to rewrite code from DAO to ADO?

Question

We got some old legacy application which was developed during 2000 and we have moved from access 2003 to 2007. When I am trying to run a module of an application, it is giving me an error:

"Run-time error 3847. ODBCDirect is no longer supported. Rewrite the code to use ADO instead of DAO".

And it highlights to the line Set WS = CreateWorkspace("NewWS", "", "", dbUseODBC). As I am really new to Access, I did research before posting this issue here but no luck. I am trying to rewrite the code to use ADO instead of DAO.

Following is my old vba code:

Public Function GetID (ByRef SegmentItem As clsSegmentDefinitions) As Long 
    Dim qdf As QueryDef
    Dim qdfNewID As QueryDef
    Dim rs As Recordset
    Dim rsNewID As Recordset
    Dim NaturalDescription As String
    Dim WS As Workspace
    Dim con As Connection 
    Set WS = CreateWorkspace("NewWS", "", "", dbUseODBC)
    WS.DefaultCursorDriver = dbUseODBCCursor
    Set con = WS.OpenConnection("", , , SQLConnectString)
    DoCmd.Hourglass False
    DoCmd.OpenForm " frmQuickAdd_AddNatural ", , , , , acDialog, SegmentItem.AddValue
    DoCmd.Hourglass True
    If Form_frmQuickAdd_AddNatural.Tag Then
        Set qdf = con.CreateQueryDef("", "{ ? = call sp_Insert(?, ?, ?) }")
        qdf.Parameters.Refresh
        qdf![@prmDescription] = Left(Form_frmQuickAdd_AddNatural.txtSegmentDescription, 34)
        qdf![@prmCreateUser] = CurrentUser
        qdf![@prmProjectID] = 0
        qdf.Execute
        Set qdfNewID = CodeDb.CreateQueryDef("")
        qdfNewID.Connect = SQLConnectString
        qdfNewID.ReturnsRecords = True
        qdfNewID.SQL = "sp_GetNewSegmentID"
        Set rsNewID = qdfNewID.OpenRecordset
        If Not IsNull(rsNewID!MaxOfSegmentID) Then
            GetID = rsNewID!MaxOfSegmentID
        Else
            GetID = 0
        End If        
    Else
        GetID = 0
    End If
    DoCmd.Close acForm, "frmQuickAdd_AddNatural"    
End Function

I had started to rewrite code but I have no clue if it is suppose to be like this at all.

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnn.Open "Provider=mssql;Data Source=" & dbq & ";User Id=" & uid & ";Password=" & pwd
With rst
    .Open "SELECT COUNT(*) FROM " & tbl, cnn, adOpenKeyset, adLockOptimistic
    num = .Fields(0)
    .Close
End With
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Answer 1

First, you really don’t want to introduce ADO into an application built and designed around DAO. Worse, is ADO has been on its way out for about 15 years now. In fact SQL server is dropping support for oleDB which ADO works on. (so don’t go there).

See this link about SQL server dropping oleDB support:

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

The industry has moved away from ADO and all major vendors are suggesting to use Open Database Connectivity as the industry standard. (that means ODBC).

I would create and save a pass-though query in Access. You code can then be re-written as:

Public Function GetID(ByRef SegmentItem As String) As Long

  Dim strSQL     As String

  strSQL = "sp_Insert('" & _
           Left(Form_frmQuickAdd_AddNatural.txtSegmentDescription, 34) & "'," & _
           "'" & CurrentUser & "', 0)"

  With CurrentDb.QueryDefs("qryPass")
     .SQL = strSQL
     .ReturnsRecords = False
     .Execute
  End If

  With CurrentDb.QueryDefs("qryPass")
     .SQL = "sp_GetNewSegmentID"
     .ReturnsRecords = True
     GetID = Nz(.OpenRecordset()("MaxOfSegmentID"),0)
  End With

End Function

So create one pass-though query. And you can use it quite much in all places where you were using JET-DIRECT. In access 2007, jet-direct support was dropped, but use of a simple pass-though query will more than suffice and also as the above shows save buckets of coding and developer time. If the “left” expression you have can return a null, then you likely need to wrap that expression in a nz() to return a “” (null string) or the appropriate value.