Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

VBA ditective

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
Rayne
Asked:
Rayne
3 Solutions
 
discogsCommented:
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
 
RayneAuthor Commented:
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
 
RayneAuthor Commented:
any ideas...?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
discogsCommented:
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
 
Gustav BrockCIOCommented:
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
 
Helen FeddemaCommented:
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
 
PatHartmanCommented:
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
 
RayneAuthor Commented:
thanks all
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now