How to check if table is linked to any form or is any SQL query is linked to that table (1 Viewer)

S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
I have two MS Access files. One is back-end, where all the tables are saved.
Other is frond-end, all the table from back-end are linked to front-end.
All the reports, forms, queries are present in front end.

I need a help here. There are few tables in the BE. I am not able to understand purpose of these tables.
How do I know if any form or SQL query is linked to this table.
Suppose there is Tbl_One in back-end and linked in front end, how do I know if any form or SQL query is linked to Tbl_One.
Reason is, I wanted to remove few of these tables if these are not linked to any other object and am not able to find a purpose of the table.
Can anyone please help me in this.
 
Minty

Minty

AWF VIP
Local time
Today, 07:33
Joined
Jul 26, 2013
Messages
10,371
If you use an add-in called V-Tools it has a deep search facility that can find all occurrences of string.

 
S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
Hi @Minty thanks for the help. Am checking this add-in on personal machine.
At the moment am not sure, if add-in is allowed by the firm. I need to check this.
If allowed, will certainly use this. Else need to search some alternative.
Have a nice day ahead. :)
 
Minty

Minty

AWF VIP
Local time
Today, 07:33
Joined
Jul 26, 2013
Messages
10,371
Copy the FE database to your local machine - do the search.
The results will be the same for either.
 
S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
Hi @Minty thanks for the help. I am not allowed to transfer anything from company machine to anywhere else. :(
That is constraint, please give me some time, I will try if I can do something with the add-in from local machine.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 07:33
Joined
Feb 19, 2013
Messages
16,627
How do I know if any form or SQL query is linked to this table.
Click to expand...
does not include references in vba code, but there is the object dependencies option on the database tools ribbon

for vba code just open the vba window and search for the table name.
 
S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
Hi @CJ_London thanks for the help. Have a nice day ahead.
I agree with you, I can check for the table in the VBA code, did not realize that.
Main issue would be SQL queries, which are more than 100.
Have a nice day ahead. :)
 
S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
Hi @CJ_London please see attached screenshot for your reference. Does this mean that, this table has no connection to any form or SQL query.
I will search for VBA code manually. Thanks.

PS - There are two SQL queries mentioned in the Ignored Objects.
I opened and checked these two queries. This particular table is not mentioned in the SQL queries.
 

Attachments

  • 1.png
    1.png
    47.3 KB · Views: 105
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:33
Joined
Sep 12, 2006
Messages
15,660
you can iterate through your queries to look for a string
I use variations of this regularly lot to search for fields in queries.
this is aircode, but the logic is correct.

Code: 
for each qdf in application.querydefs
   if instr("searched string",qdf.sql) then
       add to output string variable
  end if
next

save output string to text file
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:33
Joined
Sep 12, 2006
Messages
15,660
Here's a working example. This version allows you to search for a string but optionally exclude another string.
You can also include all queries, or include/exclude action queries.

It saves a text file in the same folder as the access database, and opens it for viewing.

Just paste this into a module.

Code: 
Sub search_queries_SELECT_msgbox2()

Const searchfor = "String1"
Const searchfor2 = ""  ' if you enter something here it will only return queries that do not INCLUDE this string.

Dim db As database
Dim QDF As QueryDef
Dim s As String
Dim f As Long
Dim fname As String
Dim result As Long


Dim recs(3) As Long
Dim passtype As Long

'    passtype = 0 'select queries
'    passtype = 1 'action queries
    passtype = 2 'all queries


    s = ""
    Select Case passtype
    Case 0: s = "Select Queries: " & vbCrLf & vbCrLf
    Case 1: s = "Action Queries: " & vbCrLf & vbCrLf
    Case 2: s = "All Queries: " & vbCrLf & vbCrLf
    End Select


    s = s & vbCrLf & "Queries Including Text: " & searchfor & vbCrLf
    If searchfor2 > "" Then
        s = s & "And Excluding Text: " & searchfor2 & vbCrLf
    End If
       
    s = s & vbCrLf
   
recs(1) = 0
recs(2) = 0
recs(3) = 0
       
Set db = CurrentDb
For Each QDF In db.QueryDefs
 
    If passtype = 0 Then
        If QDF.Type <> 0 Then GoTo nextqry
    End If
   
    If passtype = 1 Then
        result = QDF.Type And 240
        If result = 0 Then GoTo nextqry
    End If
   
    recs(1) = recs(1) + 1


    If InStr(QDF.SQL, searchfor) > 0 Then
           
            If searchfor2 <> "" Then
                If InStr(QDF.SQL, searchfor2) = 0 Then
                    s = s & QDF.name & vbCrLf
                    recs(2) = recs(2) + 1
                End If
            Else
                s = s & QDF.name & vbCrLf
                recs(2) = recs(2) + 1
            End If
    End If

nextqry:
Next

If recs(2) = 0 Then
    MsgBox ("Search Details returned no records. ")
Else
    Call MsgBox("Search Result: " & vbCrLf & vbCrLf & _
        Format(recs(1), "00000") & "  Queries Checked " & vbCrLf & _
        Format(recs(2), "00000") & "  Matches Found" & vbCrLf & vbCrLf & _
        "The Results will now be output to a text file. ")
       
        f = FreeFile
        fname = CurrentProject.path & "\" & "log-" & Format(Date, "yyyy-mm-dd") & ".txt"
        Open fname For Output As #f
        Print #f, s
        Close #f
        FollowHyperlink fname
