File share MS Access & ODBC to SQL Server

• Jim P.

  SSCrazy Eights

  Points: 8725

  More actions

  July 10, 2004 at 3:37 pm

  #514203

  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.

• Frances L

  SSCarpal Tunnel

  Points: 4727

  More actions

  March 31, 2006 at 12:48 pm

  #630223

  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