Solved

VBA ditective

Posted on 2014-04-15
8
390 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 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 35

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help finding download OneNote 2016 or lates version 7 50
Top 1 of each supplier 55 56
Attachment field in SQL 3 28
Why can't I get rid of record selectors on my form? 9 28
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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