Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

DSN less connection to SQL Server

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
Tom Knowlton
Asked:
Tom Knowlton
  • 12
  • 12
1 Solution
 
Tom KnowltonWeb developerAuthor Commented:
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
 
i014354Commented:
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
 
1WilliamCommented:
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
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.

 
Tom KnowltonWeb developerAuthor Commented:
How would Pass-Through queries work in this environemnt?
0
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
No!

Just kidding, of course.

0
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
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
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
Do I **HAVE** to have Table objects in my .MDB?
0
 
Tom KnowltonWeb developerAuthor Commented:
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
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
They are not all Pass Through's...only a small percentage are Pass Through's, but we are heading in that direction.
0
 
1WilliamCommented:
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
 
Tom KnowltonWeb developerAuthor Commented:
Okay.
0
 
1WilliamCommented:
'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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 12
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now