SQL in Access 97

I have the following SQL code in my databse as a querry.

SELECT Sheet1.ID, Sheet1.Field6, Sheet2.ID, Sheet2.Field6
FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.ID = Sheet2.ID
WHERE ((([Sheet2]![Field6])<>[Sheet1]![Field6] Or ([Sheet2]![Field6])
Is Null));

Which does what I want. But I would prefer rather then hard code the
table name I would rather a dialog box pop up and ask for a table name
or browse for one. I have no idea how to do this with SQL. Any help
would be appreciated. I am not a programmer but I am writing something
I need done and taking a long time doing it, so answer as layman as
possible or with examples if you can.

Much appreciated!

Thanks

John

re: SQL in Access 97

John,

First a little advice:

* don't name your tables Sheet# and don't name your fields Field#. If
you're doing this for the example, fine, but your actual system should use
meaningful table and field names. I recommend you rename/restructure, then
post your question again. I have a feeling you have multiple tables with
similar data in them, and you probably shouldn't.

The base Access query model won't do what you want, which is run a query and
prompt for the table name and dynamically use it in the SQL. But you can
write a little VB code that will do it for you:


Public Sub updateQuery()

dim db as database, qItem as QueryDef
dim cSQL as string, tblName1 as string, tblName2 as string
set db = currentdb()

tblName1 = InputBox("Enter table 1 name", "")
tblName2 = InputBox("Enter table 2 name", "")

if isnull(tblName1) or isnull(tblName2) then
msgbox "Enter both table names"
exit sub
endif

