Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    OBDC Connector Created with Empty Values

    I have an Access 2007 front-end application which links to a back-end MySQL database. I now need to distribute this to a number of users and I thought I'd add code to build the required ODBC connector. Using the code below, a registry entry is being created, but only the Data Source Name is filled in, all other parameters are missing.

    stAttr = "DATABASE=" & stdb & vbCr & _
    "DESCRIPTION=" & stdesc & vbCr & _
    "PORT=" & stport & vbCr & _
    "PWD=" & stpw & vbCr & _
    "SERVER=" & stsvr & vbCr & _
    "UID=" & stuser


    DBEngine.RegisterDatabase_
    dsn:="Arvixe:civic102X", _
    driver:="MySQL ODBC 3.51 Driver", _
    Silent:=False,_
    Attributes=stAttr


    In researching this it appears my values are correct, but I noted lot of discussion in the 2006-2010 time frame regarding a RegisterDatabase bug causing the same results, but all with respect to an SQL Server connection. Does anyone know is this still a bug or am I overlooking something?

    Appreciate any assistance.
    Marty

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    I always use a straight registry write to create new ODBC connectors. I try to create the entry in HKLM\SOFTWARE\ODBC so it's available to everyone who uses the machine, but you can do it in HKCU.

    cheers, Paul

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Paul,

    Thanks for the insight, could you share a vba code snip-it on how you do that?

    Marty

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Here is an easy guide.

    Instead of using a reg file in the VBS you could do something like this.
    Code:
    Set objShell = CreateObject("WScript.Shell")
     
    strKeyPath = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    strValueName = "NewDB"
    dwValue = "SQL Server"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    
    strKeyPath = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\NewDB"
    strValueName = "Driver"
    dwValue = "C:\WINDOWS\System32\sqlsrv32.dll"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    
    strValueName = "Description"
    dwValue = "New SQL DB"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    
    strValueName = "Server"
    dwValue = "Server2"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    
    strValueName = "Database"
    dwValue = "DB_name"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    
    strValueName = "LastUser"
    dwValue = "sa"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    
    strValueName = "Trusted_Connection"
    dwValue = "Yes"
    objShell.RegWrite strKeyPath & strValueName, dwValue, "REG_SZ"
    cheers, Paul

  5. The Following 2 Users Say Thank You to Paul T For This Useful Post:

    mcowen (2012-12-27),ruirib (2012-12-24)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •