How do break the link from my excel workbook to a access database.

[Vernon]

The access database my excel workbook is trying to link to no longer exists.
Yet each time I open the lworkbook I receive a message to enable or disable
automatic refresh. How do I unlink and how do I shut off the prompting for
refresh?

 

[SongBear]

I hope this doesn't post twice, I tried once and it appeared to fail, (reply
window errased itself after it caused me to log in again after I clicked the
Post button) so I am going to try again.

Vernon, the usefulness of this may depend on which version of Excel you are
using.

Please note, you should only make the following changes in a copy of the
working copy, not the original. Only make the modified copy the working copy
after you have tested all changes to make sure you have not make a horrible,
horrible mistake.

Three methods below, in descending order of safety. The first one requires
you to go to each worksheet and manually disable ‘automatic update on open’
for each query. This one is safer because you get to look at each query as
you go and will be able to be sure you actually want to do it. The second
method uses VBA code to do all of the QueryTables at once, less safe only
because you do not get that chance to “Abort Abort†if you realize, after
all, that you actually need one or more of the tables to update. On the other
hand, the VBA method is completely reversible. The third method destroys the
queries.

The following is for 'QueryTables' or queries embedded in the worksheet:
First Method: For more information, search Help for “refresh imported dataâ€.
Do this on EVERY worksheet in the workbook in question.
On each worksheet in the workbook, Right-Click inside the data area of each
query. You will get a dropdown menu with the usual Right-Click suspects such
as Cut, Copy, Paste, and Paste Special. If you are inside the query result
area, you will also see additional options allowing you to work with the
query itself, such as Edit Query, Data Range Properties, and Refresh Data.

If you click ‘Data Range Properties’ you will get a dialog box called
"External Data Range Properties". In this box you will find many choices,
including "Refresh data on file open". Likely, this box is 'checked' on one
or more of your QueryTables. Uncheck that box for each embedded query that
you do not want to update on open. If you do this for all of the QueryTables
in the workbook, this will stop the attempt at updating on open and should
stop the notifications on open.

Please note that there could be more than one QueryTable in a worksheet.
They can even end up overlapping, making them hard to find. If you make every
QT that you can find not refreshing on open and you still get the message on
open, than go to each sheet, click Edit | Go To… Then look in the ‘Go To’
list for things named ‘Query_from_[Data source name]’. If you find more than
one on a particular sheet, Go To both and make sure that you have turned off
automatic update on open for both.

Second Method:
If you still get the message on startup, create a new, blank worksheet in
the workbook. Right click on the tab of the new worksheet and select “View
Codeâ€. The Visual Basic for Applications (VBA) window will open with an open
code window (module) showing. Paste the following code in the module.

' code starts**************

Sub List_QueryTables()
Dim x As Integer
x = 0
With ActiveWorkbook
For Each s In .Sheets
x = x + 1
y = 0
Debug.Print "Worksheet#"; x, "Worksheet Name:"; s.Name
For Each q In s.QueryTables
y = y + 1
Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name,
"Refresh on Open? "; q.RefreshOnFileOpen
' q.RefreshOnFileOpen = False
' Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen
Next q
Next s
End With
End Sub

'code ends***********

Note that there is a single quote (or an apostrophe) in front of two lines
of code, leave them there for now. Make sure your blinking cursor is within
any line of the code in the Sub procedure that you have pasted.

Also note that the first Debug.Print line [Debug.Print , "QueryTable
Count:"; y, "Query Name: "; q.Name, "Refresh on Open? ";
q.RefreshOnFileOpen] word-wraps in this Discusson Goup Reply window, it
should not wrap in the code window (all one line). It should paste ok, but
check that it is on a single line.

In the top of the VBA window, you will see the usual menus such as File,
Edit, and View; you will also see the less usual items of ‘Debug’ and ‘Run’.
Under the VBA menu Run, and with your cursor inside the code you have pasted,
click “Run Sub/Userformâ€.

After you have run the ‘List_QueryTables’ Subroutine, under the VBA ‘View’
menu, click “Immediate Windowâ€. This window is where the ‘Debug.Print’
command sends output. Scan down the list in the immediate window to see if
you find any QueryTables with ‘Refresh on Open’ set to True.

If you find any, go to the listed worksheet in the workbook, Edit | Go To
that query and look at it to determine that you really want to disable auto
update on that QT.

