SQL VBA: Selecting all tables with specific table name and field name

Question

im working with access and VBA. As for now, I am trying to create a query with a SQL statement. I have a bunch of tables, all of them are named "innen" at the end and they vary at the start. Each of these tables contain the column name "OP" (also other field names). Now my goal is to select all tables with the name containing '%innen' and the column name "OP". So far i tried this:

Sub Aktuell()
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As QueryDef

    strSQL = "SELECT [*].OP FROM MSysObjects WHERE TABLE_NAME LIKE '%innen' ORDER BY MAX;"
    db.Execute strSQL

    Set qdf = CurrentDb.CreateQueryDef("NewQuery8", strSQL)
    DoCmd.OpenQuery qdf.Name

End Sub

i tried this here aswell:

strSQL = "SELECT * " & _
    "FROM INFORMATION_SCHEMA.TABLES " & _
    "WHERE COLUMN_NAME = 'OP_Datum';"

But i keep getting errors.

Any ideas? does it even work with a sql statement via vba?

In your question you wrote that you are looking for a column named `OP`, but your second query shows a search for column `OP_Datum`. Which one is correct? — Arminius, Aug 25 '17 at 07:49
Do you have read permission? Check https://stackoverflow.com/questions/1937703/record-cannot-be-read-no-read-permission-on-msysobjects — LS_ᴅᴇᴠ, Aug 25 '17 at 07:49
yea its actually OP_Datum, my bad haha. tried to make it simple as possible — U-Jean, Aug 25 '17 at 07:51
Do you need to use SQL? You could also use VBA and loop through all the TableDefs and then the fields of each TableDef... — Achim Gmeiner, Aug 25 '17 at 07:51
@LS_DEV i tried to give permission to read to the admin, but it says "missing object / object necessary" — U-Jean, Aug 25 '17 at 07:55
well guys, if you think its absolutely impossible to execute this via sql then i could try with vba. @ΦXocę 웃 Пepeúpa ツ the error is '3065' "a qry cannot be executed" — U-Jean, Aug 25 '17 at 07:58
You cannot `.execute`a SELECT-Query. Delete `db.Execute strSQL`, then the querydef will be created. Be aware that you cannot create a new querydef with same name as existing one. Delete querydef before creating the new one or use a temp querydef `Set qdf = db.CreateQueryDef("", strSQL)`. You can only read the table names from `MSysObjects`, but not the fields [how-can-i-get-a-list-of-fields-and-respective-tables-used-in-an-ms-access-2010](https://superuser.com/questions/436893/how-can-i-get-a-list-of-fields-and-respective-tables-used-in-an-ms-access-2010-d) — BitAccesser, Aug 25 '17 at 09:32

score 0 · Answer 1 · answered Aug 30 '17 at 02:15

Here is a VBA solution for you.

Option Compare Database

Function GetFieldList(TableName As String) As String()
  On Error GoTo Er
  Dim Flds() As String
  Dim fc As Long
  Dim I As Long

  'Initialize Dynamic Flds() Array
  Flds = Split("")
  fc = CurrentDb.TableDefs(TableName).Fields.Count - 1
  If fc >= 0 Then
    ReDim Preserve Flds(fc)
    For I = 0 To fc
      Flds(I) = CurrentDb.TableDefs(TableName).Fields(I).Name
    Next I
  End If
Done:

  GetFieldList = Flds
  Erase Flds
  Exit Function
Er:
  Resume Done

End Function

Sub flTest()
  Dim I As Long
  Dim Fields As Variant

  Fields = GetFieldList("Customers")
  If UBound(Fields) = -1 Then
    MsgBox "Table Not Found, or Table has no fields", vbCritical + vbOKOnly
    Exit Sub
  End If

  For I = LBound(Fields) To UBound(Fields)
    Debug.Print """" & Fields(I) & """"
  Next I


End Sub

I'll bet there is a way to so the same thing using nothing but SQL. Although, Access is a unique animal. You can do this using SQL Server. I'm not 100% sure Access can handle it. Well, why not try it and see for yourself.

SQL VBA: Selecting all tables with specific table name and field name

1 Answers1