[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VBA ditective

Posted on 2014-04-15
8
Medium Priority
?
406 Views
Last Modified: 2014-04-17
Hello
Is there a way in vba to know if tblName is being used by any query or form object? I am trying to change the column sizes in the table and it will not let me do as it says that this table is a part of relationship. Even after breaking the relationship in the relation tab, still I see the same issue so I need to know what other form or query object is using or linking to that table


Thank you
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Expert Comment

by:discogs
ID: 40002844
Which version of Access are you using? Are you using the actual delete button on your keyboard to remove the relationships? You should receive a prompt to confirm relationship removal.
0
 

Author Comment

by:Rayne
ID: 40003081
I am using access 2010 and yes I am getting that prompt. Even after clicking yes to that, I still get the error. In between the zillions of query and forms that I created,  - i guess if I could vba to loop through all objects (queries or forms) and see which ones are using that table in question...
0
 

Author Comment

by:Rayne
ID: 40003082
any ideas...?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Expert Comment

by:discogs
ID: 40003108
This is untested on my end but if you know the relationship name then:

Sub SubDelRelations()
Dim Myrel As Relation
Dim MyDB As Database

Set MyDB = DBEngine.Workspaces(0).Databases(0)

MyDB.Relations.Delete "enter your relationship name here"
End Sub

Open in new window

0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 501 total points
ID: 40003330
You can use Rick Fisher's tool, Find and Replace:

http://rickworld.com/products.html

The design of the tool is horrible but it works.
Free for the trying (and will fit your purpose) and quite cheap to register.

/gustav
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 498 total points
ID: 40004325
If at some point you made a copy of the table, or renamed it, you may have to put back a copy of the table (or the table with its previous name) into the Relationships diagram, then delete the link, and then you can make the edits you need.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 501 total points
ID: 40005052
In the ribbon for the Relationships window is a button named - All Relationships.  Pressing the button will make visible any relationship you removed from the window.

To actually delete a relationship, you must select the relationship LINE.  To hide a relationship, you can just click on the table and delete it.  The relationship is removed from the picture but not actually removed from the tables.  That is probably your problem.

Access (at least newer versions) does provide a cross reference of where objects are used.  To see it, you must turn on Name Auto-Correct.  This will build a name map and then give you the option (in the ribbon or right-click menu, I don't remember) of viewing the usage.  Once you have found what you need, I suggest that you turn Name Auto-correct OFF again and leave it that way.  Although having Auto-Correct on can save you some effort in some cases, it doesn't work the way most people think it should work and therefore causes more problems than it solves.  Safest is to leave it off unless you are intending to use it for something specific.  If you think you might actually want to use it, make sure to go find the FAQ published many years ago and read it carefully.
0
 

Author Comment

by:Rayne
ID: 40007947
thanks all
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
New style of hardware planning for Microsoft Exchange server.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question