asked on

OLEDB and ADODB with SQL2008R2 from VB6 program

We do have accounting program based on SQL 2005 and shipping base on Custom VB6 program.
Shipping VB 6 program is pointing to accounting Database and getting Sales Order info and then writing back to accounting once data is processed. We now upgrading accounting program to run on SQL 2008R2. While testing connection to a new system we re-pointed VB program to new SQL server 2008R2.
We can still pull data out but getting an error on writing back from VB to SQL. We tested security rights and it is not a problem.
Shipping Client machine with VB 6 program is running on XP SP3.

i tried native SQL string but it didn't even read from 2008r

strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my_SQL2008R2;Database=My_data;DataTypeCompatibility=80;"

ATTACHED IS A PIECE OF VB6 CODE WHICH IS FAILING
VB6prog.txt

MMTadmin

Work out what the error is!

uploaderror:
Msgbox err.description

Also who ever wrote that must have loved to type

Creating parameters is not that long winded

Set cmd = New Command
With cmd
.Parameters.Append .CreateParameter("Status", adTinyInt, adParamInput, 1, value)
End With

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leop1212

ASKER

i did use correct syntax
strCnn = "Provider=SQLNCLI10;Password=my_passw;Persist Security Info=True;User ID=sa;Server=my_SQL2008R2;Database=My_data;DataTypeCompatibility=80;"

and when I install SQL client tools on XP box it did read data via SQLNCL10 string but still getting an error on writing back. (no VB error) just a message box from the program
which is at the bottom of the VB code.