Convert DAO to ADO (VBA/Excel/2000)

Topic

New Reply

Can anyone tell me the proper syntax to use ADO in the following example:

Public Sub GetLastMonth()
      Dim dat As cDataRow
    'coming from LoadDataRowObjects
    'Open database at beginning

     Dim wrkJet As Workspace
'Dim dbsJobData As DAO.Database
'Dim rstTemp As DAO.Recordset
    Dim dbsJobData As ADODB.Connection
    Dim rstTemp As ADODB.Recordset

    'Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "", dbUseJet)
    'Set dbsJobData = OpenDatabase(DataPath & "JobData.mdb", False)
    Set wrkJet = CreateWorkspace("daSpace", "admin", "", adUseClient)            <-- blows up here
    dbsJobData.Open "PROVIDER=MSDASQL;driver={SQL Server};" _
        & "server=DATABASE;database=SalesCommisions;"
    Set rstTemp = New ADODB.Recordset
    
    For Each dat In mCol
        GetFieldData dat, dbsJobData, rstTemp
    Next
    
    rstTemp.Close
    dbsJobData.Close
    wrkJet.Close

I have about 6 places in the program I am converting that formerly went to Access 2000 databases that should now go to the SQL Server.

Reply | Quote

Replies

Workspace is a DAO object, not an ADODB object. You should remove all lines referring to wrkJet, for you don’t need it any more. The Open method of the ADODB Connection object lets you specify user and password, if necessary; the syntax is

connection.Open ConnectionString, UserID, Password, Options

Reply | Quote

Workspace is a DAO object, not an ADODB object. You should remove all lines referring to wrkJet, for you don’t need it any more. The Open method of the ADODB Connection object lets you specify user and password, if necessary; the syntax is

connection.Open ConnectionString, UserID, Password, Options

Reply | Quote

I think the above suggestion might get you all the way to where you’re heading, but to avoid confusion, you might want to preface all your ADO-specific variable declarations with the library name, that is, for example, ADODB.Recordset, etc. It should soon become apparent which no longer belong in the project.

Reply | Quote

I think the above suggestion might get you all the way to where you’re heading, but to avoid confusion, you might want to preface all your ADO-specific variable declarations with the library name, that is, for example, ADODB.Recordset, etc. It should soon become apparent which no longer belong in the project.

Reply | Quote