Once (and only after) you have examined all of the stray, hard to find QTs,
and if you decide that you really do want to disable auto update on all QTs
in the workbook, go back to the VBA window with your code showing in it.
Delete the single quote in front of the line that reads “Q.RefreshOnFileOpen
= False†and also for the line that reads ““Debug.Print , , q.Name, "Refresh
on Open? "; q.RefreshOnFileOpenâ€â€.

Empty the Immediate window, and then run the code again, and again check
your results in the “Immediate†window. You should now see that all QTs in
the workbook are now set to NOT update on open.

If you have not already done this procedure in a copy of the original
workbook, now make sure that you ‘SAVE AS’ to another file name; otherwise,
if this is already a copy, than just save, close, and then re-open the
workbook to check. You should not get the update message. If you don’t like
it, you can go back to the code window and replace the word “False†in the
“q.RefreshOnFileOpen = False†line with the word “Trueâ€, and then run the
code again. Check your results in the Immediate window, if it worked ok, then
save and re-open and you should have the annoying message back.

If you are satisfied that the workbook will still work with all Queries not
updating-on-open, then move and rename the former working copy to a backup
name and location, and then rename this file to the working copy name in the
working directory. Use as directed; (Lather, rinse, and repeat as necessary).

Third and least safe method: Warning, this will destroy any database
connection, data table linking, and record filtering and selection
information that is embedded in the queries themselves. On the other hand,
this will preserve any data left in the table from the last successful update.

If you really do know that you will never use the queries again, might need
what is left in the table, you can go to each query, use the Edit | Go To
tool to select each whole QueryTable, and then paste each table onto itself
using “Edit | Copy†then “Edit | Paste Special | Values and Number Formatsâ€.
This will make a normal, unlinked excel table out of whatever is left in the
QueryTable, preserving formats and data but not formulas. Be careful that you
do not select any formulas adjacent to the QueryTable.

[Even less safe: If you know for certain that you will never need even the
information available from the last good update in any of the QueryTables, if
any was left, then you could just delete the whole selected table instead of
copy – paste special.]

The following is for DDE or OLE links to external data, not queries:

In 2003, XP, and maybe other versions, there is a "Links" choice under the
"Edit" dropdown menu. (as in File - Edit - View at the top of the page.)
If you do not have any links in the workbook, the "Links" choice will be
grayed out.
When you click on "Edit | Links" in a workbook that has links, you will get
some choices, including the ability to convert automatic links to manual
links, and the ability to turn off "Ask to update automatic links" for
automatic links. You can also do other things having to do with re-focusing
links to another data source.
Search help for the subject "Control when links are updated" for more
information.

Hope this helps. Let us know if you need further help or clarification.

SongBear

 

[SongBear]

I did forget one thing in the previous message. If you use Method Two (VBA
Code), when finished, you will need to delete the new, empty worksheet that
you created in the workbook. This will get rid of the temporary code (which
is attached to that new empty worksheet) and prevent an all-new annoyance on
opening the workbook, the 'Macros Dissabled' security message.

 

[Vernon]

Option one worked great, thank you

I hope this doesn't post twice, I tried once and it appeared to fail, (reply
window errased itself after it caused me to log in again after I clicked the
Post button) so I am going to try again.

Vernon, the usefulness of this may depend on which version of Excel you are
using.

Please note, you should only make the following changes in a copy of the
working copy, not the original. Only make the modified copy the working copy
after you have tested all changes to make sure you have not make a horrible,
horrible mistake.

Three methods below, in descending order of safety. The first one requires
you to go to each worksheet and manually disable ‘automatic update on open’
for each query. This one is safer because you get to look at each query as
you go and will be able to be sure you actually want to do it. The second
method uses VBA code to do all of the QueryTables at once, less safe only
because you do not get that chance to “Abort Abort†if you realize, after
all, that you actually need one or more of the tables to update. On the other
hand, the VBA method is completely reversible. The third method destroys the
queries.

The following is for 'QueryTables' or queries embedded in the worksheet:
First Method: For more information, search Help for “refresh imported dataâ€.
Do this on EVERY worksheet in the workbook in question.
On each worksheet in the workbook, Right-Click inside the data area of each
query. You will get a dropdown menu with the usual Right-Click suspects such
as Cut, Copy, Paste, and Paste Special. If you are inside the query result
area, you will also see additional options allowing you to work with the
query itself, such as Edit Query, Data Range Properties, and Refresh Data.

If you click ‘Data Range Properties’ you will get a dialog box called
"External Data Range Properties". In this box you will find many choices,
including "Refresh data on file open". Likely, this box is 'checked' on one
or more of your QueryTables. Uncheck that box for each embedded query that
you do not want to update on open. If you do this for all of the QueryTables
in the workbook, this will stop the attempt at updating on open and should
stop the notifications on open.

Please note that there could be more than one QueryTable in a worksheet.
They can even end up overlapping, making them hard to find. If you make every
QT that you can find not refreshing on open and you still get the message on
open, than go to each sheet, click Edit | Go To… Then look in the ‘Go To’
list for things named ‘Query_from_[Data source name]’. If you find more than
one on a particular sheet, Go To both and make sure that you have turned off
automatic update on open for both.

Second Method:
If you still get the message on startup, create a new, blank worksheet in
the workbook. Right click on the tab of the new worksheet and select “View
Codeâ€. The Visual Basic for Applications (VBA) window will open with an open
code window (module) showing. Paste the following code in the module.

' code starts**************

Sub List_QueryTables()
Dim x As Integer
x = 0
With ActiveWorkbook
For Each s In .Sheets
x = x + 1
y = 0
Debug.Print "Worksheet#"; x, "Worksheet Name:"; s.Name
For Each q In s.QueryTables
y = y + 1
Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name,
"Refresh on Open? "; q.RefreshOnFileOpen
' q.RefreshOnFileOpen = False
' Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen
Next q
Next s
End With
End Sub

'code ends***********

Note that there is a single quote (or an apostrophe) in front of two lines
of code, leave them there for now. Make sure your blinking cursor is within
any line of the code in the Sub procedure that you have pasted.

Also note that the first Debug.Print line [Debug.Print , "QueryTable
Count:"; y, "Query Name: "; q.Name, "Refresh on Open? ";
q.RefreshOnFileOpen] word-wraps in this Discusson Goup Reply window, it
should not wrap in the code window (all one line). It should paste ok, but
check that it is on a single line.

In the top of the VBA window, you will see the usual menus such as File,
Edit, and View; you will also see the less usual items of ‘Debug’ and ‘Run’.
Under the VBA menu Run, and with your cursor inside the code you have pasted,
click “Run Sub/Userformâ€.

After you have run the ‘List_QueryTables’ Subroutine, under the VBA ‘View’
menu, click “Immediate Windowâ€. This window is where the ‘Debug.Print’
command sends output. Scan down the list in the immediate window to see if
you find any QueryTables with ‘Refresh on Open’ set to True.

If you find any, go to the listed worksheet in the workbook, Edit | Go To
that query and look at it to determine that you really want to disable auto
update on that QT.

Once (and only after) you have examined all of the stray, hard to find QTs,
and if you decide that you really do want to disable auto update on all QTs
in the workbook, go back to the VBA window with your code showing in it.
Delete the single quote in front of the line that reads “Q.RefreshOnFileOpen
= False†and also for the line that reads ““Debug.Print , , q.Name, "Refresh
on Open? "; q.RefreshOnFileOpenâ€â€.

Empty the Immediate window, and then run the code again, and again check
your results in the “Immediate†window. You should now see that all QTs in
the workbook are now set to NOT update on open.

If you have not already done this procedure in a copy of the original
workbook, now make sure that you ‘SAVE AS’ to another file name; otherwise,
if this is already a copy, than just save, close, and then re-open the
workbook to check. You should not get the update message. If you don’t like
it, you can go back to the code window and replace the word “False†in the
“q.RefreshOnFileOpen = False†line with the word “Trueâ€, and then run the
code again. Check your results in the Immediate window, if it worked ok, then
save and re-open and you should have the annoying message back.

If you are satisfied that the workbook will still work with all Queries not
updating-on-open, then move and rename the former working copy to a backup
name and location, and then rename this file to the working copy name in the
working directory. Use as directed; (Lather, rinse, and repeat as necessary).

Third and least safe method: Warning, this will destroy any database
connection, data table linking, and record filtering and selection
information that is embedded in the queries themselves. On the other hand,
this will preserve any data left in the table from the last successful update.

