Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Passing XML from VBA to SQL Server

I wrote code to pass an XML parameter in ADO from Access 2003 VBA to SQL Server 2012 but get the error message  "Parameter object is improperly defined.  Inconsistent or incomplete information was provided." on the line of code that appends the parameter to the parameters collection.  I'm using the "MSOLEDBSQL" data access provider for the first time because I read that is supports XML.  Note: I'm also using the DataTypeCompatibility=80 keyword in the connection string, which I read has to be used for XML support.  I also believe I am passing well formed xml.  What am I doing wrong?  Here is the code and the xml ...

    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim param1 As New ADODB.Parameter
       
    cn.ConnectionString = "Provider=MSOLEDBSQL;" _
             & "Server=ALTEKDC2;" _
             & "Database=ALTEK;" _
             & "UID=user1;" _
             & "PWD=xyz;" _
             & "DataTypeCompatibility=80;"
'             & "MARS Connection=True;"     For Multiple Result Sets
    cn.Open
    'cn.Open Forms!DummyForm!tbxOLEConnectString
    With cmd
        .ActiveConnection = cn
        .CommandText = "procCheckConditions"
        .CommandType = adCmdStoredProc
        Set param1 = .CreateParameter("ProgramFunctionId", adInteger, adParamInput, , lngProgramFunctionId)
        .Parameters.Append param1
        MsgBox strXML
        Set param1 = .CreateParameter("XML", adLongVarChar, adParamInput, , strXML)
        .Parameters.Append param1    '***************************This is where it produces the error  ********************
        Set param1 = .CreateParameter("RetVal", adBoolean, adParamOutput)
        .Parameters.Append param1
        .Execute Options:=adExecuteNoRecords
        CheckConditions = .Parameters("RetVal")
    End With
    Set cn = Nothing
    Set param1 = Nothing
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Declan Basile

ASKER

Thanks.  Works great now.  I tried "SQLNCLI11" and it worked without having to download and install that data access provider.  Is it included in Windows 7 as part of DAC like the provider "SQLOLEDB" that I've always used in the past?  Or was it installed when I installed a local copy of SQL Server?  It'd be nice not to have to install it on all the client computers.
It is normally not part of Windows. But it is imho the fastest client for SQL Server.
Thanks very much!