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. )
jd576Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JR2003Commented:
   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 :-)
0
JR2003Commented:
oops, I thought you wanted an unconected connection not a DSN-less connection!

I use this connection string for Oracle and it works without a DSN and for the MSDataShape provider.


    Dim g_MyDb As ADODB.Connection

    Dim sConnectString As String
    sConnectString = "Provider=MSDataShape.1;"
    sConnectString = sConnectString & "UID=" & Trim$(Username) & ";"
    sConnectString = sConnectString & "PWD=" & Trim$(Password) & ";"
    sConnectString = sConnectString & "DRIVER={Microsoft ODBC for Oracle};"
    sConnectString = sConnectString & "SERVER=" & Trim$(ServiceName) & ";"
    sConnectString = sConnectString & "Persist Security Info=True;"

    Set g_MyDb = New ADODB.Connection
    With g_MyDb
        .ConnectionString = sConnectString
        .Open
    End With
   
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JR2003Commented:
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;"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jd576Author Commented:
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?
0
JR2003Commented:
jd576
What are the error description and error number you get?
0
jd576Author Commented:
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....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.