473,721 Members | 1,869 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating a deleting a DSN in code

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.
Nov 13 '05 #1
6 5460
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.

Nov 13 '05 #2
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
Nov 13 '05 #3
"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!)

Nov 13 '05 #4
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.
Nov 13 '05 #5
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

Nov 13 '05 #6
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.
Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
6665
by: John Baker | last post by:
Hi: I would like to know how to create a temp DB to store the data in a table while I do something else with the table. Specifically, how do I create the temp remove the temp I want to be certain that these are not linked or anything.
3
2106
by: Keith Smith | last post by:
Are there any alternatives to sharing a database without creating a shared folder? I guess I could create a client/server app, but I'm trying to avoid that. On the other hand, I am also trying to avoid someone deleting the share to my database folder. Any ideas?
3
2994
by: kris.dorey | last post by:
Hi, Ive got the following code which seems ok but when the user runs the function for a second time I get an error message stating that the mdb is in use by another process. There is still an ldb for the life of the application even after calling oldebconnection.close and gc.collect. Any ideas?
9
3053
by: Maziar Aflatoun | last post by:
Hi everyone, I like to export some data from the database and allow my users to store this file to their harddrive as a .txt file. Does anyone know how I would go about doing that? (without creating the actual physical text file on my local drive first). Thank you Maziar A.
5
3500
by: Sam777 | last post by:
I was under the impression that creating the app_offline.htm file at the root of the webapp would cause all handles to be closed so that the app could be removed. Unfortunately, this isn't the case. One handle remains open. Debugging shows that it's actually the IIS cache and not ASP.NET that owns this handle. During IIS shutdown, the handle is closed with the following stack trace: ChildEBP RetAddr 0006fbe4 5a403e05...
5
15326
by: Brian McClellan | last post by:
Just wondering if anyone has a simple example of creating a gridview completely programmatically, i'm not doing anything terribly sophisticated. When creating the gridview declaratively evertying works fine, however programmatically, while the grid will display data that exsists in the database, any operation on the data ( editing/updating/deleting ) seems to cause a rowdeleting/updating etc error. Or is this simply not meant to be done?
3
1701
by: sklett | last post by:
I have several objects that I'm saving to disk via the XmlSerializer. 80% of the time this works fine, but occasionaly it will create a file like this: <?xml version="1.0"?> <SystemOptions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <AssetDirectory>C:\Documents and Settings\sklett\Local Settings\Temp\</AssetDirectory> <ProductLabelPrinter>Adobe PDF</ProductLabelPrinter>...
3
2282
by: Bartholomew Simpson | last post by:
I am writing some C++ wrappers around some legacy C ones - more specifically, I am providing ctors, dtors and assignment operators for the C structs. I have a ton of existing C code that uses these structs. A typical usage case will be as ff (note the code below is Pseudocode and WILL NOT compile) //example structs (I have left out the ctors/dtors etc for brevity sake) struct MyStructA
8
2536
by: Andrus | last post by:
Code below causes error in class definition line .....Isolator<T>' does not implement interface member 'System.Collections.IEnumerable.GetEnumerator()'. 'Myapp.Isolator<T>.GetEnumerator()' cannot implement 'System.Collections.IEnumerable.GetEnumerator()' because it does not have the matching return type of 'System.Collections.IEnumerator'. I tried to change method signature to
0
8834
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9365
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9127
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9056
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4483
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2569
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.