[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MSDataShape and DSN-less SQL Connection

Posted on 2004-10-25
6
Medium Priority
?
507 Views
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. )
0
Comment
Question by:jd576
[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
  • 4
  • 2
6 Comments
 
LVL 18

Expert Comment

by:JR2003
ID: 12398530
   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
 
LVL 18

Accepted Solution

by:
JR2003 earned 1500 total points
ID: 12398694
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
 
LVL 18

Expert Comment

by:JR2003
ID: 12398708
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jd576
ID: 12399043
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
 
LVL 18

Expert Comment

by:JR2003
ID: 12399120
jd576
What are the error description and error number you get?
0
 

Author Comment

by:jd576
ID: 12407801
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

656 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