If you really do know that you will never use the queries again, might need
what is left in the table, you can go to each query, use the Edit | Go To
tool to select each whole QueryTable, and then paste each table onto itself
using “Edit | Copy†then “Edit | Paste Special | Values and Number Formatsâ€.
This will make a normal, unlinked excel table out of whatever is left in the
QueryTable, preserving formats and data but not formulas. Be careful that you
do not select any formulas adjacent to the QueryTable.

[Even less safe: If you know for certain that you will never need even the
information available from the last good update in any of the QueryTables, if
any was left, then you could just delete the whole selected table instead of
copy – paste special.]

The following is for DDE or OLE links to external data, not queries:

In 2003, XP, and maybe other versions, there is a "Links" choice under the
"Edit" dropdown menu. (as in File - Edit - View at the top of the page.)
If you do not have any links in the workbook, the "Links" choice will be
grayed out.
When you click on "Edit | Links" in a workbook that has links, you will get
some choices, including the ability to convert automatic links to manual
links, and the ability to turn off "Ask to update automatic links" for
automatic links. You can also do other things having to do with re-focusing
links to another data source.
Search help for the subject "Control when links are updated" for more
information.

Hope this helps. Let us know if you need further help or clarification.

SongBear

The access database my excel workbook is trying to link to no longer exists.
Yet each time I open the lworkbook I receive a message to enable or disable
automatic refresh. How do I unlink and how do I shut off the prompting for
refresh?

 

[SongBear]

Great, thanks for letting me know.
SongBear

Option one worked great, thank you

I hope this doesn't post twice, I tried once and it appeared to fail, (reply
window errased itself after it caused me to log in again after I clicked the
Post button) so I am going to try again.

Vernon, the usefulness of this may depend on which version of Excel you are
using.

Please note, you should only make the following changes in a copy of the
working copy, not the original. Only make the modified copy the working copy
after you have tested all changes to make sure you have not make a horrible,
horrible mistake.

Three methods below, in descending order of safety. The first one requires
you to go to each worksheet and manually disable ‘automatic update on open’
for each query. This one is safer because you get to look at each query as
you go and will be able to be sure you actually want to do it. The second
method uses VBA code to do all of the QueryTables at once, less safe only
because you do not get that chance to “Abort Abort†if you realize, after
all, that you actually need one or more of the tables to update. On the other
hand, the VBA method is completely reversible. The third method destroys the
queries.

The following is for 'QueryTables' or queries embedded in the worksheet:
First Method: For more information, search Help for “refresh imported dataâ€.
Do this on EVERY worksheet in the workbook in question.
On each worksheet in the workbook, Right-Click inside the data area of each
query. You will get a dropdown menu with the usual Right-Click suspects such
as Cut, Copy, Paste, and Paste Special. If you are inside the query result
area, you will also see additional options allowing you to work with the
query itself, such as Edit Query, Data Range Properties, and Refresh Data.

If you click ‘Data Range Properties’ you will get a dialog box called
"External Data Range Properties". In this box you will find many choices,
including "Refresh data on file open". Likely, this box is 'checked' on one
or more of your QueryTables. Uncheck that box for each embedded query that
you do not want to update on open. If you do this for all of the QueryTables
in the workbook, this will stop the attempt at updating on open and should
stop the notifications on open.

Please note that there could be more than one QueryTable in a worksheet.
They can even end up overlapping, making them hard to find. If you make every
QT that you can find not refreshing on open and you still get the message on
open, than go to each sheet, click Edit | Go To… Then look in the ‘Go To’
list for things named ‘Query_from_[Data source name]’. If you find more than
one on a particular sheet, Go To both and make sure that you have turned off
automatic update on open for both.

Second Method:
If you still get the message on startup, create a new, blank worksheet in
the workbook. Right click on the tab of the new worksheet and select “View
Codeâ€. The Visual Basic for Applications (VBA) window will open with an open
code window (module) showing. Paste the following code in the module.

' code starts**************

Sub List_QueryTables()
Dim x As Integer
x = 0
With ActiveWorkbook
For Each s In .Sheets
x = x + 1
y = 0
Debug.Print "Worksheet#"; x, "Worksheet Name:"; s.Name
For Each q In s.QueryTables
y = y + 1
Debug.Print , "QueryTable Count:"; y, "Query Name: "; q.Name,
"Refresh on Open? "; q.RefreshOnFileOpen
' q.RefreshOnFileOpen = False
' Debug.Print , , q.Name, "Refresh on Open? "; q.RefreshOnFileOpen
Next q
Next s
End With
End Sub

