CreateQuery Database Code

[Guest]

Hello all,
I have found a nice bit of code from
'**********************************
'Created by Roger Carlson *
'(e-mail address removed)*
'(e-mail address removed) *
'**********************************
it provides the user the ability to create on the fly queries from a form.
It works well when the tables are in the same database as the form.
Unfortunately, I have a FE and BE.
Can someone help me modify this code to work with LINKED tables?
OR
I can have the form reside on the BE database, but how can I open the BE
form from the FE database?

Here is a line of code on the first cbo box to select the source of the query.
*******************************************************8
SELECT DISTINCT "Table: " & MSysObjects.Name , MSysObjects.Name AS Expr1
FROM MSysObjects WHERE (((MSysObjects.Name) Like "tbl*") AND
((MSysObjects.Type)=1)); UNION SELECT DISTINCT "Query: " & MSysObjects.Name
, MSysObjects.Name AS Expr1 FROM MSysObjects WHERE (((MSysObjects.Name) Like
"qsel*") AND ((MSysObjects.Type)=5));
*******************************************************
Here is the code in the AFTER UPDATE event of this cbo box.
*****************************************************
Dim strsql As String
strsql = "SELECT DISTINCT " & cboFieldName & " FROM " & cboTable & ";"

'MsgBox strSQL
Me!cboCriteria.RowSource = strsql
Me!cboCriteria.Requery
Me!txtFieldType = cboFieldName.Column(1)
****************************************************

Any assistance you can provide is very much appreciated.

David

 

[MacDermott]

This should work fine with linked tables, just as long as your linked tables
are all named starting with "tbl".

 

[Guest]

MacDermott,
Unfortunately, it doesn't. I think the problem is with the code for the
record source of the cbo box. It is only pulling back tables in the database
that start with tbl and not linked tables in the database that start tbl.

I started with Roger's database. Imported a table called tblEmployee and
linked a table called tblEmployee1. The cbo box displays the tblEmployee
table, but not the tblEmployee1 linked table.
I have also tried changing the ((MSysObjects.Type)=1)); to = 2 or 3 or 4 or
5 or 6, with no luck.

Can I place this form on the backend database and open the form from the
front end database?

 

[Guest]

PLEASE DISREGARD, I found a fix.

The fix was to create queries that are just SELECT ALL for the tables I want
to view. The queries section in the code below work fiine.

This is why I love Access, there seems to be a dozen ways to accomplish a
single task.

 

[MacDermott]

In my (A2K) copy of MSysObjects, linked tables are Type=6; native ones are
Type=1.