Creating a deleting a DSN in code

[mathilda]

Help! I've been tasked to create a DSN in code and then delete it on
close. For security we do not want to leave it in place outside of
the application we are implementing.

[Douglas J. Steele]

Rather than going to that bother, why not make DSN-less connections?

Check http://members.rogers.com/douglas.j....LessLinks.html for one
approach.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"mathilda" <sm***********@yahoo.com> wrote in message
news:ff********************************@4ax.com...

Help! I've been tasked to create a DSN in code and then delete it on
close. For security we do not want to leave it in place outside of
the application we are implementing.

[Tim Marshall]

mathilda wrote:

Help! I've been tasked to create a DSN in code and then delete it on
close. For security we do not want to leave it in place outside of
the application we are implementing.

What is your platform and back end?

Doug's solution is something I am eager to explore now that I am in
A2003, but it would not work for Oracle in A97 and DAO.

Another possiblity is creating a user in the back end database (Oracle,
and I assume the same can be done for MS SQL, but I don't know for sure)
for which only grant select has been given to specific tables. That's
what I do for my oracle database, giving my users read only (no create
or delete) access to the tables they need.

The latter solution is, of course, no good if you want them to be able
to write to your data.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Douglas J. Steele]

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cj**********@coranto.ucs.mun.ca...

mathilda wrote:

Help! I've been tasked to create a DSN in code and then delete it on
close. For security we do not want to leave it in place outside of
the application we are implementing.

What is your platform and back end?

Doug's solution is something I am eager to explore now that I am in
A2003, but it would not work for Oracle in A97 and DAO.

Another possiblity is creating a user in the back end database (Oracle,
and I assume the same can be done for MS SQL, but I don't know for sure)
for which only grant select has been given to specific tables. That's
what I do for my oracle database, giving my users read only (no create
or delete) access to the tables they need.

The latter solution is, of course, no good if you want them to be able
to write to your data.

We've used it successfully against Oracle (don't remember which version)
from Access 97.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[mathilda]

On Tue, 05 Oct 2004 13:23:47 -0230, Tim Marshall
<TI****@antarctic.flowerpots> wrote:

mathilda wrote:

Help! I've been tasked to create a DSN in code and then delete it on
close. For security we do not want to leave it in place outside of
the application we are implementing.

What is your platform and back end?

Doug's solution is something I am eager to explore now that I am in
A2003, but it would not work for Oracle in A97 and DAO.

Another possiblity is creating a user in the back end database (Oracle,
and I assume the same can be done for MS SQL, but I don't know for sure)
for which only grant select has been given to specific tables. That's
what I do for my oracle database, giving my users read only (no create
or delete) access to the tables they need.

The latter solution is, of course, no good if you want them to be able
to write to your data.

I'm ussing an Access 2000 front end with a fourth shift (i.e. sql
server 2000) back end. The boss has already set up DSNs on several
peoples machines before we were made aware of the security faux paux.

[JoeBlo]

Try this :
'Start Code
Option Compare Database
Option Explicit

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
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal
fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$,
ByVal cbDescriptionMax%, pcbDescription%) As Integer
Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1

'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0& ' NULL Pointer

Sub MakeODBC(ProgPath As String, _
DataPath As String, _
UserName As String, _
PASSWORD As String, _
ServerODBC As String, _
NameDNS As String)

On Error GoTo errDNS
'Usage : MakeODBC "c:\Acomba","c:\f1000.dta\demo","Supervisor","demo "

Dim strConnect As String
Dim strAttributes As String

strAttributes = "SERVER=" & ServerODBC & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
strAttributes = strAttributes & "DSN=" & NameDNS & Chr$(0)
strAttributes = strAttributes & "AcombaExe=" & ProgPath & Chr$(0)
strAttributes = strAttributes & "DBQ=" & DataPath & Chr$(0)
strAttributes = strAttributes & "UID=" & UserName & Chr$(0)
strAttributes = strAttributes & "PWD=" & PASSWORD & Chr$(0)

DBEngine.RegisterDatabase "Acomba", "Acomba ODBC Driver", True,
strAttributes

MsgBox "Création du DNS avec succès !", vbInformation, "Création de DNS"
Exit Sub
errDNS:
MsgBox "Erreur dans la création du DNS !", vbExclamation, "Création de
DNS"

End Sub

Public Function GetDSNsAndDrivers(ModeRetour As Integer) As String
Dim i As Integer
Dim sDSNItem As String * 1024
Dim sDRVItem As String * 1024
Dim sDSN As String
Dim sDRV As String
Dim iDSNLen As Integer
Dim iDRVLen As Integer
Dim lHenv As Long 'handle to the environment
Dim retvalue As String

