Solved

OLEDB and ADODB with SQL2008R2 from VB6 program

Posted on 2013-01-17
3
517 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

717 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