Post reply

Excel ODBC Connection String

  • January 30, 2006 at 8:56 pm

    #68070

    Hi,

    We have recently moved a SQL database to a new Instance and we have users that access this database using MS Query and Excel. The problem we have is that the ODBC Connection String is saved in the Excel worksheet. 

    Is there a way of editing the worksheet and updating the connection string ?

    I have tried using Notepad but unfortunately it corrupts the worksheet and excel is unable to read it.

    Any help will be much appreciated.

    Thanks,

    Sebastian   

     

  • January 31, 2006 at 8:30 am

    #617947

    Hi,

    I'm not sure exactly what method you are using to connect..

    ...but if you are using a linked query you can edit the DSN from the "Data" menu option, then choose "Import External Data", then "Edit Query" (if that is where the query is located). Then from the Edit OLE DB Query window you can make your changes in the Connection window.

    If it's not there, it may be in code, Alt-F11 will bring up the code window so you can see if there is any code behind the worksheets or in modules.

    Hope this helps.

    Scott

  • February 1, 2006 at 6:27 am

    #618086

    Try this, too:

    Record Scott Anderson's steps (previous post) in Excel in a macro

    I found one thing very odd: the connection string must be broken into at least 2 strings. It's supposed to be an array of variants. I didn't try string data types. I altered the code created by the macro by using spreadsheet values for the server, database, table name, and SQL code. I created the connection string in 2 parts: varConnection1 and varConnection2; then converted each to variants, using CVar().

    Connection:=Array(varConnection1, varConnection2)

  • February 1, 2006 at 9:30 am

    #618142

    The SQL query string is limited to 255 characters, but can be sent as an array of much greater length.  I found the following function on one of the non-Microsoft-sponsored web sites in 1997 or so.

    'SQL query as a string can only be 255 characters.

    'This converts it to an array, which can be much larger.

    'Found on one of the non-Microsoft Web sites about Excel.

    Function StringToArray(Query As String) As Variant

     Const StrLen = 127      'Set the maximum string length for

                             'each element in the array to return

                             'to 127 characters.

     Dim NumElems As Integer

     Dim Temp() As String

                          'Divide the length of the string Query by StrLen and add

                          '1 to determine how many elements the String array Temp

                          'should contain and redimension the Temp array to contain

                          'this number of elements.

     NumElems = (Len(Query) / StrLen) + 1

     ReDim Temp(1 To NumElems) As String

                          'Build the Temp array by sequentially extracting 127

                          'segments of the Query string into each element of the

                          'Temp array.

    'Log function added by John E. Carter

     logpath$ = Application.Path & "\sqlquery.txt"

     Open logpath$ For Output As 1

     Print #1, Date$, Time$, "cldsprod.xls"

     For i = 1 To NumElems

       Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)

       Print #1, Temp(i)

     Next i

     Print #1, ""

     Close 1

                          'Set the function StringToArray to the Temp array so it

                          'can be returned to the calling procedure

     StringToArray = Temp

    End Function

  • February 1, 2006 at 5:31 pm

    #618258

    Thanks everyone for their help.

    Regards,

    Sebastian

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply