Testing for ODBC names

Hi all

I want to get a list of ODBC connections, and then pick one - the
point is, that the connection I am looking for might be spelled
differently (e.g. with space). So with a list I can find it eaily.

Any suggestions?
Sonnich
0
jodleren
1/26/2010 9:03:24 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
936 Views

Similar Articles

[PageSpeed] 5

The ODBC connections i a workbook are the queries you created.  the
appear in the workbook names.  So you can search through the names. 
Youcan see the named in one of two palces

1) Insert Name define
2) File Property custom


You can also find all the queries in a workbook and then look at the
conenction property.

for each sht in sheets
for each qry in sht.queries


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=173403

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
joel
1/26/2010 11:50:59 AM
On Jan 26, 1:50=A0pm, joel <joel.45e...@thecodecage.com> wrote:
> The ODBC connections i a workbook are the queries you created. =A0the
> appear in the workbook names. =A0So you can search through the names.
> Youcan see the named in one of two palces
>
> 1) Insert Name define
> 2) File Property custom
>
> You can also find all the queries in a workbook and then look at the
> conenction property.
>
> for each sht in sheets
> for each qry in sht.queries

Not exactly what I want.... I want to get a list of possible ODBC
connections, as in the ODBC settings window.
I am now trying to get that from the registry, I found an example
here, but that does not work either.

Basically, I need the value names from HKEY_LOCAL_MACHINE\SOFTWARE\ODBC
\ODBC.INI\ODBC Data Sources

Then I have what I want.

Sonnich
0
jodleren
1/26/2010 1:50:49 PM
On Jan 26, 11:03=A0am, jodleren <sonn...@hot.ee> wrote:
> Hi all
>
> I want to get a list of ODBC connections, and then pick one - the
> point is, that the connection I am looking for might be spelled
> differently (e.g. with space). So with a list I can find it eaily.
>
> Any suggestions?
> Sonnich

This almost works - it gets the amount, but data is empty. Anybody
knows why?

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias
"RegOpenKeyExA" ( _
  ByVal hKey As Long, _
  ByVal lpSubKey As String, _
  ByVal ulOptions As Long, _
  ByVal samDesired As Long, _
  phkResult As Long) As Long
Private Declare Function RegQueryInfoKey Lib "advapi32.dll" Alias
"RegQueryInfoKeyA" ( _
  ByVal hKey As Long, _
  ByVal lpClass As String, _
  lpcbClass As Long, _
  ByVal lpReserved As Long, _
  lpcSubKeys As Long, _
  lpcbMaxSubKeyLen As Long, _
  lpcbMaxClassLen As Long, _
  lpcValues As Long, _
  lpcbMaxValueNameLen As Long, _
  lpcbMaxValueLen As Long, _
  lpcbSecurityDescriptor As Long, _
  lpftLastWriteTime As Long) As Long
Private Declare Function RegEnumValue Lib "advapi32.dll" Alias
"RegEnumValueA" ( _
  ByVal hKey As Long, _
  ByVal dwIndex As Long, _
  ByVal lpValueName As String, _
  lpcbValueName As Long, _
  ByVal lpReserved As Long, _
  lpType As Long, _
  ByVal lpData As String, _
  lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As
Long) As Long
Private Declare Function RegQueryValueExString Lib "advapi32.dll"
Alias "RegQueryValueExA" ( _
  ByVal hKey As Long, _
  ByVal lpValueName As String, _
  ByVal lpReserved As Long, _
  lpType As Long, _
  ByVal lpData As String, _
  lpcbData As Long) As Long

' Registry value type definitions
Private Const REG_NONE As Long =3D 0
Private Const REG_SZ As Long =3D 1
Private Const REG_EXPAND_SZ As Long =3D 2
Private Const REG_BINARY As Long =3D 3
Private Const REG_DWORD As Long =3D 4
Private Const REG_LINK As Long =3D 6
Private Const REG_MULTI_SZ As Long =3D 7
Private Const REG_RESOURCE_LIST As Long =3D 8

Private Sub CommandButton1_Click()
  Dim mCurrentKey As Long
  Result =3D RegOpenKeyEx(&H80000002, "SOFTWARE\ODBC\ODBC.INI\ODBC Data
Sources", 0&, &H2001D, mCurrentKey)

  Dim DataType As Long
  Dim Value As String
  Dim ValueLength As Long
  Dim ReadString As String
'  Dim Result As Long
If False Then // this works
  Result =3D RegQueryValueExString(mCurrentKey, "ChinaWise", 0&,
DataType, vbNullString, ValueLength)
  If Result =3D ERROR_SUCCESS Then
    Value =3D Space(ValueLength)
    Result =3D RegQueryValueExString(mCurrentKey, "ChinaWise", 0&,
DataType, Value, ValueLength)
    If Result =3D ERROR_SUCCESS Then
      Select Case DataType
        Case REG_SZ, REG_EXPAND_SZ, REG_MULTI_SZ
          ReadString =3D Left(Value, ValueLength - 1)
        Case Else
          Err.Raise vbObjectError + 515, , "Not a string value: " &
Name
      End Select
    End If
  End If
  If Result <> ERROR_SUCCESS Then
    Err.Raise vbObjectError + 515, , "Cannot read string value: " &
Name
  End If
End If

  Dim Values()
  ReDim Preserve Values(0)

'  Dim Name As String
  Dim NameLength As Long
  Dim ValueCount As Long
  Dim MaxValueLength As Long
  Dim i As Long

  If RegQueryInfoKey(mCurrentKey, vbNullString, 0&, 0&, 0&, 0&, 0&,
ValueCount, MaxValueLength, 0&, 0&, 0&) =3D ERROR_SUCCESS Then
    If ValueCount > 0 Then
      ReDim Values(0 To ValueCount - 1)
    Else
      Values =3D Split("")
    End If
    MaxValueLength =3D MaxValueLength + 1
    sName =3D Space(MaxValueLength) // always empty - why?
    For i =3D 0 To ValueCount - 1
      NameLength =3D MaxValueLength
      If RegEnumValue(mCurrentKey, i, sName, NameLength, 0&, 0&,
vbNullString, 0&) =3D ERROR_SUCCESS Then
        Values(i) =3D Left(sName, NameLength)
      Else
        Err.Raise vbObjectError + 520, , "Error reading value name"
      End If
    Next
  Else
    Err.Raise vbObjectError + 521, , "Error reading value names"
  End If

  RegCloseKey (mCurrentKey)

  'GetValueNames (names)
  For i =3D 0 To UBound(Values)
    Cells(1 + i, 1) =3D i
    Cells(1 + i, 2) =3D Values(i)
  Next

End Sub
0
jodleren
1/26/2010 2:54:41 PM
Make sure your are using the correct registry path.  You did have the
root entry.  You registry search was using the default vbalue which I'm
not sure was the current user.

Result = RegOpenKeyEx(&H80000002,
"\HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources ", 0&,
&H2001D, mCurrentKey)


I added Current user.  First open the resitry editor and see if the
entry is in the registry


Start Button : Run regedit


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=173403

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
1/26/2010 3:11:33 PM
On Jan 26, 5:11=A0pm, joel <joel.45e...@thecodecage.com> wrote:
> Make sure your are using the correct registry path. =A0You did have the
> root entry. =A0You registry search was using the default vbalue which I'm
> not sure was the current user.
>
> Result =3D RegOpenKeyEx(&H80000002,
> "\HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources ", 0&,
> &H2001D, mCurrentKey)
>
> I added Current user. =A0First open the resitry editor and see if the
> entry is in the registry
>
> Start Button : Run regedit

That is what the first number is there for
  HKEY_CLASSES_ROOT =3D &H80000000
  HKEY_CURRENT_USER =3D &H80000001
  HKEY_LOCAL_MACHINE =3D &H80000002
  HKEY_USERS =3D &H80000003
  HKEY_PERFORMANCE_DATA =3D &H80000004
  HKEY_CURRENT_CONFIG =3D &H80000005
  HKEY_DYN_DATA =3D &H80000006

and the data is under local machine :)
0
jodleren
1/26/2010 3:42:59 PM
Maybe not exactly what you are asking for, but this code may come in useful:

Option Explicit
Private Declare Function SQLDataSources Lib "odbc32.dll" _
                                        (ByVal hEnv As Long, _
                                         ByVal fDirection As Integer, _
                                         ByVal szDSN As String, _
                                         ByVal cbDSNMax As Integer, _
                                         ByRef pcbDSN As Integer, _
                                         ByVal szDescription As String, _
                                         ByVal cbDescriptionMax As Integer, 
_
                                         ByRef pcbDescription As Integer) As 
Long
Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
                                        (ByVal HandleType As Integer, _
                                         ByVal InputHandle As Long, _
                                         ByRef OutputHandlePtr As Long) As 
Long
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
                                       (ByVal EnvironmentHandle As Long, _
                                        ByVal dwAttribute As Long, _
                                        ByVal ValuePtr As Long, _
                                        ByVal StringLen As Long) As Long
Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
                                       (ByVal HandleType As Integer, _
                                        ByVal Handle As Long) As Long
Private Const SQL_MAX_DSN_LENGTH As Long = 128
Private Const SQL_MAX_DESC_LENGTH As Long = 128
Private Const SQL_SUCCESS As Long = 0
Private Const SQL_FETCH_NEXT As Long = 1
Private Const SQL_NULL_HANDLE As Long = 0
Private Const SQL_HANDLE_ENV As Long = 1
Private Const SQL_ATTR_ODBC_VERSION As Long = 200
Private Const SQL_OV_ODBC3 As Long = 3
Private Const SQL_IS_INTEGER As Long = (-6)

Function GetDSNs(Optional strDriverPartial As String, _
                 Optional bShowDrivers As Boolean) As String()

  Dim i As Long
  Dim hEnv As Long         'handle to the environment
  Dim sServer As String
  Dim sDriver As String
  Dim nSvrLen As Integer
  Dim nDvrLen As Integer
  Dim arrDSN() As String

  Dim collDSNs As Collection
  Dim collDrivers As Collection

  Set collDSNs = New Collection
  Set collDrivers = New Collection

  strDriverPartial = UCase(strDriverPartial)

  On Error Resume Next

  'obtain a handle to the environment
  If SQLAllocHandle(SQL_HANDLE_ENV, _
                    SQL_NULL_HANDLE, _
                    hEnv) <> 0 Then

    'if successful, set the
    'environment for subsequent calls
    If SQLSetEnvAttr(hEnv, _
                     SQL_ATTR_ODBC_VERSION, _
                     SQL_OV_ODBC3, _
                     SQL_IS_INTEGER) <> 0 Then

      'set up the strings for the call
      sServer = Space$(SQL_MAX_DSN_LENGTH)
      sDriver = Space$(SQL_MAX_DESC_LENGTH)

      'load the DSN names
      Do While SQLDataSources(hEnv, _
                              SQL_FETCH_NEXT, _
                              sServer, _
                              SQL_MAX_DSN_LENGTH, _
                              nSvrLen, _
                              sDriver, _
                              SQL_MAX_DESC_LENGTH, _
                              nDvrLen) = SQL_SUCCESS

        'add data to the controls
        If Len(strDriverPartial) > 0 Then
          If InStr(1, UCase(Left$(sDriver, nDvrLen)), _
                   strDriverPartial, _
                   vbBinaryCompare) > 0 Then
            'so we avoid duplicate DSN's
            '---------------------------
            collDSNs.Add Left$(sServer, nSvrLen), Left$(sServer, nSvrLen)
            If bShowDrivers Then
              collDrivers.Add Left$(sDriver, nDvrLen)
            End If
          End If
        Else
          collDSNs.Add Left$(sServer, nSvrLen), Left$(sServer, nSvrLen)
          If bShowDrivers Then
            collDrivers.Add Left$(sDriver, nDvrLen)
          End If
        End If

        'repad the strings
        sServer = Space$(SQL_MAX_DSN_LENGTH)
        sDriver = Space$(SQL_MAX_DESC_LENGTH)

      Loop

    End If  'If SQLSetEnvAttr

    'clean up
    Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)

  End If  'If SQLAllocHandle

  If collDSNs.Count > 0 Then

    If bShowDrivers Then
      'so in this case return a 2-D array
      '----------------------------------
      ReDim arrDSN(1 To collDSNs.Count, 1 To 2)

      For i = 1 To collDSNs.Count
        arrDSN(i, 1) = collDSNs(i)
        arrDSN(i, 2) = collDrivers(i)
      Next i
    Else
      ReDim arrDSN(1 To collDSNs.Count)

      For i = 1 To collDSNs.Count
        arrDSN(i) = collDSNs(i)
      Next i
    End If
  End If

  GetDSNs = arrDSN

End Function


RBS



"jodleren" <sonnich@hot.ee> wrote in message 
news:a19a5b14-d28c-436d-acb5-705f23541464@b10g2000yqa.googlegroups.com...
> Hi all
>
> I want to get a list of ODBC connections, and then pick one - the
> point is, that the connection I am looking for might be spelled
> differently (e.g. with space). So with a list I can find it eaily.
>
> Any suggestions?
> Sonnich 

0
RB
1/27/2010 8:48:00 PM
Reply:

Similar Artilces:

Duplicate named ranges
Is it possible to have duplicate named ranges in a workbook? I am using Excel 2000 on a Windows 2000 system. I can acheive creating a duplicate named range in a workbook by doing a copy sheet into the same workbook but I am unable to create multiple named ranges in the ame workbook by other means. When I try to define the named ranges I get directed to the last copy defined. Is there a way around this? TIA George George, You can have workbook names that apply to the whole workbook, and worksheet names that only apply to that sheet and can be replicated on each sheet. To create the lat...

Naming range
Does anyone know how to name a range, so that I can export from excel into outlook? Several ways, easiest being to highlight it and put a name in the name box (to the left of the formula box) -- Don Guillett SalesAid Software donaldb@281.com "Kimmie" <anonymous@discussions.microsoft.com> wrote in message news:2953201c465ce$2542ce20$a601280a@phx.gbl... > Does anyone know how to name a range, so that I can > export from excel into outlook? try range(mynamedrange).select -- Don Guillett SalesAid Software donaldb@281.com "BerHav" <BerHav@discussions.micr...

How to refer to tab names?
Hi all, I have around 50 tabs (worksheets) to deal with in a workbook. I have to come up with a summary in another worksheet that lists down the tab names and the total items in each tab. So, could anyone please help me figure out on how: 1. to refer to each of the tab names (i.e Food, Place,and etc.) and place them in a cell in another worksheet? 2. to refer to each count of the items at the end of the list for each tab? How can I do it without having to key in them individually? TQ. Use INDIRECT() as below. 'the below will refer to cell B10 of tab name specif...

Table name in Expression builder not allowed?
I tried to do a simple calculation (on a form) but fail to understand this: =[UnitPrice]*[Quantity] works, however if I use the Tables list from (below) and construct the formula it shows as: [OrderDetails].UnitPrice * [OrderDetails].Quantity and it does not work. I get #Name error When field names are written as [UnitPrice] is it not a standard practice to name them as such? Somewhere I also see [OrderDetails]![UnitPrice] notation. Why? Thanks "dindigul" <padhye.m@gmail.com> wrote in news:O9MdHWQEIHA.5752@TK2MSFTNGP02.phx.gbl: > I tried to do a simple calculation ...

JDBC-ODBC Bridge driver & ODBC driver are two different installati
http://www.paphoscarhire.mobi/odbc-bridge2.jpg desktop control panel settings, I have above URL (IMG) Settings well, I need to run in my PC: Type 1 db driver Access / java[SE1.6] Desktop Applications, what I need to install ? I refer for Type 1, JDBC-ODBC Bridge driver.... Is it needed install and Access 2003 ODBC driver (see URL) ...? How I understand if I already installed it(?) since I do not know ? JDBC-ODBC Bridge driver & ODBC driver are two different installations? How to load driver in my program ? The below are correct for: Class.forName & DriverManager.ge...

Want to auto name worksheets
I work with a large spreadsheet that has several worksheets in it. Is it possible to create a macro that can copy the contents of a cell and then paste that as the new name for the worksheet? Reply to doc_cowher@yahoo.com Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Doc, this will do what you want, Sub Name_Sheet() ActiveSheet.Name = [A1] End Sub -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any re...

test #9
...

