Database link in excell

paul20091968 · November 13th, 2006, 04:14 AM

Hi, I have made several queries in MS access and linked some of them in excel via my datasource into excel. My problem is that some queries are not used in Excel and therefore want to delete in MS access just to clean up. But how do I know which query is used in Excel? If i click on a pivot table in excel there is no reference to that particulair query name.
Is is possible to get this in code, that for example cell a1 is showing the name of the database query link for eacht worksheet.
Im new with this and have looked for some info without success.

rgrds,

Paul.

maccas · November 13th, 2006, 07:20 AM

Paul,

I'm not quite sure what you need this for so I haven't done the interface with spreadsheet to dump the queries used. The objects you need to use are QueryTables and are a property of the WorkSheet object in the Excel Obeject model. The following bit of code demonstrates how to get your hands on the data links in a workbook:

Code:

Sub Test()

Dim sh As Worksheet
Dim qt As QueryTable

    For Each sh In ThisWorkbook.Sheets

        If sh.QueryTables.Count > 0 Then

            For Each qt In sh.QueryTables

                MsgBox qt.Sql & vbCr & qt.Connection

            Next qt

        End If

    Next sh

End Sub

Maccas

maccas · November 13th, 2006, 07:49 AM

Actually I've just looked into this some more and found that pivot tables store their external data source connections in a different place. The following should do you:

Code:

Sub Test()

Dim sh As Worksheet
Dim qt As QueryTable
Dim pt As PivotTable
Dim i As Integer
Dim str As String

    For Each sh In ThisWorkbook.Sheets

        ' Query Tables
        If sh.QueryTables.Count > 0 Then

            For Each qt In sh.QueryTables

                MsgBox "SQL = " & qt.Sql & vbCr & "Connection = " & qt.Connection

            Next qt

        End If

        ' Pivot Tables
        If sh.PivotTables.Count > 0 Then

            For Each pt In sh.PivotTables

                If UBound(pt.SourceData) > 2 Then
                    For i = 2 To UBound(pt.SourceData)
                        str = str & pt.SourceData(i)
                    Next i
                    MsgBox "SQL = " & str & vbCr & "Connection = " & pt.SourceData(1)
                Else
                    MsgBox "SQL = " & pt.SourceData(2) & vbCr & "Connection = " & pt.SourceData(1)
                End If

            Next pt

        End If

    Next sh

End Sub

paul20091968 · November 13th, 2006, 08:49 AM

This is great exactly what I need but, there is always a but. I have setup the file location with UNC naming hence not for example m:\OPS_Processes\Reports\.... but \\Nlchos17a.nl.abcdef.com\OPS_Processes$\OPS_Proce sses\Reports

The latter does not appear in the msgbox but the m:\OPS_Processes\Reports does. Can I change this into \\Nlchos17a.nl.abcdef.com\OPS_Processes$\OPS_Proce sses\Reports and where should I do that?

maccas · November 13th, 2006, 09:58 AM

You can map between the two by using the Scripting object model. You'll need to add a reference to it: Tools -> References... -> Microsoft Scrpting Runtime (C:\WINDOWS\system32\scrrun.dll) or use late binding as discussed in another post today.

Code:

Sub test()

Dim scrFSO As Scripting.FileSystemObject
Dim scrDrive As Scripting.Drive

    Set scrFSO = New Scripting.FileSystemObject

    Set scrDrive = scrFSO.GetDrive("V")
    MsgBox scrDrive.ShareName

End Sub

let us know if you're still struggling.

Maccas

paul20091968 · November 13th, 2006, 10:24 AM

Hi Maccas,

I would like to come back on the connection names in the msgbox. I was experimenting with the code to trim it down in order to see what is happening but for some reason it does not work (even when I copy and paste your original code in my test file) I have created in an access database 3 simple queries named 1, 2 and 3. these 3 queries are linked into excell as pivots 1 per worksheet.
I have pasted your code but this error is comming back. "run time error 1004 application defined or object defined error" and line " If UBound(pt.SourceData) > 2 Then..." is highlighted.

How should the code look like with only one pivot do we need the if Ubound be part of it. Im just looking for a staggered approach for me to understand.

best regards

Paul.

Im now really strugling.

maccas · November 13th, 2006, 11:43 AM

Paul,

Sorry for not replying sooner I've been away from the computer. It would appear that we're getting away from what I'll be able to solve remotely for you as this is a debugging issue now. I've never used this functionality before (just figured it out from the object model) so I've never come across this issue.

It sounds like the the computer is either chucking an exception at the SourceData property of one of your pivot tables or the dimensionality of the SourceData property (I'm guessing the former is more likely). Could you open up the locals window (View -> Locals Window) at the debug line highlight. From here could you expand up the pt varaible and then report what is under the SourceData property? This would be how I'd debug the issue if I were faced with what you are.

I'm guessing that some pivot tables do not have a SourceData property if the internally linked and so we'll need to put in some defensive programming.

Maccas

paul20091968 · November 17th, 2006, 08:02 AM

Hi Maccas,

Sorry for me not responding sooner, was pretty bussy. The problem I had is solved, not really sure how but I rebooted the PC. This morning I had a similair error and rebooted the PC and work perfect.

Thanks for looking into this matter.

rgrds,

Paul.