Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Getting error when using ADO for Data Access

    Hi folks,

    I felt like a change and decided to change from DAO to ADO, but am having a problem implementing it.

    I get the message:

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another
    This is my code:

    Code:
        Dim sSQL As String
        Dim sSQL2 As String
        Dim strLink As String
        Dim adoConn As ADODB.Connection
        Dim adoCmd As ADODB.Command
        Dim rs As ADODB.Recordset
        Dim stDocName As String
        Set adoConn = CurrentProject.Connection
        Set adoCmd = New ADODB.Command
        Set rs = New ADODB.Recordset
     
        sSQL = " SELECT Name FROM dbo_tblEmployerContacts" & _
               " WHERE EmpID = " & Form_frmEmployer.EmpID.Value
     
        With adoCmd
            .ActiveConnection = adoConn
            .CommandType = adCmdFile
            .CommandText = sSQL
        End With
     
        Set rs = adoCmd.Execute
     
        stDocName = "frmEngagement"
        strLink = stDocName & "." & Form_frmEmployer.EmpID.Value
     
        If rs.EOF = True Then
        MsgBox "No employer contacts found.  Please go back and add a contact.", vbOKOnly
        Else
        GoTo SQL2:
        End If
     
        rs.Close
        Set rs = Nothing
     
    SQL2:
        Set adoCmd = Nothing
        rs.Close
        Set rs = Nothing
     
        sSQL2 = " SELECT FirstName, Surname" & _
               " FROM dbo_tblCollegeContacts" & _
               " WHERE EmpID = " & Form_frmEmployer.EmpID.Value
     
        With adoCmd
            .ActiveConnection = adoConn
            .CommandType = adCmdFile
            .CommandText = sSQL2
        End With
     
        Set rs = adoCmd.Execute
     
        If rs.EOF = True Then
        MsgBox "No college contacts found. Please go back and add a contact.", vbOKOnly
        Else
        If MsgBox("You must fill in the following engagement information," & _
                  "or it will result in a blank record. Do you want to continue", vbYesNo, "WARNING") = vbYes Then
           DoCmd.OpenForm stDocName, , , , , , strLink
        End If
        End If
     
        rs.Close
        Set rs = Nothing
    Now when I debug it seems to bomb out during the first adoCmd 'With' Statement, specifically after setting the .CommandType.

    Since I'm a noob with ADO atm, what seems to be the problem here?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try
    Code:
    .CommandType = adCmdText
    instead.

    From help:
    Note Do not use the CommandTypeEnum values of adCmdFile or adCmdTableDirect....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks pootle, works perfectly now.

    Did you here the good news anyway. We got 'Outstanding' for our Ofsted Inspection, woo

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nicely done - I did not hear that. Congratulations.

    Ours finished last week. We got Satisfactory (3) which as you know is not satisfactory.

    The good news is MIS systems were mentioned as "excellent, comprehensive & well used" which to be honest is all I am bothered about - I'm returning to the world of the private sector come new year. So a woo from here too!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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