Can you prompt for the table name in a query

[Stoffel24]

Hi

I am trying to write a query in Access that will work for several different tables. The SQL code appears below:

SELECT [x].Table, [x].Field_seq, [x].Field_name, [x].Field_type, [y].Field_type
FROM x INNER JOIN y ON ([x].Field_name = [y].Field_name) AND ([x].Table = [y].Table)
WHERE ((([y].Field_type)<>[x].[Field_type]))
ORDER BY [x].Table, [x].Field_seq;

Where you see x and y are the names of the tables. Ideally, when attempting to run the query, you will be prompted to choose the name of one of the tables from the database. Alternatively, it could just prompt for a name and the user will have to know what the name of the table is and enter this.

I would be happy to try doing this with VBA (I have used Excel VBA quite a bit but never Access) but any solutions or suggestions would be gratefully recieved. Thanks a lot.

 

[xlbo]

Well, SQL is basically just a string when executed via code
You can use the Querydef object as your query. One of the parameters is the SQL parameter (which is a string)
Ergo, you should be able to replace your table names with variables like

SQLStr = &quot;SELECT [&quot; & x & &quot;].Table, [&quot; & x & &quot;].Field_seq, [&quot; & x & &quot;].Field_name, [&quot; & x & &quot;].Field_type, [&quot; & y & &quot;].Field_type FROM &quot; & x & &quot;INNER JOIN &quot; & y & &quot;ON ([&quot; & x & &quot;].Field_name = [&quot; etc etc etc

Then use the querydef object to execute the query and return a recordset. Here's the example from Access help:

CreateQueryDef Method Example

This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX()

Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim qdfNew As QueryDef

Set dbsNorthwind = OpenDatabase(&quot;Northwind.mdb&quot

With dbsNorthwind
' Create temporary QueryDef.
Set qdfTemp = .CreateQueryDef(&quot;&quot;, _
&quot;SELECT * FROM Employees&quot
' Open Recordset and print report.
GetrstTemp qdfTemp
' Create permanent QueryDef.
Set qdfNew = .CreateQueryDef(&quot;NewQueryDef&quot;, _
&quot;SELECT * FROM Categories&quot
' Open Recordset and print report.
GetrstTemp qdfNew
' Delete new QueryDef because this is a demonstration.
.QueryDefs.Delete qdfNew.Name
.Close
End With

End Sub




Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes