MSDataShape and DSN-less SQL Connection

Posted on 2004-10-25
Last Modified: 2013-11-23
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. )
Question by:jd576
    LVL 18

    Expert Comment

       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 :-)
    LVL 18

    Accepted Solution

    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
    LVL 18

    Expert Comment

    This is what is suggested from

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

    Author Comment


    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?
    LVL 18

    Expert Comment

    What are the error description and error number you get?

    Author Comment

    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....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now