?
Solved

DSN less connection to SQL Server

Posted on 2003-03-13
25
Medium Priority
?
592 Views
Last Modified: 2008-07-24
I want to stop using a DSN to connect to SQL Server.

I need some advice and examples of how I can do this?
0
Comment
Question by:Tom Knowlton
[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
  • 12
  • 12
25 Comments
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8128666
For example I have Pass Through queries that have an ODBC Connection string property I have to set.

What would change under a DSN less connection?
0
 
LVL 2

Expert Comment

by:i014354
ID: 8128795
The following code is used with DAO to open an ODBC database, process a query, and return a set of records.

Notice that this code makes a "DSN-less" connection


Private Sub RS()

     Dim ws As Workspace
     Dim db As Database
     Dim rs As Recordset
     Dim sql As String
     sql = "Select * From titles"
     Set ws = DBEngine.Workspaces(0)
     Dim cnStr As String
     cnStr = "driver={SQL Server};server=mysvr;" & _
       "database=pubs;uid=myuid;pwd=mypwd"
     Set db = ws.OpenDatabase(Name:="PUBS", Exclusive:=False, _
       ReadOnly:=False, Connect:=cnStr)
     Set rs = db.OpenRecordset(sql, dbOpenDynaset)
     rs.MoveLast
     MsgBox "DAO: " & Str(rs.RecordCount) & " rows returned."
     rs.Close
     db.Close
     ws.Close
   End Sub
0
 
LVL 18

Expert Comment

by:1William
ID: 8128820
You need to 'Ping' the database.  Once this is done, you will not get another request to connect (for that session).  I created a module to do this, fired off by the open event of the first form I open.

Public Sub PingDatabase()   ' Ping the back end so that the application caches the connect string and won't ask again.

    Set db = OpenDatabase("", False, True, GetConnectString)
    DoCmd.Beep  ' Ping!
   
End Sub
Public Function GetConnectString() As String   ' Connection string utilizing a DSN-less connection

        GetConnectString = "ODBC;Driver={SQL Server};Server=KDCNT4HRI01;Database=XXXX;UID=XXXX;pwd=XXXX;network=dbmssocn"
   
End Function

Fill in the XX's with the apropriate values, and call Ping Database from your form.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8128821
How would Pass-Through queries work in this environemnt?
0
 
LVL 18

Expert Comment

by:1William
ID: 8128857
In my method, once the database has been pinged, the Access application 'caches' this information and the queries will not ask for it again.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8128895
1William:

Hmmm...okay.

So when I create a Pass-Through query programmatically....do I set the ODBC Connections string, or do I leave it blank?
0
 
LVL 18

Expert Comment

by:1William
ID: 8128962
Copy the code I posted to a module.  When you application opens, I assume you are also opening a form.  In the on Open event, include Call PingDatabase.
At this point, connectivty (handshake) has been established between SQL server and your app.  Access caches this handshake (this exisits for the lifetime of the session).
For a pass through query, yes, you do complete the ODBC connection string.  Access will look at that and confirm/compare/check to see if a connection had been previously made (which it has, via PingDatabase).
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8128987
So when I complete the ODBC connection string, what do I provide for the DSN= part?

Sorry I am so clueless on this stuff.  Just trying to understand.

Tom
0
 
LVL 18

Expert Comment

by:1William
ID: 8129049
Where is "DSN=part" in this:
GetConnectString = "ODBC;Driver={SQL Server};Server=XXXXXXXXX;Database=XXXX;UID=XXXX;pwd=XXXX;network=dbmssocn"

Because this is DSN-Less, there is no DSN
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129145
So DSN is not a required part of an ODBC Connection String?


For example, right now my Pass-Through queries ODBC Connection String property looks like this:

ODBC;DSN=ITDev;Database=pipeline

for when I do Development, and it looks like this

ODBC;DSN=Pipeline;Database=Pipeline

when I publish the MDB out to the network for everyone to use.
0
 
LVL 18

Accepted Solution

by:
1William earned 500 total points
ID: 8129296
Correct.
You will want it to look like this (sort of):
ODBC;Driver={SQL Server};Server=XXX

Again, where xxx is the server name

You'll of cached the connected string via the PingDatabase, so when an attempt is made to the same server/database, Access will already have the 'answers'.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129312
I think I get it!

Thanks!

I'll award you points now, but can I ask one or two follow-up questions later, once I get a chance to implement this?

Tom
0
 
LVL 18

Expert Comment

by:1William
ID: 8129329
No!

Just kidding, of course.

0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129382
How do I link to tables in SQL Server without using a DSN?

I used to right click in the Table objects window...Link Tables...type ODBC Databases....and then I have to use a DSN.

How does this change when going DSNless?
0
 
LVL 18

Expert Comment

by:1William
ID: 8129582
Ah ha!  I had a feeling this question was coming....


Add this to your module (where you put the PingDatabase and GetConnectString.

Public Sub LinkTables()
Dim strConnect1 As String
Dim tbl01 As TableDef

    Set db = CurrentDb
   
' Create the Tables Def(s)
    Set tbl01 = db.CreateTableDef("Name of Table in Access")    ' Target table name (Alias)
' Do The Connection
    tbl01.Connect = GetConnectString

' Set the Source
        tbl01.SourceTableName = "Name of Table in SQL server" ' Source Table Name
   
'Append the Table Def(s) to the Database
    db.TableDefs.Append tbl01
   
    DoCmd.Beep  ' Ping!
    MsgBox "Table Attachment(s) Completed", vbExclamation, "Attach Table(s) to XXXXXXXXX"
 
End Sub
0
 
LVL 18

Expert Comment

by:1William
ID: 8129603
Opps, forgot to mention.
You will want to define each table you want to link.  You can do them:
 one at time
a whole bunch of TDFs - can be a pain
Create a table list them and move through each record

To run the procedure, simply put your cursor in the procedure and hit run(F5)
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129649
This is how I currently re-link my tables:


~~~~~~~~~~~~~~~~~~~~


Function RelinkAllTables(strSQLDB As String, strDSN As String) As Boolean

    Dim tdf As TableDef
    Dim fLink As Boolean

    On Error GoTo HandleErr
    RelinkAllTables = False

    For Each tdf In CurrentDb.TableDefs
        With tdf
            If .Attributes = dbAttachedODBC Then
               ' MsgBox .Name
                If .Name <> "dbo_tblGrantApp" Then  ' Skip tblGrantApp in WEB database!
                    fLink = LinkTableDAO( _
                    strLinkName:=.Name, _
                    strDBName:=strSQLDB, _
                    strTableName:=.SourceTableName, _
                    strDSNName:=strDSN)
                End If
            End If
        End With
    Next tdf
   
    RelinkAllTables = fLink
   
ExitHere:
    Exit Function
   
HandleErr:
    RelinkAllTables = False
    MsgBox Prompt:=Err & ":  " & Err.Description, _
    Title:="Error in RelinkAllTables"
    Resume ExitHere
   
End Function

Public Function LinkTableDAO( _
    strLinkName As String, _
    strDBName As String, _
    strTableName As String, _
    strDSNName As String) As Boolean
   
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
   
On Error Resume Next
    Set db = CurrentDb
    Set tdf = db.TableDefs(strLinkName)
    If Err.Number = 0 Then
        db.TableDefs.Delete strLinkName
        db.TableDefs.Refresh
    Else
        Err.Number = 0
    End If
   
    Set tdf = db.CreateTableDef(strLinkName)
   
    tdf.Connect = _
        "ODBC;DSN=" & strDSNName _
        & ";Database=" & strDBName _
        & ";Integrated Security = True" _
        & ";TABLE=" & strTableName
       
    tdf.SourceTableName = strTableName
   
    db.TableDefs.Append tdf
   
    db.TableDefs.Refresh
   
    LinkTableDAO = (Err = 0)
End Function
0
 
LVL 18

Expert Comment

by:1William
ID: 8129704
Well, with a little finagling, the code I supplied will work fine.   If you want to drop the links and then re-link, thats ok too (like to toggle between development and Production databases).  Your drop link code should plug right in.  I'd suggest making the DropTable a seperate function though.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129708
Do I **HAVE** to have Table objects in my .MDB?
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129742
Meaning...can I reference my tables at runtime?  They all reside on SQL Server now...the DBA controls the design, integrity, etc.

How would this affect bound fields on the forms?
0
 
LVL 18

Expert Comment

by:1William
ID: 8129748
Well, if all of your queries are SQL pass though's, no.  Might make is a little easier to develop and test, though.  Your Production version could certainly do without.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129808
They are not all Pass Through's...only a small percentage are Pass Through's, but we are heading in that direction.
0
 
LVL 18

Expert Comment

by:1William
ID: 8129862
Well, you'll have to link all the required tables. Might as well do it for all possibly needed ones. (unless there are hundreds!) investigate putting the table names in a table, setting a constant to toggle between development and production modes.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8129893
Okay.
0
 
LVL 18

Expert Comment

by:1William
ID: 8129914
'What does not Kill You, Makes you Stronger'

I must be mighty strong 'cause everyone wants to kill me!


Best of luck.  You are on the right track.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

770 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