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. )
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

JR2003Connect With a Mentor Commented:
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
    End With
   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 :-)
This is what is suggested from

"Provider=MSDataShape;Data Provider=SQLOLEDB;Data Source=Aron1;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

jd576Author Commented:

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:

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
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?
What are the error description and error number you get?
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....
All Courses

From novice to tech pro — start learning today.