Solved

VBA ditective

Posted on 2014-04-15
8
385 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
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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 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 166 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 34

Accepted Solution

by:
PatHartman earned 167 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
My experience with Windows 10 over a one year period and suggestions for smooth operation
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now