On Error Resume Next

If ModeRetour = 1 Then
CurrentDb.Execute "DELETE TMP_ODBC.* FROM TMP_ODBC"
Else
CurrentDb.Execute "DELETE TMP_ODBCServeur.* FROM TMP_ODBCServeur"
End If
'get the DSNs
If SQLAllocEnv(lHenv) <> -1 Then
Do Until i <> SQL_SUCCESS
sDSNItem = Space$(1024)
sDRVItem = Space$(1024)
i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024,
iDSNLen, sDRVItem, 1024, iDRVLen)
sDSN = Left$(sDSNItem, iDSNLen)
sDRV = Left$(sDRVItem, iDRVLen)

If sDSN <> Space(iDSNLen) Then
If ModeRetour = 1 Then
CurrentDb.Execute "INSERT INTO TMP_ODBC ( Description )
SELECT '" & sDRV & "' AS ODBC"
Else
CurrentDb.Execute "INSERT INTO TMP_ODBCServeur (
Description ) SELECT '" & sDSN & "' AS ODBC"
End If
End If
Loop
End If
End Function

Public Sub DeleteDNS(ServerODBC As String, NameDNS As String)
'Supprimer un DNS
'Usage : DeleteDNS("Acomba ODBC Driver", "Acomba")
Dim intRet As Long
Dim strDriver As String
Dim strAttributes As String

strDriver = ServerODBC
strAttributes = "DSN=" & NameDNS & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, strDriver,
strAttributes)
If intRet Then
MsgBox "Suppression du DNS réussi avec succès !", vbInformation,
"Suppression DNS"
Else
MsgBox "Impossible de supprimer le DNS", vbInformation,
"Suppression DNS"
End If

End Sub

'Ré-attache tout les tables d'une base de donnée ODBC
Public Sub MakeLinkODBC(ServerName As String, _
DNSName As String, _
DatabaseName As String, _
UserName As String, _
UserPass As String)

On Error GoTo errODBC
Dim SQLDb As DAO.Database
Dim dbLocal As DAO.Workspace
Dim i As Long
Dim TblName As String
Dim tdfCurrent As DAO.TableDef

Set dbLocal = DBEngine.Workspaces(0)
Set SQLDb = dbLocal.OpenDatabase("", False, False, "ODBC;DATABASE=" &
DatabaseName & _
";UID=" & UserName & _
";PWD=" & UserPass & _
";DSN=" & DNSName & _
";SERVER=" & ServerName &
"")

For i = 0 To SQLDb.TableDefs.Count - 1
TblName = SQLDb.TableDefs(i).Name
If Left(TblName, 4) = "dbo." And Left(TblName, 1) <> "~" And Left(TblName,
3) <> "sys" Then
TblName = Right(TblName, Len(TblName) - 4)
If TblName <> CurrentDb.TableDefs(i).Name Then
CurrentDb.TableDefs.Delete TblName
End If

Set tdfCurrent = CurrentDb.CreateTableDef(TblName)
tdfCurrent.Connect = "ODBC;DATABASE=" & DatabaseName & ";UID=" &
UserName & ";PWD=" & UserPass & ";DSN=" & DNSName

tdfCurrent.SourceTableName = TblName
CurrentDb.TableDefs.Append tdfCurrent
RefreshDatabaseWindow
End If
Next i

SQLDb.Close
dbLocal.Close

Exit Sub

errODBC:
If Err.Number = 3010 Then 'Objet existe déjà
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "SQL Serveur"
Resume Next
End If

End Sub
'// End code
Robert Simard
Logipro

[mathilda]

On Wed, 6 Oct 2004 09:16:12 -0400, "JoeBlo"
<lo******************@hotmail.com> wrote:

'Start Code
Option Compare Database
Option Explicit

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
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal
fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$,
ByVal cbDescriptionMax%, pcbDescription%) As Integer
Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1

'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0& ' NULL Pointer

Sub MakeODBC(ProgPath As String, _
DataPath As String, _
UserName As String, _
PASSWORD As String, _
ServerODBC As String, _
NameDNS As String)

On Error GoTo errDNS
'Usage : MakeODBC "c:\Acomba","c:\f1000.dta\demo","Supervisor","demo "

Dim strConnect As String
Dim strAttributes As String

strAttributes = "SERVER=" & ServerODBC & Chr$(0)
strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
strAttributes = strAttributes & "DSN=" & NameDNS & Chr$(0)
strAttributes = strAttributes & "AcombaExe=" & ProgPath & Chr$(0)
strAttributes = strAttributes & "DBQ=" & DataPath & Chr$(0)
strAttributes = strAttributes & "UID=" & UserName & Chr$(0)
strAttributes = strAttributes & "PWD=" & PASSWORD & Chr$(0)

DBEngine.RegisterDatabase "Acomba", "Acomba ODBC Driver", True,
strAttributes

MsgBox "Création du DNS avec succès !", vbInformation, "Création de DNS"
Exit Sub
errDNS:
MsgBox "Erreur dans la création du DNS !", vbExclamation, "Création de
DNS"

End Sub

Public Function GetDSNsAndDrivers(ModeRetour As Integer) As String
Dim i As Integer
Dim sDSNItem As String * 1024
Dim sDRVItem As String * 1024
Dim sDSN As String
Dim sDRV As String
Dim iDSNLen As Integer
Dim iDRVLen As Integer
Dim lHenv As Long 'handle to the environment
Dim retvalue As String

On Error Resume Next

If ModeRetour = 1 Then
CurrentDb.Execute "DELETE TMP_ODBC.* FROM TMP_ODBC"
Else
CurrentDb.Execute "DELETE TMP_ODBCServeur.* FROM TMP_ODBCServeur"
End If
'get the DSNs
If SQLAllocEnv(lHenv) <> -1 Then
Do Until i <> SQL_SUCCESS
sDSNItem = Space$(1024)
sDRVItem = Space$(1024)
i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024,
iDSNLen, sDRVItem, 1024, iDRVLen)
sDSN = Left$(sDSNItem, iDSNLen)
sDRV = Left$(sDRVItem, iDRVLen)

If sDSN <> Space(iDSNLen) Then
If ModeRetour = 1 Then
CurrentDb.Execute "INSERT INTO TMP_ODBC ( Description )
SELECT '" & sDRV & "' AS ODBC"
Else
CurrentDb.Execute "INSERT INTO TMP_ODBCServeur (
Description ) SELECT '" & sDSN & "' AS ODBC"
End If
End If
Loop
End If
End Function

Public Sub DeleteDNS(ServerODBC As String, NameDNS As String)
'Supprimer un DNS
'Usage : DeleteDNS("Acomba ODBC Driver", "Acomba")
Dim intRet As Long
Dim strDriver As String
Dim strAttributes As String

strDriver = ServerODBC
strAttributes = "DSN=" & NameDNS & Chr$(0)
'To show dialog, use Form1.Hwnd instead of vbAPINull.
intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, strDriver,
strAttributes)
If intRet Then
MsgBox "Suppression du DNS réussi avec succès !", vbInformation,
"Suppression DNS"
Else
MsgBox "Impossible de supprimer le DNS", vbInformation,
"Suppression DNS"
End If

End Sub

'Ré-attache tout les tables d'une base de donnée ODBC
Public Sub MakeLinkODBC(ServerName As String, _
DNSName As String, _
DatabaseName As String, _
UserName As String, _
UserPass As String)

On Error GoTo errODBC
Dim SQLDb As DAO.Database
Dim dbLocal As DAO.Workspace
Dim i As Long
Dim TblName As String
Dim tdfCurrent As DAO.TableDef

Set dbLocal = DBEngine.Workspaces(0)
Set SQLDb = dbLocal.OpenDatabase("", False, False, "ODBC;DATABASE=" &
DatabaseName & _
";UID=" & UserName & _
";PWD=" & UserPass & _
";DSN=" & DNSName & _
";SERVER=" & ServerName &
"")

For i = 0 To SQLDb.TableDefs.Count - 1
TblName = SQLDb.TableDefs(i).Name
If Left(TblName, 4) = "dbo." And Left(TblName, 1) <> "~" And Left(TblName,
3) <> "sys" Then
TblName = Right(TblName, Len(TblName) - 4)
If TblName <> CurrentDb.TableDefs(i).Name Then
CurrentDb.TableDefs.Delete TblName
End If

Set tdfCurrent = CurrentDb.CreateTableDef(TblName)
tdfCurrent.Connect = "ODBC;DATABASE=" & DatabaseName & ";UID=" &
UserName & ";PWD=" & UserPass & ";DSN=" & DNSName

tdfCurrent.SourceTableName = TblName
CurrentDb.TableDefs.Append tdfCurrent
RefreshDatabaseWindow
End If
Next i

SQLDb.Close
dbLocal.Close

Exit Sub

errODBC:
If Err.Number = 3010 Then 'Objet existe déjà
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "SQL Serveur"
Resume Next
End If

End Sub
'// End code

Sweet!

Thanks, that did the trick.