Results 1 to 3 of 3
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Convert DAO to ADO (VBA/Excel/2000)

    Can anyone tell me the proper syntax to use ADO in the following example:<pre>Public Sub GetLastMonth()
    Dim dat As cDataRow
    <font color=red> 'coming from LoadDataRowObjects
    'Open database at beginning</font color=red>

    Dim wrkJet As Workspace
    <font color=red>'Dim dbsJobData As DAO.Database
    'Dim rstTemp As DAO.Recordset</font color=red>
    Dim dbsJobData As ADODB.Connection
    Dim rstTemp As ADODB.Recordset

    <font color=red>'Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "", dbUseJet)
    'Set dbsJobData = OpenDatabase(DataPath & "JobData.mdb", False)</font color=red>
    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</pre>


    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. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert DAO to ADO (VBA/Excel/2000)

    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

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Convert DAO to ADO (VBA/Excel/2000)

    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •