Hi travis,
Try this small bit of code. It will find all data queries and list them.
Optionally, you can specify the old ODBC path to delete old queries.
Sub ListDataQueries()
Dim sht As Worksheet
Dim qtbl As QueryTable
Dim qName, qSheet, qAdress, qSource As String
Dim connOld, success As String
' set this to your old database to delete connections
' to that database, or just ignore
' to list the current querytables
connOld = "ODBC;DBQ=C:\Old Path\olddb.mdb"
success = "Still Active"
For Each sht In ActiveWorkbook.Worksheets
For Each qtbl In sht.QueryTables
qName = qtbl.Name
qSheet = qtbl.Destination.Worksheet.Name
qAdress = qtbl.Destination.Address
qSource = qtbl.Connection
If Left(qSource, Len(connOld)) = connOld Then
qtbl.Delete
success = "Deleted"
End If
MsgBox qName & " - " _
& qSheet & "!" _
& qAdress & " " _
& success
Next qtbl
Next sht
End Sub
Ed Ferrero
http://www.edferrero.com
[Quoted Text] > I've just switched from one Access application to another as the data
> source for an Excel spreadsheet, as a result I'm having to swt up all
> the links all over.
>
> Inserting the new links isn't a problem, but I can't seem to get rid of
> all the old ones. Although I'm pretty sure I've gotten rid of all the
> references to the old database I still get error messages about the
> path (to the old database) no longer being valid, followed by a login
> screen etc.
>
> Is there a way to find and kill all the links so I can start again?
>
> These links were set up with data > import external data > new database
> query. The links option under the edit menu in Excel is greyed out.
>
> Travis
>
|