test with attachment
This is a multi-part message in MIME format. ------=_NextPart_000_002E_01CB40A7.2472CAD0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit test with attachment ------=_NextPart_000_002E_01CB40A7.2472CAD0 Content-Type: application/msword; name="testdoc.doc" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="testdoc.doc" 0M8R4KGxGuEAAAAAAAAAAAAAAAAAAAAAPgADAP7/CQAGAAAAAAAAAAAAAAABAAAAKgAAAAAAAAAA EAAALAAAAAEAAAD+////AAAAACkAAAD////////////////////////...

2006 Isn't Adding Payee Name Variations
I like using the Payee Name Variation in MS Money. It was working exactly liked I wanted it to in MS Money 2005. Now that I have Money 2006, it isn't working at all and I THINK I have the same options checked in Online Settings. In 2005, I would get a downloaded payee like ALLSTATE INS CO INS PREM. I would change it to Allstate Insurance (before accepting)and the downloaded name would automatically pop into the Payee Name Variation box when I acceped the transaction. In 2006, if I change the downloaded payee before accepting, nothing is added to the Payee Name Variation box. I am having ...

Report using Fields with the same name from different tables
Hello all - I created a db with no knowledge of naming convention. Now I am attempting to write some reports pulling data from tables with the same field names. Apparently Access doesn't like this....I attempted to change the names in the BE but when I relink the FE it is prompting for parameters. What is the best way to proceed? Thanks - Lee -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201005/1 TheLee via AccessMonster.com wrote: >I created a db with no knowledge of naming convention. Now I am attempting to &...

test
test You will not receive this again, nor if you state "ignore" or "no reply" in the subject. Following are your complete headers as they appeared on our server: From: "����?�" <hirurg0013@inbox.ru> Subject: test Date: Sat, 1 May 2010 19:13:34 +0300 Lines: 4 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.5843 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-RFC2646: Format=Flowed; Original Message-ID: <OW3lRmU6KHA.5476@TK2MSFTNGP06.phx.gbl> Newsgroups: microsoft.public.nntp.test...

Cannot Omit or Change Name When Printing in Memo Style- Office 2003
Cannot Omit or Change Name When Printing in Memo Style by outlook 2003. Any help is appreciated. Leila Hello Leila, mmh, which name? Your name on the top? You could change it in the Account Options under Username. >-----Original Message----- >Cannot Omit or Change Name When Printing in Memo Style by >outlook 2003. Any help is appreciated. >Leila > >. > No, printing is pretty limited... You could hit forward or reply and print, then close the message. Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Vis...

How to count last name letters
Hi folks, I would like to know how I can count how many records have the same letter. For example, how many records start with the letter A, how many for letter B, etc. Seems simple enough, but I can't figure out how to work this. Would appreciate some help. Thanks. In news:uKD40b2rHHA.3484@TK2MSFTNGP05.phx.gbl, jason <jasonsantos-NOSPAM-@allstatesmedical.com> wrote: > Hi folks, > I would like to know how I can count how many records have the same > letter. For example, how many records start with the letter A, how > many for letter B, etc. > > Seems simple ...

How and where rename tab's name?
...

variable column names in a report
i have an application where the user can enter self selected column names which then in turn are added to a table... application works fine. however, when it comes to reporting, how do i go about adding those self entered columns in a report? any help would be appreciated Do you actually need this in a report or can the results be pushed to Excel for printing? -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "k2sarah" <k2sarah@discussions.microsoft.com> wrote in message news:C56B6FD1-2F5B-45D5-8760-A61E3CA41495@microsoft.c...

Domain name msoffice.com.xx (Is it legal?)
I've searched through the web & found "msoffice.com.ua" being a registered entity in Russia. I intend to register a domain name also using "msoffice.com.<<our country>>" for Microsoft office consulation purpose. Microsoft itself is using "office.microsoft.com". Eventhough we click to "msoffice.com", it will still point to "office.microsoft.com". Does anybody know if the public can REALLY legally use "msoffice.com.<<our country>>" as a domain name? ...

Sorting Cube dimension names in a Pivot Table
How do I get the dimension names in the "pivot table field list" sorted by the translated names? The pivot table field list contains: - one measure-node - one dimension node for each cube-dimension. The cube-dimension-nodes are represented by their translated names. It looks like, this names are not sorted by this translated names, but by the order of the data source. The related radio-buttons in the pivot-table-options are greyed ("A to Z" and "sort by data source"). I would need the "A" to "Z" order. Pleas notice! I...

