July 10, 2004 at 3:37 pm
This is one we're setting up now. Its in a module. Its called on form open and automatically builds a System DSN.
Private Const REG_SZ = 1 'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Public Function Add_ISIR_System_DSNs()
'Open Þ Load Þ Resize Þ Activate Þ Current
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String
Dim lResult As Long
Dim hKeyHandle As Long
'Specify the DSN parameters.
DataSourceName = "DSNName"
DatabaseName = "DBName"
Description = "Describe the DSN"
DriverPath = " C:\WINDOWS\system32"
LastUser = "sa"
Server = "ServerName"
DriverName = "SQL Server"
'Create the new DSN key.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DataSourceName, hKeyHandle)
'Set the values of the new DSN key.
lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))
'Close the new DSN key.
lResult = RegCloseKey(hKeyHandle)
'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.
lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)
End Function
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
March 31, 2006 at 12:48 pm
I tried to connect the MS ACCESS to link the SQL server table using DSN-less connection since I do not like to go to different computer to set up the DNS. I think CurrentDb.TableDefs give me all the table in the MS ACCESS database.
Public Function RefreshLink()
'Used to refresh connection strings of all tables
'must have a reference to DAO 3.6 Object Library
'--------Change SERVER, DATABASE & Address for each use---------
Dim tbl As DAO.TableDef
Dim tblCnx As String
tblCnx = "DRIVER=SQL Server;SERVER=dbase-svr;" _
& "APP=Microsoft® Access;DATABASE=HRHSPhone>;" _
& "Network=dbase-svr;Address='172.16.2.15',1433;Trusted_Connection=Yes"
For Each tbl In CurrentDb.TableDefs
Debug.Print tbl.Name & "-" & tbl.Connect
If Left(tbl.Connect, 4) = "ODBC" Then
tbl.Connect = tblCnx
tbl.RefreshLink
End If
Next
End Function
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply