[AccessD] DSNs

Jim Dettman jimdettman at verizon.net
Wed Jul 18 12:27:13 CDT 2007


 <<I build a new database for each order, thus the reason for editing the
DSN>>

  Well scratch that one then.

<<In a text editor I assume?>>

  No, with code.  I've pasted in the VB6 code I use below.  Will work fine
in Access.  This code uses a table however to get the DSN specifics.  You'd
need to modify it a bit to pull in the database name.  However, from what
you've said, I would go DSN less.

<<I didn't state it, but these are for linking tables out of the SQL Server
database. >>

  Basically a DSN is nothing more then a holding place for connection info.
You can supply that info right in the .Connect property and skip the DSN.

  See this MSDN article:

http://msdn2.microsoft.com/en-us/library/bb188204.aspx 

  and look for "Setting Connection Properties"

  There are loads of examples of doing this on the web.

  A good site for getting connection strings is:

http://www.connectionstrings.com/


Jim.

============================================================================
==
Option Explicit           ' Require variables to be declared before being
used.

Private Const ODBC_ADD_SYS_DSN = 4       'Add data source
Private Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source
Private Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source

Private Const vbAPINull = 0&

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
   hwndParent As Long, ByVal fRequest As Long, ByVal _
   lpszDriver As String, ByVal lpszAttributes As String) As Long
   
Function CreateDSNs(strDatabaseName As String) As Integer

   ' Using tblDSNs, Create/refresh required DSN entires for a database.

   Dim ws As DAO.Workspace
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   
   Dim strDriver As String
   Dim strAttributes As String
   Dim strDatabase As String
   Dim strDSN As String
   Dim intEntryCount As Integer
   Dim intNumberofDSNs As Integer
   Dim varRet As Variant
   
   'Dim pb As New Form_frm_ProgBar

   On Error goto CreateDSNs_Err
   
    Set ws = DBEngine.CreateWorkspace("", "Admin", "")
    Set db = ws.OpenDatabase("P:\xxx\SetClientEnv\SetClientEnv.MDB")
   Set rs = db.OpenRecordset("tblDSNs")
   intNumberofDSNs = rs.RecordCount
   intEntryCount = 0
   'pb.SetMessage "Creating/refreshing DSN Entries"
   
   With rs
      While Not .EOF
        ' Check if this entry applies to this database.
        If UCase(rs("DatabaseName")) = UCase(strDatabaseName) Then
             ' Register method can't create system DSNs
             ' only user ones.
             'DBEngine.RegisterDatabase rs("DSN"), _
             '         "SQL Server", _
             '         True, _
             '         "Description= Traverse - " & strDatabase & _
             '         Chr(13) & "Server=" & rs("Server") & _
             '         Chr(13) & "Database=" & strDatabase & _
             '         Chr(13) & "Network=DBMSSOCN" & _
             '         Chr(13) & "Trusted_Connection=Yes"
    
            strDriver = "SQL Server" & Chr(0)
            strAttributes = "DSN=" & rs("DSN") & Chr(0)
            strAttributes = strAttributes & "Description= Traverse - " &
rs("Database") & Chr(0)
            strAttributes = strAttributes & "Server=" & rs("Server") &
Chr(0)
            strAttributes = strAttributes & "Database=" & rs("Database") &
Chr(0)
            strAttributes = strAttributes & "Network=DBMSSOCN" & Chr(0)
            strAttributes = strAttributes & "Trusted_Connection=Yes" &
Chr(0)
       
            varRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN,
strDriver, strAttributes)
       
            If varRet <> 1 Then
              MsgBox "DSN Creation Failed"
              GoTo CreateDSNs_Err
            End If
        End If
        
        intEntryCount = intEntryCount + 1
        'pb.SetBarPercent (intEntryCount / intNumberofDSNs) * 100
        
        rs.MoveNext
      Wend
   End With
   
   CreateDSNs = True
   
CreateDSNs_End:
   On Error Resume Next
   
   If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
   End If
   
    If Not db Is Nothing Then
        db.Close
        Set db = Nothing
    End If
    
    If Not ws Is Nothing Then
        ws.Close
        Set ws = Nothing
    End If
   
   Exit Function

   
CreateDSNs_Err:
   CreateDSNs = False
   GoTo CreateDSNs_End

End Function



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, July 18, 2007 12:50 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] DSNs

>1. Have multiple DSN's defined and switch the .Connect property to point to
the correct one.

I build a new database for each order, thus the reason for editing the DSN

>2. Redefine a single DSN.

In a text editor I assume?

>3. Provide all the connection information in the .Connect property and go
DSN less.

I didn't state it, but these are for linking tables out of the SQL Server
database. 

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Wednesday, July 18, 2007 12:42 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] DSNs

John,

1. Have multiple DSN's defined and switch the .Connect property to point to
the correct one.
2. Redefine a single DSN.
3. Provide all the connection information in the .Connect property and go
DSN less.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, July 18, 2007 10:24 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] DSNs

I am working with Access as the FE to processing orders where I need to
export to a fixed width file because Access stores the format, which is
convenient until I come up to speed on how to do something similar in SQL
Server.  The issue is that the DSN is specific to a database name.  How can
I edit that to point to a different database?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list