Check if a Query exists in MS Access from Excel VBA

Question

The following function works fine for finding tables in an MS Access database through the standard new connection and recordset **but it does not find queries or linked tables.

Function CHKtablename(TABLECHK As String) As Boolean
Dim conn As New Connection
Dim rs As New Recordset
Dim strconn As String
Dim qry As String
Dim chk As Boolean 
strconn = "provider=Microsoft.Ace.Oledb.12.0;" & " Data source= Source path" & "user id=admin;password=" 
conn.Open(strconn) 
Set rs = conn.Openschema(adschematables) 
    While Not rs.EOF
        If rs.Fields("Table_Name") = TABLECHK Then
            CHKtablename = True
        End If
        rs.Movenext
    Wend
End Function

How can I change this to find them?

I appreciate your time and help.

Have you tested this code? seems to me it wouldn't compile, and would have run-time errors. What dose rs open? Where does it do that? — marlan
– marlan, Commented Feb 24, 2019 at 16:03
I'm sorry I will clarify this is not the complete code I will edit and add the remaining wait a moment — Aeon
– Aeon, Commented Feb 24, 2019 at 16:09
Posted code still shows errors that should not be in compiled code. Such as lack of space after function. Yet you say it works fine. Use QueryDefs to verify if query exists. — June7
– June7, Commented Feb 24, 2019 at 18:00
Well I am writing this on my phone so it's hard to make the code exactly right I'm terribly sorry, but I however it does work to find tables — Aeon
– Aeon, Commented Feb 24, 2019 at 18:11

June7 · Accepted Answer · 2019-06-15 09:54:01Z

2

Would be nice if could query MSysObjects table but that is unreliable outside Access because of permission issue. It failed for me.

Set a VBA reference to Microsoft Office x.x Access Database Engine Library.

One approach uses QueryDefs collection. Tested and works for me. However, both files are on laptop in same user folder.

Sub CHKqueryname()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = DBEngine.OpenDatabase("C:\Users\June\LL\Umpires.accdb")
For Each qd In db.QueryDefs
    If qd.Name = "GamesSorted" Then
        Debug.Print qd.Name
        Exit Sub
    End If
Next
End Sub

If you want to avoid QueryDefs, try error handler code:

Sub Chkqueryname()
    On Error GoTo Err:
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = DBEngine.OpenDatabase("C:\Users\June\LL\Umpires.accdb")
    Set rs = db.OpenRecordset("query name")
    rs.MoveLast
    Debug.Print rs.RecordCount
Err:
    If Err.Number = 3078 Then MsgBox "query does not exist"
End Sub

For ADODB version, set reference to Microsoft ActiveX Data Objects x.x Library.

Sub CHKqueryname()
    On Error GoTo Err:
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\June\LL\Umpires.accdb'"
    rs.Open "query name", cn, adOpenStatic, adLockReadOnly
    Debug.Print rs.RecordCount
Err:
    If Err.Number = -2147217900 Then MsgBox "query does not exist"
End Sub

edited Jun 15, 2019 at 9:54

answered Feb 24, 2019 at 19:41

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Aeon Over a year ago

I tried your solution I get a error 3343 unrecognized dB format checked the path and dB name and its correct I'm using excel 2016 and access 2016 code breaks on set Set db = DBEngine.OpenDatabase("source path")

June7 Over a year ago

I have Access 2010 and all of that code works for me. So replace "source path" with a valid path as shown in my example.

Aeon Over a year ago

Well yes its a valid path I just used source path as a place holder, and I I WI try your other options tomorrow thank you so much for all the time you put into helpijg me so far!

Aeon Over a year ago

Adodb version works for me I used it thank you so much for the help

June7 Over a year ago

@ComputerVersteher, Maybe OP will see your comment. They asked for method not using QueryDefs. See their last comment below question.

|

Collectives™ on Stack Overflow

Check if a Query exists in MS Access from Excel VBA

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related