database query/names issue, causing excel to be sluggish
I have an issue with an excel worksheet I've created. WorkbookA has "Names" which are used to define drop-down menus in WorksheetA. These "Names" are defined to reference a WorksheetB (also in WorkbookA) which itself is populated by a database query, pulling from another Excel WorkbookB. The logic is that WorkbookB should not be touched by the User, only an Admin, and Workbook A is used by User. The Names in WorkbookA are to prevent incorrect entries. Now that I've set it up, WorkbookA is moving really sluggish, but if I delete all the Names it speeds up again....

Can't open two Documents with same name !!
Hi: I install Office 97 in WinXP, works good except when I open a excel spreadsheet a windows always opens saying Can't open two Documents with same name. I click ok and everything works OK. How do I stop this warning I get all the time when I first open a Spreadsheet. Thanks Debra Dalgleish has a list of frequently asked questions at: http://www.contextures.com/xlfaqIndex.html This one sounds like yours: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Owen wrote: > > Hi: I install Office 97 in WinXP, works good except when I > open a excel spreadsheet a windows alway...

One stop shopping: Need ALL Mailbox Delegate Names & Permissions
I'm trying to programatically get all names and permission levels for users who have access to a particular mailbox. As I understand it, access to a mailbox can be granted in serveral ways. The first is via a "Send As" delegate created in Outlook (Tools->Options->Delegates). The names are stored in AD (publicDelegates attribute) and are available through LDAP queries. Problem is with the permissions, where are they stored? How can they be retrieved programatically? Another method of granting access is by adding a user to the (mailbox, inbox, calendar ...) folders in Ou...

"Microsoft Outlook Test Message" question
In a 28 minute span today, I received 10 messages with the above subject from me to me with this message: "This is an e-mail message sent automatically by Microsoft Outlook's Account Manager while testing the settings for your POP3 account." I use Outlook Express 6 w/ WinXP Home SP3, and I do have Word and Excel 2003 installed, but not Outlook. I did at one time to test out MAPILAB, an NNTP newsreader, but they were both uninstalled about 6 months ago. I have scanned for malware of all sorts and came up clean. Any idea why this might have occurred? This is the fi...

Access97 Queries connecting via ODBC to Sybase SQLanywhere backend
I have a Access97 frontend database and it links tables and queries to a Sybase SQLAnywhere backend database. I have imported all the tables from the SQLAnywhere into a Access97 database. I have changed the linked relationship in the tables section to point to the Access97 backend now and not the Sybase backend. Problem is I have SQL queries written in the Access 97 frontend that still try to access Sybase via ODBC. How do I change these queries to access the new Backend. Jamie Network Administrator If the queries were originally written using linked tables, they should still work. Ho...

copy named range & paste values
This works great with columns, but now need to copy named range ActiveCell.EntireColumn.Value = ActiveCell.EntireColumn.Value (my attempt) Sub select_range() Range("zero_range").Select Copy.Range Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub Range("zero_range").value=Range("zero_range").value -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message news:c6f61ae2-a365-43c0-a...

A Name range referring to different cells in different worksheets
Hi all, I have a problem of using named ranges. Replicate the problem. Create a name, say. "Test", which refers to "=Sheet1!$A$1,Sheet2!$B$2,Sheet3!$C$3". Then in Sheet1!A1, type 1, in Sheet2!A2, type 2, in Sheet3!A3, type 3. In a cell, type "=SUM(Test)". The cell displays "#VALUE!" error. Why is that, and are there any solutions or workaround? Thanks a lot. Frederick Chow Hong Kong. You're using the union operator (,) which requires that all arguments be on the same sheet. You could define Test as =Sheet1!A1 + Sheet2!B2 + Sheet3!B...

update the hyperlink in file after changing the file name
I have received a template file which has hyperlinks from the summary page to individual detail worksheets. When I am saving the file on my desktop the hyperlinks are lost as they look for the original file. Is there any way to auto update the links? I would really appreciate any help. On May 10, 12:56=A0pm, Bel71 <Be...@discussions.microsoft.com> wrote: > I have received a template file which has hyperlinks from the summary pag= e to > individual detail worksheets. When I am saving the file on my desktop the > hyperlinks are lost as they look for the original file. ...