473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
Nov 12 '05 #1
8 2000
DFS
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" <jo*************@rogers.com> wrote in message
news:3f**************************@posting.google.c om...
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

Nov 12 '05 #2
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" <jo*************@rogers.com> wrote in message
news:3f**************************@posting.google.c om...
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

Nov 12 '05 #3


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!
Nov 12 '05 #4
jo*************@rogers.com (John Hardy) wrote in message news:<3f**************************@posting.google. com>...
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


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.
Nov 12 '05 #5

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!
Nov 12 '05 #6


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!
Nov 12 '05 #7

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!
Nov 12 '05 #8
DFS
"John Hardy" <jo*************@rogers.com> wrote in message
news:40***********************@news.frii.net...

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:
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.
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!

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

63
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy...
13
by: bill | last post by:
I am trying to convince a client that dotNet is preferable to an Access project (ADP/ADE). This client currently has a large, pure Access MDB solution with 30+ users, which needs to be upgraded....
1
by: Dave | last post by:
Hello NG, Regarding access-declarations and member using-declarations as used to change the access level of an inherited base member... Two things need to be considered when determining an...
13
by: Simon Bailey | last post by:
I am a newcomer to databases and am not sure which DBMS to use. I have a very simplified knowledge of databases overall. I would very much appreciate a (simplifed) message explaining the advantages...
0
by: Frederick Noronha \(FN\) | last post by:
---------- Forwarded message ---------- Solutions to Everyday User Interface and Programming Problems O'Reilly Releases "Access Cookbook, Second Edition" Sebastopol, CA--Neither reference book...
20
by: Olav.NET | last post by:
I am a .NET/C++ developer who is supposed to do some work with Access. I do not know much about it except for the DB part. Questions: *1* I am looking for INTENSIVE books to get quickly up to...
64
by: John | last post by:
Hi What future does access have after the release of vs 2005/sql 2005? MS doesn't seem to have done anything major with access lately and presumably hoping that everyone migrates to vs/sql. ...
1
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ......
17
by: Mell via AccessMonster.com | last post by:
Is there a way to find out where an application was created from? i.e. - work or home i.e. - if application sits on a (work) server/network, the IT people know the application is sitting...
37
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.