?
Solved

OLEDB and ADODB with SQL2008R2 from VB6 program

Posted on 2013-01-17
3
Medium Priority
?
524 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 84

Accepted Solution

by:
Dave Baldwin earned 1500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Suggested Courses

649 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