Creating ODBC connections VIA VBA

[GODZILLA]

Hello,

I am looking to build something to map ODBC connections on a few computers.

I found this:

[LEFT][COLOR=#333333][FONT=Courier New]Option Compare Database  [/FONT][/COLOR][/LEFT]
[COLOR=#333333][FONT=Courier New]Option Explicit  [/FONT][/COLOR]
 
[LEFT][COLOR=#333333][FONT=Courier New]'Const ODBC_ADD_SYS_DSN = 1      'Add a user data source  [/FONT][/COLOR][/LEFT]
[COLOR=#333333][FONT=Courier New]Const ODBC_CONFIG_SYS_DSN = 2    'Configure (edit) datasource [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Const ODBC_REMOVE_SYS_DSN = 3    'Remove data source  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Const ODBC_ADD_SYS_DSN = 4       'Add a system data source  [/FONT][/COLOR]
 
[LEFT][COLOR=#333333][FONT=Courier New]Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _  [/FONT][/COLOR][/LEFT]
[COLOR=#333333][FONT=Courier New]hwndParent As Long, ByVal fRequest As Long, ByVal _  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]lpszDriver As String, ByVal lpszAttributes As String) As Long [/FONT][/COLOR]
 
[LEFT][COLOR=#333333][FONT=Courier New]Function Build_SystemDSN()  [/FONT][/COLOR][/LEFT]
[COLOR=#333333][FONT=Courier New]Dim Driver As String  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Dim Ret As Long  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Dim Attributes As String  [/FONT][/COLOR]
 
[LEFT][COLOR=#333333][FONT=Courier New]Driver = "SQL Server"  [/FONT][/COLOR][/LEFT]
[COLOR=#333333][FONT=Courier New]'attributes are the connection information  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Attributes = "server=ANSRLTR" & Chr(0)  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Attributes = Attributes & "DSN=DSN_Temp" & Chr(0)  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Attributes = Attributes & "Database=ANSRLTR_Prod" & Chr(0)  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]'use this line if you want to use the users name and password [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]'Attributes = Attributes & "Trusted_Connection=Yes" & Chr(0)  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]'use this line if you are adding a username and password  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Attributes = Attributes & "Uid=Temp;" & Chr(0) & "Pwd=TempID" & Ch(0)  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]Ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]'ret is equal to 1 on success and 0 if there is an error  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]If Ret <> 1 Then  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]MsgBox "DSN Creation Failed"  [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier New]End If  [/FONT][/COLOR]
 
 
[LEFT][COLOR=#333333][FONT=Courier New]End Function[/FONT][/COLOR][/LEFT]

But i need to it to work for Access accdb.

Any ideas?

 

[nathansav]

try changing and looking at www.connectionstrings.com for inspiration.

 

[GODZILLA]

try changing and looking at www.connectionstrings.com for inspiration.

what a cracking site. I will have a look. Thanks

 

[Banana]

Also, I'd consider using DSN-less connection instead of trying to create a DSN.

Doug Steele's article on DSN-less connection
Improving security of ODBC connections

One more thing - why an ODBC connection to Access files? If you're doing it within Access, you will find that Access blocks you from using ODBC connection to another Access database and you'd be better off using native linking methods.

 

[GODZILLA]

Also, I'd consider using DSN-less connection instead of trying to create a DSN.

Doug Steele's article on DSN-less connection
Improving security of ODBC connections

One more thing - why an ODBC connection to Access files? If you're doing it within Access, you will find that Access blocks you from using ODBC connection to another Access database and you'd be better off using native linking methods.

The links are for Excel to access data links.

When i open one of my workbooks on a different computer they do not like the datalinks as the odbc connections are not avalible.

My plan was to set up the links via an app installed on vaious base units.