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

    SQL Server & VBA (VBA/Excel/2000/SR1)

    Can someone tell me why this keeps blowing up? I am sucessfully retreiving data from the SQL Server but now I am at the point where we need to send updates back up the pipeline.
    <pre>Public Sub SaveMonthDataC()
    Dim dat As cDataRow

    <font color=red> 'Open database at beginning</font color=red>

    Dim wrkJet As Workspace
    Dim dbsJobData As DAO.Database
    Dim rstTemp As DAO.Recordset
    Dim cNn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCnn As String


    Set cNn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    cNn.CursorLocation = adUseClient
    cNn.Open "PROVIDER=MSDASQL;driver={SQL Server};server=DATABASE;" _
    & "database=SalesCommisions;uid=;pwd=;"
    rst.Open "CFS", cNn


    <font color=red>' Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "", dbUseJet)
    ' Set dbsJobData = OpenDatabase(DataPath & "JobData.mdb")
    ' Set rstTemp = dbsJobData.OpenRecordset("dbo_CFS", dbOpenDynaset) 'old code</font color=red>

    For Each dat In mCol
    SaveRowDataC dat, cNn, rst <--- Type Mismatch Runtime 13 here
    Next

    rstTemp.Close
    dbsJobData.Close
    wrkJet.Close
    rst.Close
    cNn.Close
    Set rstTemp = Nothing
    Set dbsJobData = Nothing
    Set wrkJet = Nothing

    End Sub
    -------------------
    Private Sub SaveRowDataC(DataObject As cDataRow, DataFile As DAO.Database, RecSet As DAO.Recordset)
    Dim cel As range
    Dim strFormat As String

    RecSet.FindFirst "[JobNo] = '" & DataObject.JobNumber & "'" & _
    " AND [YearMonth] = '" & strThisYearMonth & "'"


    If RecSet.NoMatch Then
    RecSet.AddNew
    RecSet.Fields("JobNo").Value = DataObject.JobNumber
    RecSet.Fields("YearMonth").Value = strThisYearMonth
    Debug.Print "nomatch " & DataObject.JobNumber
    Else
    RecSet.Edit
    End If</pre>

    Alan

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

    Re: SQL Server & VBA (VBA/Excel/2000/SR1)

    The definition

    Sub SaveRowDataC(DataObject As cDataRow, DataFile As DAO.Database, RecSet As DAO.Recordset)

    shows that SaveRowDataC expects DAO arguments. You call it as

    SaveRowDataC dat, cNn, rst

    where cNn and rst are ADODB arguments (and it is not clear what dat is from the code you posted)

  3. #3
    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

    Re: SQL Server & VBA (VBA/Excel/2000/SR1)

    I just discovered that.
    Alan

Posting Permissions

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