Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-23-2006, 02:58 AM   #1
Moonshine
Registered User
 
Moonshine's Avatar
 
Join Date: Jan 2003
Location: England
Posts: 125
Thanks: 0
Thanked 0 Times in 0 Posts
Moonshine is on a distinguished road
ODBC Connection Via VBA

Hi All

Im having problems using a bit of code i found, i think from here... im trying to create an ODBC connection upon start up if it doesnt already exist.

I can get the connection to be created with all details apart from the password, it doesnt seem to save it! Please could anyone have a look at the code and tell me what im doing wrong?

Code:
Sub Install()


'***************************************************************
'*****
'*****  This script creates a DSN for connecting to a
'*****  SQL Server database. To view errors comment out line 16
'*****
'*****  Script Name: AutoDSN.vbs
'*****  Author: Darron Nesbitt
'*****  Depends: VBScript, WScript Host
'*****  Created: 10/2/2001
'*****
'***************************************************************

'Values for variables on lines 25 - 29, 32, and 36
'must be set prior to running this script.

    On Error Resume Next

    Dim RegObj
    Dim SysEnv
    
    Set RegObj = CreateObject("WScript.Shell")

    '***** Specify the DSN parameters *****

    DataSourceName = "PERSON_BASIC_DATA_USER"
    DatabaseName = "BBC_STAGE"
    Description = "Person basic Data User"
    Server = "BBCSSLIVESQL"
    LastUser = "PERSON_BD_USER"
    PWD = "pbduser"

     'if you use SQL Server the driver name would be "SQL Server"
    DriverName = "SQL Server"

     'Set this to True if Windows Authentication is used
     'else set to False or comment out
    WindowsAuthentication = False

    'point to DSN in registry
    REG_KEY_PATH = "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName

   ' Open the DSN key and check for Server entry
    lResult = RegObj.RegRead(REG_KEY_PATH & "\Server")

    'if lResult is nothing, DSN does not exist; create it
    If lResult = "" Then

      'get os version through WSCript Enviroment object
      Set SysEnv = RegObj.Environment("SYSTEM")
      OSVer = UCase(SysEnv("OS"))

      'check which os is running so correct driver path can be set
      Select Case OSVer
        Case "WINDOWS_NT"
            DrvrPath = "C:\WinNT\System32"
        Case Else
            DrvrPath = "C:\Windows\System"
      End Select

      'create entries in registry
      RegObj.RegWrite REG_KEY_PATH & "\DataBase", DatabaseName, "REG_SZ"
      RegObj.RegWrite REG_KEY_PATH & "\Description", Description, "REG_SZ"
      RegObj.RegWrite REG_KEY_PATH & "\LastUser", LastUser, "REG_SZ"
      RegObj.RegWrite REG_KEY_PATH & "\Server", Server, "REG_SZ"
      RegObj.RegWrite REG_KEY_PATH & "\Driver", DrvrPath, "REG_SZ"
      RegObj.RegWrite REG_KEY_PATH & "\PWD", PWD, "REG_SZ"

      'if WindowsAuthentication set to True,
      'a trusted connection entry is added to registry
      'else, SQL Authentication is used.
      If WindowsAuthentication = True Then
        RegObj.RegWrite REG_KEY_PATH & "\Trusted_Connection", "Yes", "REG_SZ"
      End If

      'point to data sources key
      REG_KEY_PATH = "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & DataSourceName

      'and add the name of the new dsn and the driver to use with it
      RegObj.RegWrite REG_KEY_PATH, DriverName, "REG_SZ"

      MsgBox DataSourceName & " DSN Created!"

    Else
        Exit Sub
    End If

    Set RegObj = Nothing
    Set SysEnv = Nothing
    End Sub
There are loads of variables and connections to .DLL files pervious to this, but didnt want to paste as the message would be huge!

Moonshine is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change ODBC connection in VBA stringzz Modules & VBA 2 11-24-2005 10:13 PM
Querydef ODBC connection problem jonomac Modules & VBA 0 08-09-2005 06:26 AM
Setting default ODBC connection hadwin Modules & VBA 3 08-22-2004 07:48 PM
ODBC Connection peggypph General 1 07-02-2004 03:30 AM
How to 'grab' the ODBC connection used by linked tables DanR General 5 04-13-2004 07:52 PM




All times are GMT -8. The time now is 07:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World