'code ends***********

Note that there is a single quote (or an apostrophe) in front of two lines
of code, leave them there for now. Make sure your blinking cursor is within
any line of the code in the Sub procedure that you have pasted.

Also note that the first Debug.Print line [Debug.Print , "QueryTable
Count:"; y, "Query Name: "; q.Name, "Refresh on Open? ";
q.RefreshOnFileOpen] word-wraps in this Discusson Goup Reply window, it
should not wrap in the code window (all one line). It should paste ok, but
check that it is on a single line.

In the top of the VBA window, you will see the usual menus such as File,
Edit, and View; you will also see the less usual items of ‘Debug’ and ‘Run’.
Under the VBA menu Run, and with your cursor inside the code you have pasted,
click “Run Sub/Userformâ€.

After you have run the ‘List_QueryTables’ Subroutine, under the VBA ‘View’
menu, click “Immediate Windowâ€. This window is where the ‘Debug.Print’
command sends output. Scan down the list in the immediate window to see if
you find any QueryTables with ‘Refresh on Open’ set to True.

If you find any, go to the listed worksheet in the workbook, Edit | Go To
that query and look at it to determine that you really want to disable auto
update on that QT.

Once (and only after) you have examined all of the stray, hard to find QTs,
and if you decide that you really do want to disable auto update on all QTs
in the workbook, go back to the VBA window with your code showing in it.
Delete the single quote in front of the line that reads “Q.RefreshOnFileOpen
= False†and also for the line that reads ““Debug.Print , , q.Name, "Refresh
on Open? "; q.RefreshOnFileOpenâ€â€.

Empty the Immediate window, and then run the code again, and again check
your results in the “Immediate†window. You should now see that all QTs in
the workbook are now set to NOT update on open.

If you have not already done this procedure in a copy of the original
workbook, now make sure that you ‘SAVE AS’ to another file name; otherwise,
if this is already a copy, than just save, close, and then re-open the
workbook to check. You should not get the update message. If you don’t like
it, you can go back to the code window and replace the word “False†in the
“q.RefreshOnFileOpen = False†line with the word “Trueâ€, and then run the
code again. Check your results in the Immediate window, if it worked ok, then
save and re-open and you should have the annoying message back.

If you are satisfied that the workbook will still work with all Queries not
updating-on-open, then move and rename the former working copy to a backup
name and location, and then rename this file to the working copy name in the
working directory. Use as directed; (Lather, rinse, and repeat as necessary).

Third and least safe method: Warning, this will destroy any database
connection, data table linking, and record filtering and selection
information that is embedded in the queries themselves. On the other hand,
this will preserve any data left in the table from the last successful update.

If you really do know that you will never use the queries again, might need
what is left in the table, you can go to each query, use the Edit | Go To
tool to select each whole QueryTable, and then paste each table onto itself
using “Edit | Copy†then “Edit | Paste Special | Values and Number Formatsâ€.
This will make a normal, unlinked excel table out of whatever is left in the
QueryTable, preserving formats and data but not formulas. Be careful that you
do not select any formulas adjacent to the QueryTable.

[Even less safe: If you know for certain that you will never need even the
information available from the last good update in any of the QueryTables, if
any was left, then you could just delete the whole selected table instead of
copy – paste special.]

The following is for DDE or OLE links to external data, not queries:

In 2003, XP, and maybe other versions, there is a "Links" choice under the
"Edit" dropdown menu. (as in File - Edit - View at the top of the page.)
If you do not have any links in the workbook, the "Links" choice will be
grayed out.
When you click on "Edit | Links" in a workbook that has links, you will get
some choices, including the ability to convert automatic links to manual
links, and the ability to turn off "Ask to update automatic links" for
automatic links. You can also do other things having to do with re-focusing
links to another data source.
Search help for the subject "Control when links are updated" for more
information.

Hope this helps. Let us know if you need further help or clarification.

SongBear

The access database my excel workbook is trying to link to no longer exists.
Yet each time I open the lworkbook I receive a message to enable or disable
automatic refresh. How do I unlink and how do I shut off the prompting for
refresh?