End If


End Sub
 
S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
gemma-the-husky said:
you can iterate through your queries to look for a string
I use variations of this regularly lot to search for fields in queries.
this is aircode, but the logic is correct.

Code: 
for each qdf in application.querydefs
   if instr("searched string",qdf.sql) then
       add to output string variable
  end if
next

save output string to text file
Click to expand...
Hi @gemma-the-husky am getting a bug at Application.QueryDefs
It is is saying Compile Error. Method or Data Member not found.
Could you please help if you get time.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:33
Joined
May 7, 2009
Messages
19,247
it is a Member of Database object (currentdb) not Application.
 
S

SachAccess

Active member
Local time
Today, 12:03
Joined
Nov 22, 2021
Messages
389
gemma-the-husky said:
Here's a working example. This version allows you to search for a string but optionally exclude another string.
You can also include all queries, or include/exclude action queries.

It saves a text file in the same folder as the access database, and opens it for viewing.

Just paste this into a module.

Code: 
Sub search_queries_SELECT_msgbox2()

Const searchfor = "String1"
Const searchfor2 = ""  ' if you enter something here it will only return queries that do not INCLUDE this string.

Dim db As database
Dim QDF As QueryDef
Dim s As String
Dim f As Long
Dim fname As String
Dim result As Long


Dim recs(3) As Long
Dim passtype As Long

'    passtype = 0 'select queries
'    passtype = 1 'action queries
    passtype = 2 'all queries


    s = ""
    Select Case passtype
    Case 0: s = "Select Queries: " & vbCrLf & vbCrLf
    Case 1: s = "Action Queries: " & vbCrLf & vbCrLf
    Case 2: s = "All Queries: " & vbCrLf & vbCrLf
    End Select


    s = s & vbCrLf & "Queries Including Text: " & searchfor & vbCrLf
    If searchfor2 > "" Then
        s = s & "And Excluding Text: " & searchfor2 & vbCrLf
    End If
      
    s = s & vbCrLf
  
recs(1) = 0
recs(2) = 0
recs(3) = 0
      
Set db = CurrentDb
For Each QDF In db.QueryDefs

    If passtype = 0 Then
        If QDF.Type <> 0 Then GoTo nextqry
    End If
  
    If passtype = 1 Then
        result = QDF.Type And 240
        If result = 0 Then GoTo nextqry
    End If
  
    recs(1) = recs(1) + 1


    If InStr(QDF.SQL, searchfor) > 0 Then
          
            If searchfor2 <> "" Then
                If InStr(QDF.SQL, searchfor2) = 0 Then
                    s = s & QDF.name & vbCrLf
                    recs(2) = recs(2) + 1
                End If
            Else
                s = s & QDF.name & vbCrLf
                recs(2) = recs(2) + 1
            End If
    End If

nextqry:
Next

If recs(2) = 0 Then
    MsgBox ("Search Details returned no records. ")
Else
    Call MsgBox("Search Result: " & vbCrLf & vbCrLf & _
        Format(recs(1), "00000") & "  Queries Checked " & vbCrLf & _
        Format(recs(2), "00000") & "  Matches Found" & vbCrLf & vbCrLf & _
        "The Results will now be output to a text file. ")
      
        f = FreeFile
        fname = CurrentProject.path & "\" & "log-" & Format(Date, "yyyy-mm-dd") & ".txt"
        Open fname For Output As #f
        Print #f, s
        Close #f
        FollowHyperlink fname
End If


End Sub
Click to expand...
Hi @gemma-the-husky , am getting a bug at Dim db As Dababase line.
It is saying Compile Error. Expected user-defined type, not project.
Could you please help if you get time.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:33
Joined
May 7, 2009
Messages
19,247
Dim db As DAO.Database
Dim QDF As DAO.QueryDef
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:33
Joined
Sep 12, 2006
Messages
15,660
It works for me. Just put the code inside a normal code module. Put the standard headers in the module,

Maybe you will have to change
dim dB as database to
dim dB as dao.database

etc, as @arnelgp just posted. It depends on the references in your database.
You haven't used dababase, have you? I presume that's just a typo in your post.

I just copied this code to a new database, added a table and a query and it worked correctly. I just wanted to make sure I hadn't used some public variables declared somewhere else - but I hadn't. This code should work correctly.
 
Minty

Minty

AWF VIP
Local time
Today, 07:33
Joined
Jul 26, 2013
Messages
10,371
@gemma-the-husky It might be an Access version thing.
After an O365 update a while ago we had to add the DAO disambiguation to a host of older databases that had worked fine.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:33
Joined
Sep 12, 2006
Messages
15,660
@Minty. It might be that.
I recently converted a database from A2003 to A365, and the conversion set the references in the wrong order, so ADO was listed ahead of DAO.
 
You must log in or register to reply here.

Similar threads

C
SQL Server to Azure SQL
Replies
2
Views
203
choaspes
C
D
Linked SQL Server Table Missing Columns
Replies
8
Views
696
GPGeorge
GPGeorge
Z
SQL table linked in MSAccess, insert yes, update/delete no
2
Replies
24
Views
809
Zakraket
Z
K
How to change the source of a linked table?
Replies
18
Views
842
KitaYama
K
R
Linked Table - Excel - Blank Column A
2
Replies
21
Views
1,006
pdanes
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom