Automatic DSN Creation 1

[rpbenito]

Is there any way to automatically create a System DSN? I would like my users NOT to have to go into the control panel and set it up themselves or go around to each machine and do it myself if possible.

Thanks in advance.

 

[Scoty]

RBENITO,
This is not my code, but I use it a lot. It was origanally intended for VB but I believe it will work in access. Make a form with a command button and put this code under it:

  Dim Driver        As String
   Dim DSN           As String
   Dim Server        As String
   Dim Database      As String
   Dim Description   As String
   
   DSN = "DSN=MyDatabase"
   Database = "DBQ=c:\databases\MyDatabase.mdb"'this is your physical path to the *.mdb
   Description = "DESCRIPTION=MyDatabase"
   AccessParameter = DSN & vbNullChar & Database & vbNullChar & Description & vbNullChar & vbNullChar
   SQLConfigDataSource 0&, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)", AccessParameter

Then create a module called ODBCAPI and put this code in it:

' ODBC API
' -- ODBC Commands
Public Const ODBC_ADD_DSN = 1&
Public Const ODBC_CONFIG_DSN = 2&
Public Const ODBC_REMOVE_DSN = 3&
Public Const ODBC_ADD_SYS_DSN = 4&
Public Const ODBC_CONFIG_SYS_DSN = 5&
Public Const ODBC_REMOVE_SYS_DSN = 6&
Public Const ODBC_REMOVE_DEFAULT_DSN = 7&

' -- ODBC Error Codes
Public Const ODBC_ERROR_GENERAL_ERR = 1
Public Const ODBC_ERROR_INVALID_BUFF_LEN = 2
Public Const ODBC_ERROR_INVALID_HWND = 3
Public Const ODBC_ERROR_INVALID_STR = 4
Public Const ODBC_ERROR_INVALID_REQUEST_TYPE = 5
Public Const ODBC_ERROR_COMPONENT_NOT_FOUND = 6
Public Const ODBC_ERROR_INVALID_NAME = 7
Public Const ODBC_ERROR_INVALID_KEYWORD_VALUE = 8
Public Const ODBC_ERROR_INVALID_DSN = 9
Public Const ODBC_ERROR_INVALID_INF = 10
Public Const ODBC_ERROR_REQUEST_FAILED = 11
Public Const ODBC_ERROR_INVALID_PATH = 12
Public Const ODBC_ERROR_LOAD_LIB_FAILED = 13
Public Const ODBC_ERROR_INVALID_PARAM_SEQUENCE = 14
Public Const ODBC_ERROR_INVALID_LOG_FILE = 15
Public Const ODBC_ERROR_USER_CANCELED = 16
Public Const ODBC_ERROR_USAGE_UPDATE_FAILED = 17
Public Const ODBC_ERROR_CREATE_DSN_FAILED = 18
Public Const ODBC_ERROR_WRITING_SYSINFO_FAILED = 19
Public Const ODBC_ERROR_REMOVE_DSN_FAILED = 20
Public Const ODBC_ERROR_OUT_OF_MEM = 21
Public Const ODBC_ERROR_OUTPUT_STRING_TRUNCATED = 22

'API Command to create a Data Source Name, not used in this example
Public Declare Function SQLCreateDataSource Lib "odbccp32.dll" (ByVal hwnd&, ByVal lpszDS$) As Boolean
'API to modify/Edit/Create a Data Source Name
Public Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwnd As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Boolean

This works for me. Give it a try let us know how it worked.
AGAIN: this is not my code I don't remember who wrote it but it was a member of this forum. If you know who it is feel free to post their name so they get propper credit.

Hope this helps
Scotty :

 

[rpbenito]

How would I make this work for connectin to An oracle DB?

Thanks

 

[jes]

Hi,

Check out this page - it's a lot simpler than the stuff mentioned above( No dis, Scott )

http://www.swynk.com/friends/lesandrini/ODBCTutor02.asp

hope it helps

Jes

 

[rpbenito]

I need a DSN connection..not a DSN less connection.. thanks anyway

 

[jes]

Whoops! Sorry, I'll learn to read properly in the future.

 

[Scoty]

rbenito,
I'm not sure about oracle. I only have access experience. Good look. You may want to check out vb 5 and 6 forum. Everything they post there should work.

Good Luck
Scotty :