Link to home
Start Free TrialLog in
Avatar of jd576
jd576

asked on

MSDataShape and DSN-less SQL Connection

I am having trouble forming a DSN-less SQL Connecton string for a MSDataShape query used by DataReport - Using VB6 ADO 2.8.

For reference, these are my functioning Connection Strings, using DSN:

Standard SQL DB Connection (using ADO):

sConnection = "Provider=SQLOLEDB.1;Password=" & SQLpw & ";Persist Security Info=False;User ID=" & SQLUser & ";Initial Catalog=" & sDB & ";Data Source=" & sData

SQL Using MSDataShape for DataReports:

sConnectionRpt = "Provider=MSDataShape.1;Persist Security Info=False;Data Source=" & sData & ";Data Provider=SQLOLEDB.1;Password=" & SQLpw & ";Persist Security Info=False;User ID=" & SQLUser & ";Initial Catalog=" & sDB

This DSN-less connection works for SQL DB connection:

sConnection = "DRIVER=SQL Server;UID=" & SQLUser & ";Password=" & SQLpw & ";" & _
"Network=DBMSSOCN;APP=Microsoft Data Access Components;SERVER=" & sData & ",1433;" & _
"Initial Catalog=" & sDB & ";Persist Security Info=False"

This is what I tried (AND DOES NOT WORK) for MSDataShape:

sConnectionRpt="Provider=MSDataShape.1;DRIVER=SQL Server;UID=" & SQLUser & ";Password=" & SQLpw & ";" & _
"Network=DBMSSOCN;APP=Microsoft Data Access Components;SERVER=" & sData & ",1433;" & _
"Initial Catalog=" & sDB & ";Persist Security Info=False"

Anyone have an idea how to write this DSNless connection for use with MSDataShape??  (I will be totally amazed if anyone comes up with the quick answer for this one. )
Avatar of JR2003
JR2003
   Dim sSql As String
    Dim rsFabricated As ADODB.Recordset
   
    Set fso = New Scripting.FileSystemObject
    sSql = "SHAPE APPEND NEW adVarChar(" & iLineSize & ") AS LineDetail "
   
    Dim MyConn As ADODB.Connection
    Set MyConn = New ADODB.Connection
    MyConn.Open "Provider=MSDataShape;Data Provider=None;"
   
    Set rsFabricated = New ADODB.Recordset
    With rsFabricated
        .Open sSql, MyConn, adOpenStatic, adLockOptimistic, -1
    End With


JR :-)
ASKER CERTIFIED SOLUTION
Avatar of JR2003
JR2003
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 JR2003
JR2003
This is what is suggested from http://www.connectionstrings.com/

"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
Avatar of jd576
jd576

ASKER

JR2003:

You are on the same track I tried.  My connection is essentially this, with a little mre.  I tried  your string, using the necessary DRIVER=SQL Server or DRIVER={SQL Server}, but it  does not work.  I need to add:

Network=DBMSSOCN;
APP=Microsoft Data Access Components

When I run this (essentially the string I give in my question), I get an error on the first attempt at executing
          .ConnectionRpt.Open
BUT if I simply resume on the error, it then continues, executes, opens, and runs the report.

I cannot figure this one out.  I do nothing to correct the error, just resume.   Any ideas on that?
Avatar of JR2003
JR2003
jd576
What are the error description and error number you get?
Avatar of jd576
jd576

ASKER

The error is something like "connection property" error.  I don't remember now, and am working at the moment on a network that blocks my SQL access to test.

I will give you the points on this.  Your answer is essentially correct.  Whatever my error problem, I will obviously need to figure it out myself....