User DSN

[Andrew_Fryer]

Is there any way to create a User DSN ODBC connection using VBA?

Thanks

[Iouri]

I know how to create system DSN. Never did user DSN. If you are interested let me know

Iouri Boutchkine
iouri@hotsheet.com

[Andrew_Fryer]

Yes, that would be great thanks.

Andrew

[Iouri]

CREATE AN ODBC ENTRY
You can create, edit, or delete your program's ODBC entry automatically. This code checks if an ODBC
source has been made; if not, it configures one according to your program's specification.
To edit or remove an existing ODBC source, change the fRequest parameter to the ODBC_CONFIG_DSN or
ODBC_REMOVE_DSN values, respectively:

'ODBC_REMOVE_SYS_DSN = 6&- for sys DSN

Declare Function SQLConfigDataSource Lib _
"ODBCCP32.DLL" (ByVal hwndParent As Long, _
ByVal fRequest As Long, ByVal lpszDriver _
As String, ByVal lpszAttributes As String) _
As Long
Public Sub MakeODBCDataSource()
Const ODBC_ADD_DSN = 1
' Add data source
Const ODBC_CONFIG_DSN = 2
' Configure (edit) data source
Const ODBC_REMOVE_DSN = 3
' Remove data source
Const vbAPINull As Long = 0&
' NULL Pointer
Dim lngRet
' Check if it has been done, only do this once
If GetSetting(App.ExeName, "options", _
"ODBCSetup", "No") = "No" Then
Dim sDriver As String
Dim sAttributes As String
sDriver = "Microsoft Access Driver (*.mdb)"
sAttributes = sAttributes & "DSN=MyDSN" & _
Chr$(0)
sAttributes = sAttributes & _
"DBQ=C:\Temp\Myfile.mdb" & Chr$(0)
lngRet = SQLConfigDataSource(vbAPINull, _
ODBC_ADD_DSN, sDriver, sAttributes)
SaveSetting App.ExeName, "options", _
"ODBCSetup", "Yes"
End if
End Sub


Iouri Boutchkine
iouri@hotsheet.com

[Iouri]

Here couple more examples

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

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
------------------

Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String
sDriver = "Oracle73 Ver 2.5"
sAttributes = "Server=pressdb.world" & Chr$(0)
sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DATABASE=DB" & Chr$(0)
sAttributes = sAttributes & "UID=ABC" & Chr$(0)
sAttributes = sAttributes & "PWD=myPassword" & Chr$(0)
DBEngine.RegisterDatabase "kiki", "Oracle73 Ver 2.5", True, sAttributes
nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes)
End Sub

Public Sub DeleteDSN(sDSN As String)
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String
sDriver = "Oracle73 Ver 2.5"
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
nRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, sDriver, sAttributes)
End Sub

'===another example========
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

'Create ODBC System Data Source
Dim lngRet As Long
Dim strAttributes As String

'Set the detail of DSN
strAttributes = "Dsn=eConstructor" & Chr$(0) & "Server=(Local)" & Chr$(0) & "Description=e-Constructor Online Shop Server" & Chr$(0) & "Database=eConstructor" & Chr$(0) & "Trusted_Connection=Yes"

'Create DSN
'0& = Null, 4 = Add a System DataSource
lngRet = SQLConfigDataSource(0&, 4, "SQL Server", strAttributes)

If lngRet = 0 Then
MsgBox Err.Description
End If



Iouri Boutchkine
iouri@hotsheet.com

[Andrew_Fryer]

Hello,

VBA doesn't have the App keyword or several of the other functions. Is there any way around that?

[Cimperiali]

if you're using excel and you've saved the cartel, you can use:
MsgBox ThisWorkbook.FullName
which will give you path and name to saved sheet.
If you have not saved it, you may use
Application.Path
which will give you path to folder where excel is installed
If you're using some other office program, let us know...


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Michael
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[Andrew_Fryer]

It is actually Access I am using.

Thanks for your help

Andrew

[Cimperiali]

Debug.Print Application.CurrentDb.Name

Application.CurrentDb.Name will give you name and path to current db if it is saved.


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Michael
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[Andrew_Fryer]

Hello,

Sorry - I think I have been confusing everyone here. I will try and explain in more detail what I am trying to do:

I have an application which needs to connect to two remote databases, each of which has a different IP, i.e

Db1 = 12.46.19.212, db instance = inst1
Db2 = 96.112.19.17, db instance = inst2

Normally, if it were just the two databases then I would set up a DSN so that I could link the tables via access. However there are several databases and I do not wish to set up the DSN for each machine it is installed on. What I wish to do is to create the DSN's the first time the program is ran, therefore meaning that each machine will have the correct one.

Any ideas would be appreciated. Most of the VB stuff does not seem to work in VB for Access.

Thanks

Andrew