Creating a System DSN in code 1

[storm75m]

I have some code to create a SQL Server DSN, but it creates a user DSN and I would like to create a system DSN so that it works for any user that logs into the computer. Here is my code:

str = "Description=SQL Server " & vbCr & _
"Network=DBMSSOCN" & vbCr & _
"Trusted_Connection=yes" & vbCr & _
"Address=PARKTENSQL" & vbCr & _
"Server=PARKTENSQL" & vbCr & _
"Database=FieldTimeSystem"
DBEngine.RegisterDatabase "SQL_FTS", "SQL Server", True, str

Any help would be much appreciated, thanks in advance!

 

[nonot]

Call this procedure from a command button :

Sub RegisterDatabaseX()

Dim dbsRegister As Database
Dim strDescription As String
Dim strAttributes As String
Dim errLoop As Error

Dim WshShell As Object

' Build keywords string.
strDescription = InputBox("Enter a description " & _
"for the database to be registered.&quot
strAttributes = "Database=pubs" & _
vbCr & "Description=" & strDescription & _
vbCr & "OemToAnsi=No" & _
vbCr & "Server=VODKA"

' Update Windows Registry.
On Error GoTo Err_Register
DBEngine.RegisterDatabase "Publishers", "SQL Server", _
True, strAttributes
On Error GoTo 0

Set WshShell = CreateObject("WScript.Shell&quot
WshShell.RegWrite "HKCU\Software\ODBC\ODBC.INI\Publishers\LastUser", "sa"

MsgBox "Use regedit.exe to view changes: HKEY_CURRENT_USER\Software\ODBC\ODBC.INI"

Exit Sub

Err_Register:

' Notify user of any errors that result from
' the invalid data.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
End If

Resume Next

End Sub


Hope this helps.

 

[FancyPrairie]

I found that File DSN's are much easier to manage. You create and use it just like a system or user dsn. However, you can place the DSN file on a server where everyone has access to it. Therefore, you don't have to create a DSN on every machine you put your db on.