cSQL = SELECT [" & tblName1 & "].ID, [" & tblName1 & "].Field6, [" &
tblName2 & "].ID, [" & tblName2 & "].Field6 "
CSQL = CSQL & "FROM [" & tblName1 & "] INNER JOIN [" & tblName2 & "] ON [" &
tblname1 & "].ID = [" & tblName2 & "].ID "
CSQL = CSQL & "WHERE [" & tblName2 & "].Field6 <> [" & tblName1 & "].Field6
OR [" & tblName2 & "].Field6 IS NULL;"

Set qItem = db.QueryDefs("query name")
qItem.sql = cSQL
qItem.Close
docmd.openQuery "query name"

End Sub

I think after you look at, and restructure, your database you'll find out
something like this is unnecessary.



"John Hardy" <john.hardy.7715@rogers.com> wrote in message
news:3f4d8b08.0401261536.4f0e1c23@posting.google.c om...[color=blue]
> I have the following SQL code in my databse as a querry.
>
> SELECT Sheet1.ID, Sheet1.Field6, Sheet2.ID, Sheet2.Field6
> FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.ID = Sheet2.ID
> WHERE ((([Sheet2]![Field6])<>[Sheet1]![Field6] Or ([Sheet2]![Field6])
> Is Null));
>
> Which does what I want. But I would prefer rather then hard code the
> table name I would rather a dialog box pop up and ask for a table name
> or browse for one. I have no idea how to do this with SQL. Any help
> would be appreciated. I am not a programmer but I am writing something
> I need done and taking a long time doing it, so answer as layman as
> possible or with examples if you can.
>
> Much appreciated!
>
> Thanks
>
> John[/color]

re: SQL in Access 97

I'd fear that, if you have many tables with such similar data, you are
"using the table name as data", a violation of relational database design
principles, and there's a significant chance that you're making your work
more difficult. If it hasn't already, it's likely to do so in the future.

If you'd clarify what data you have and what you are trying to accomplish,
there's a good chance that someone might be able to offer suggestions for
accomplishing it in another, simpler way.

That said, it would certainly be possible to create a form with Combo boxes
listing the tables in your database from which the user could choose the
ones to be compared and from which data should be extracted. The VBA code
would not be overly difficult.

First, post a response here toc clarify -- maybe we can help you make it
simpler. Then, if no one can do that, perhaps someone can address the VBA
approach I mentioned.

Larry Linson
Microsoft Access MVP


"John Hardy" <john.hardy.7715@rogers.com> wrote in message
news:3f4d8b08.0401261536.4f0e1c23@posting.google.c om...[color=blue]
> I have the following SQL code in my databse as a querry.
>
> SELECT Sheet1.ID, Sheet1.Field6, Sheet2.ID, Sheet2.Field6
> FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.ID = Sheet2.ID
> WHERE ((([Sheet2]![Field6])<>[Sheet1]![Field6] Or ([Sheet2]![Field6])
> Is Null));
>
> Which does what I want. But I would prefer rather then hard code the
> table name I would rather a dialog box pop up and ask for a table name
> or browse for one. I have no idea how to do this with SQL. Any help
> would be appreciated. I am not a programmer but I am writing something
> I need done and taking a long time doing it, so answer as layman as
> possible or with examples if you can.
>
> Much appreciated!
>
> Thanks
>
> John[/color]

re: SQL in Access 97

The sheet number and fields are only temporary and the reason why I want
to be able to choose the names.

Once I have everything working properly I will go back and clean
everything up. There are only two tables at the moment and they are
sheet1 and 2 and I keep modifying them just to run tests. The program
simply brings 2 .xls files from Excel and compares them in these tables
and then exports the updated one back to excel. I just wanted to be able
to name the tables the same as the file that are imported and have a
copy of that file in the DB format which would eventually be deleted
once I am sure it is no longer needed. It's just a small automation
program. But it's been a real challenge for an amature like myself.

I will try your code out. Thanks for your help.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: SQL in Access 97

john.hardy.7715@rogers.com (John Hardy) wrote in message news:<3f4d8b08.0401261536.4f0e1c23@posting.google. com>...[color=blue]
> I have the following SQL code in my databse as a querry.
>
> SELECT Sheet1.ID, Sheet1.Field6, Sheet2.ID, Sheet2.Field6
> FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.ID = Sheet2.ID
> WHERE ((([Sheet2]![Field6])<>[Sheet1]![Field6] Or ([Sheet2]![Field6])
> Is Null));
>
> Which does what I want. But I would prefer rather then hard code the
> table name I would rather a dialog box pop up and ask for a table name
> or browse for one. I have no idea how to do this with SQL. Any help
> would be appreciated. I am not a programmer but I am writing something
> I need done and taking a long time doing it, so answer as layman as
> possible or with examples if you can.
>
> Much appreciated!
>
> Thanks
>
> John[/color]

there are various ways of showing a list of tables in a combobox...
then you could have a temporary querydef and execute it... Where are
the tables you are referring to? Are they Excel sheets that you are
linking to? You can use the code here...
http://www.mvps.org/access/api/api0001.htm ... to prompt the user to
search for a non-Access table... then you'd have to manipulate the
SQL property of the querydef (Access query) in code, save the changes,
and then open the query. If you're not a programmer, then this will
be an uphill battle, because as far as I know, there's no way to
specify a table you want to query on the fly without some kind of code
to build the SQL statement and then assign that string as the SQL
property of a QueryDef (query definition). I could be wrong, but I
don't think so.

re: SQL in Access 97

I get a compile error in your SQL statement. Says to the left of SELECT?
I guess it's a parenthese error, but I don't know SQL well enough to
know what's causing it.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: SQL in Access 97

That's what it was. Now I just have to play with it a bit :)




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: SQL in Access 97

I think your code is going to do it for me. You see the tables are
already there.They were created with the file imported from Excel. So
all I want to do is enter the name of the tables, select them from a
list box would be great! then run the SQL querry on them.

So your code ask me for the table names which I enter and all seems to
work fine up 'till this point:

Set qItem = dbs.QueryDefs("query name") ** What is this doing? **
qItem.sql = cSQL
qItem.Close
docmd.openQuery "query name"

This is where the code jambs and gives a runtime error 3265 - item not
found?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: SQL in Access 97

"John Hardy" <john.hardy.7715@rogers.com> wrote in message
news:4015f97c$0$70306$75868355@news.frii.net...[color=blue]
>
> I think your code is going to do it for me. You see the tables are
> already there.They were created with the file imported from Excel. So
> all I want to do is enter the name of the tables, select them from a
> list box would be great! then run the SQL querry on them.
>
> So your code ask me for the table names which I enter and all seems to
> work fine up 'till this point:[/color]

Create a query, using a valid SELECT statement, and save it as "TempQuery."
In the following code, substitute "TempQuery" for "query name". The code
updates the SQL statement and opens the query results in view mode.
[color=blue]
> Set qItem = dbs.QueryDefs("query name") ** What is this doing? **
> qItem.sql = cSQL
> qItem.Close
> docmd.openQuery "query name"
>
> This is where the code jambs and gives a runtime error 3265 - item not
> found?[/color]



[color=blue]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]