Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change and ODBC Connection
I have a 514 spreadsheets set up pointing to one database via an ODBC
and now I need to point them the in a different direction, same query in the background , any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change and ODBC Connection
Hi,
You can refresh your query by code. Dim strWhat As String Dim StrFrom As String Dim strWhere As String Dim strOrder As String Dim strSql As String Dim varSql As Variant Dim wk as Worksheet strWhat = "SELECT *" StrFrom = "FROM yourdata" strWhere = "WHERE your condition " strOrder = "ORDER BY your column" strSql = strWhat & " " & StrFrom & " " & strWhere & " " & strOrder varSql = StringToArray(strSql) for each wk in worksheets wk.activate With Range("A2").QueryTable .Connection = _ "OLEDB;Provider=MSDAORA.1;Password=MyPW;User ID=MyID;Data Source=MyDataSource" .CommandType = xlCmdSql .CommandText = varSql .Refresh BackgroundQuery:=False End With next wk End Sub 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 Dim i ' 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. For i = 1 To NumElems Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen) Next i ' Set the function StringToArray to the Temp array so it ' can be returned to the calling procedure. StringToArray = Temp End Function RegaRDS jy wrote in message ups.com... I have a 514 spreadsheets set up pointing to one database via an ODBC and now I need to point them the in a different direction, same query in the background , any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC connection failed. | Excel Discussion (Misc queries) | |||
ODBC Connection | Excel Programming | |||
How do I change my DSN without recreating the odbc connection? | Excel Worksheet Functions | |||
ODBC connection by udl | Excel Programming |