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.c om...

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****@antarct ic.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****@antarct ic.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 SQLConfigDataSo urce Lib "ODBCCP32.D LL" (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 cbDescriptionMa x%, 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(ProgPa th 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=Te mp 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.Regist erDatabase "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 GetDSNsAndDrive rs(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.Execu te "DELETE TMP_ODBC.* FROM TMP_ODBC"
Else
CurrentDb.Execu te "DELETE TMP_ODBCServeur .* FROM TMP_ODBCServeur "
End If
'get the DSNs
If SQLAllocEnv(lHe nv) <> -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.Execu te "INSERT INTO TMP_ODBC ( Description )
SELECT '" & sDRV & "' AS ODBC"
Else
CurrentDb.Execu te "INSERT INTO TMP_ODBCServeur (
Description ) SELECT '" & sDSN & "' AS ODBC"
End If
End If
Loop
End If
End Function

Public Sub DeleteDNS(Serve rODBC As String, NameDNS As String)
'Supprimer un DNS
'Usage : DeleteDNS("Acom ba 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 = SQLConfigDataSo urce(vbAPINull, ODBC_REMOVE_DSN , strDriver,
strAttributes)
If intRet Then
MsgBox "Suppressio n du DNS réussi avec succès !", vbInformation,
"Suppressio n DNS"
Else
MsgBox "Impossible de supprimer le DNS", vbInformation,
"Suppressio n DNS"
End If

End Sub

'Ré-attache tout les tables d'une base de donnée ODBC
Public Sub MakeLinkODBC(Se rverName 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.Worksp aces(0)
Set SQLDb = dbLocal.OpenDat abase("", 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.Table Defs(i).Name Then
CurrentDb.Table Defs.Delete TblName
End If

Set tdfCurrent = CurrentDb.Creat eTableDef(TblNa me)
tdfCurrent.Conn ect = "ODBC;DATABASE= " & DatabaseName & ";UID=" &
UserName & ";PWD=" & UserPass & ";DSN=" & DNSName

tdfCurrent.Sour ceTableName = TblName
CurrentDb.Table Defs.Append tdfCurrent
RefreshDatabase Window
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 SQLConfigDataSo urce Lib "ODBCCP32.D LL" (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 cbDescriptionMa x%, 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(ProgPa th 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=Te mp 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.Regist erDatabase "Acomba", "Acomba ODBC Driver", True,
strAttribute s

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 GetDSNsAndDrive rs(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.Execu te "DELETE TMP_ODBC.* FROM TMP_ODBC"
Else
CurrentDb.Execu te "DELETE TMP_ODBCServeur .* FROM TMP_ODBCServeur "
End If
'get the DSNs
If SQLAllocEnv(lHe nv) <> -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.Execu te "INSERT INTO TMP_ODBC ( Description )
SELECT '" & sDRV & "' AS ODBC"
Else
CurrentDb.Execu te "INSERT INTO TMP_ODBCServeur (
Description ) SELECT '" & sDSN & "' AS ODBC"
End If
End If
Loop
End If
End Function

Public Sub DeleteDNS(Serve rODBC As String, NameDNS As String)
'Supprimer un DNS
'Usage : DeleteDNS("Acom ba 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 = SQLConfigDataSo urce(vbAPINull, ODBC_REMOVE_DSN , strDriver,
strAttribute s)
If intRet Then
MsgBox "Suppressio n du DNS réussi avec succès !", vbInformation,
"Suppressio n DNS"
Else
MsgBox "Impossible de supprimer le DNS", vbInformation,
"Suppressio n DNS"
End If

End Sub

'Ré-attache tout les tables d'une base de donnée ODBC
Public Sub MakeLinkODBC(Se rverName 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.Worksp aces(0)
Set SQLDb = dbLocal.OpenDat abase("", 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.Table Defs(i).Name Then
CurrentDb.Table Defs.Delete TblName
End If

Set tdfCurrent = CurrentDb.Creat eTableDef(TblNa me)
tdfCurrent.Conn ect = "ODBC;DATABASE= " & DatabaseName & ";UID=" &
UserName & ";PWD=" & UserPass & ";DSN=" & DNSName

tdfCurrent.Sour ceTableName = TblName
CurrentDb.Table Defs.Append tdfCurrent
RefreshDatabase Window
End If
Next i

SQLDb.Close
dbLocal.Clos e

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.