query any table?

[YouMust]

Hi there I hope you can point me in the right direction,

We have a few hundered tables that have the same fields in, this was done by my predecessor.

Now, I want to run a query on a few of these tables as and when needed.
Could I not make just one query that has a pop up asking for the table to run the query?

I'm guessing this will have to be done in VBA VIA a form/button.
no way do this just with the query?

thanks in advance

 

[Minty]

You could list the tables in a list box, and use that to run the query. Something like

    Dim sTableName as String
    Dim sSql as String

    sTableName = Me.Yourlistbox
    
    sSql = "SELECT * FROM " & sTableName & " ;"
    
    On Error Resume Next
    Dim qdf As DAO.QueryDef
    DoCmd.DeleteObject acQuery, "strQRY"
    Set qdf = CurrentDb.CreateQueryDef("strQRY", sSql)
    DoCmd.OpenQuery "strQRY"

 

[Galaxiom]

Continuing to work with your predecessor's mistakes compounds the problem and is a total waste of time.

Your next step should be to restructure the data.

 

[YouMust]

your totally 100% right Galaxiom.

We are leaving MS access, the query is part of my data extraction.
The company has decided to use Globalshop solutions and that uses the pervasive database.

Not sure if thats the right move as its all very propriety.
But thats not my decision I just have to make it all work at the end of the day.

 

[Galaxiom]

You might use a Union query to get the data into one table but there is a limit on the number of unions that is far below your needs. You would still need to write that query which would be rather painful.

Your best approach is with VBA. You could loop through the Tabledefs Collection and generate an Insert query on each table. This is just the automated version of Minty's solution.

Hopefully there is a naming pattern you can use to choose which tables are to be included. Otherwise you could loop though the Fields Collection of the tables to check they conform to the pattern.