Create ODBC with a macro and modify macro based on current PC's settings.

Hi Dooglave, in most cases a DSN is created in the system registry.
My plan is to use ODBCCONF.EXE to configure the DSN in the registry, and use the Shell statement in Excel to execute it transparent to the user. One issue here is that the validreader DSN we set up via this code could overwrite settings for an existing DSN in the users registry. Would be worthwhile if you can check users registries first to see if there is already an existing validreader DSN. You can check out the help on ODBCCONF usage with Start>Run odbcconf /?

Here is the code --- back up your registry before running this. Note this creates a system DSN, can you check to see if yours is a file or system DSN? I'm assuming it is a SQL Server DSN, but you can check the driver for validreader and make sure. Unfortunately I don't have SQL Server so I can't check this works properly. I have changed the DSN name to validreader1 in case it works and overwrites your existing settings. Your network admin might be able to give you further guidance in this area.

Sub SetupDSN()
q = """"
'Note the q generates quotation marks " required by the commandline
CommandLine = "odbcconf.exe /a {CONFIGSYSDSN " & q & "SQL Server" & q & " " & q & "DSN=validreader1|Description=TMDSQL2003|UID=reader|PWD=password|APP=Microsoft Office 2003|WSID=KINGSUNNY|Network=DBMSSOCN" & q & "}"
RetVal = Shell(CommandLine, vbMinimizedNoFocus)
End Sub

Let me know how it goes
Cheers...Terry

I'll get started on this first thing tomorrow morning.

Some quick notes:

I have verified that my database connection is NOT a file.   It is in fact a System DNS and the driver is “SQL Server”

Briefly I just tried to run the macro and received error “CONFIGSYSDNS: Unable to create a data source for the ‘SQL Server’ driver: Invalid keywords-value pairs.

I’m attempting to debug it now.

That's a good start. If you search for validreader in your registry, it should show you the keywords & values that should appear in the setting string.
Cheers...Terry

I think I could get this working except I can't figure out where all the "" go and the &s and what they are for.

It is confusing. If you were to type this in at the DOS command line or in the Run box, it would look like this:

odbcconf.exe /a {CONFIGSYSDSN "SQL Server" "DSN=validreader1|Description=TMDSQL2003|etc..."}

The & q & replaces each quotation mark so it comes through in the CommandLine string with the quotation marks not interpreted as another string delimiter.

Hope that helps
Cheers...Terry

Ok, I've been killing my self trying to get this to work. I can't.

So I tried something else.

I found out that if I put the following in a .reg file and use it to add to the registry, that's all I need.  
So now I have to get VB to do the same thing the .reg file does and I'll be all set.

   .reg file:
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\validreader1]
"Driver"="C:\\WINNT\\System32\\SQLSRV32.dll"
"Description"="TMDSQL2003"
"Server"="TMDSQL2003"
"UserID"="reader"
"Password"="password"

Couple of options:
1. Create the reg file using FileSystemObject, execute using Shell & then delete
2. Use API code to modify registry directly.
I would prefer Option 2, but the usual recommendation is to backup your registry first!
Here is the code:
---------------------------------------------------------------------------
'Add this to a new module
Option Explicit
Const ODBC_ADD_SYS_DSN = 4       'Add data source
Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
   hwndParent As Long, ByVal fRequest As Long, ByVal _
   lpszDriver As String, ByVal lpszAttributes As String) As Long

Function Build_SystemDSN(DSN_DRIVER As String, DSN_NAME As String, DSN_DESC As String, _
                        DSN_SERVER As String, DSN_UID As String, DSN_PWD As String)

   Dim ret%, Driver$, Attributes$

   Driver = DSN_DRIVER & Chr(0)
   Attributes = "DSN=" & DSN_NAME & Chr(0)
   Attributes = Attributes & "Description=" & DSN_DESC & Chr(0)
   Attributes = Attributes & "Server=" & DSN_SERVER & Chr(0)
   Attributes = Attributes & "UserID=" & DSN_UID & Chr(0)
   Attributes = Attributes & "Password=" & DSN_PWD & Chr(0)

   ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)


   'ret is equal to 1 on success and 0 if there is an error
   If ret <> 1 Then
       MsgBox "DSN Creation Failed"
   End If

End Function

Run this in the immediate window, and see what happens
? Build_SystemDSN("SQL Server","validreader1","TMDSQL2003","TMDSQL2003","reader","password")

Let me know how it goes, if the API fails, we can go the FileSystemObject route, which should work
Cheers...Terry

DSN Creation Failed

I'm trying to get my hands on the ODBC 3.0 SDK to see if there is anything else.

That did it!  Terry, thank you so much for sticking with this.


For those Subsequent readers, here is my final code:  

Sheet1:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$1" Then
            Call GetName(Target)
   End If
End Sub

Module1:
Sub GetName(Rng As Range)
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=validreader1;Description=TMDSQL2003;UID=reader;PWD=password", Destination:=Range("B1"))
        '"ODBC;DSN=validreader1;Description=TMDSQL2003;UID=reader;PWD=password;APP=Microsoft Office 2003;WSID=KINGSUNNY;Network=DBMSSOCN" _
        ', Destination:=Range("B1"))
        .CommandText = Array( _
        "SELECT invModel.name" & Chr(13) & "" & Chr(10) & "FROM Valid.dbo.invItem invItem, Valid.dbo.invModel invModel" & Chr(13) & "" & Chr(10) & "WHERE invItem.invModelId = invModel.invModelId AND ((invItem.barcode='" + Rng + "'))" _
        )
        .Name = "Query from validreader"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = True
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Module2: (Note: There is one difference between WindowsXP and Windows 2000. That's the location of the driver file, SQLSRV32.dll)
Sub SetupDSN()
Dim fso, regfile
q = """"
Set fso = CreateObject("Scripting.FileSystemObject")
Set regfile = fso.CreateTextFile("C:\SQL_DSN.REG", True)
regfile.WriteLine ("Windows Registry Editor Version 5.00")
regfile.WriteLine ("")
regfile.WriteLine ("[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\validreader1]")
regfile.WriteLine (q & "Driver" & q & "=" & q & "C:\\WINNT\\System32\\SQLSRV32.dll" & q)
regfile.WriteLine (q & "Description" & q & "=" & q & "TMDSQL2003" & q)
regfile.WriteLine (q & "Server" & q & "=" & q & "TMDSQL2003" & q)
regfile.WriteLine (q & "UserID" & q & "=" & q & "reader" & q)
regfile.WriteLine (q & "Password" & q & "=" & q & "password" & q)
regfile.Close
retval = Shell("REGEDIT /s C:\SQL_DSN.REG", vbMinimizedNoFocus)
End Sub


That's it!