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