Solved

OLEDB and ADODB with SQL2008R2 from VB6 program

Posted on 2013-01-17
3
511 Views
Last Modified: 2013-01-28
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
0
Comment
Question by:leop1212
3 Comments
 
LVL 2

Expert Comment

by:MMTadmin
ID: 38789859
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
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 38790509
You are missing a ';' after "User ID=sa".  What is 'DataTypeCompatibility'?

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

This is the format I use from http://www.connectionstrings.com/sql-server-2008 :

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;
0
 

Author Comment

by:leop1212
ID: 38794580
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question