Solved

VBA ditective

Posted on 2014-04-15
8
394 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 36

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

831 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