aspose file tools*
The moose likes JDBC and the fly likes MS Access - how to read query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MS Access - how to read query" Watch "MS Access - how to read query" New topic
Author

MS Access - how to read query

rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
Suppose I've made MS Access database and want my Java application to read query objects from that database.
I'd like to get them as an array of Strings or as an collection of other type of objects.
How to get them?
I'm using JBuilder 4 Ent.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Do you mean you want to store Java objects in the database?
First can I say I disagree with storing objects in the database, then say you'll want to store them as BLOBs and read this article at the Sun site.

Dave.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

sounds like you need a complete lesson. Everything you need is here: http://java.sun.com/docs/books/tutorial/jdbc/index.html
just a little word of warning, when using the jdbcdbc bridge to connect to MSAccess, all the methods in the java.sql package are not implemented(which means it will give you an error such as "method not implemented" or such). It is probably safer to stick to the jdbc 1.0 API to start.
Jamie
(and I remembered to disable the smilies!)
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
Suppose I've made MS Access database with the following objects:
Three tables:
table_1
table_2
table_3
&
Two queries:
query_1
query_2
Then I'm making my Java applicatin and want it to read query_1 and query_2 in form of Strings (like regular SQL notation) from the MS Access database. How to do that?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

you would have to replicate the query you made in access in the java program. If your query was "SELECT COF_NAME, PRICE FROM COFFEES" on the table coffees then this is how you would retrieve the results of the query:

for the rest of the code to make a complete database application you can find the code here(that is where the code above came from): http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
Jamie
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
It's OK!
I undrstand that.
I just want something else.
OK if I can not get the Strings that represents SQL queries stored in MS Access database.
We can try something else.
How can I run (start) certain query in MS Access database. I suppose I should use command exec... ;but I still don't have list of queries objects stored in database. I even don't know how many of them there are.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Sorry, I thought you were just trying to run a query the database. If you want to know the contents of a Query object that you created in MSAccess you would have to hit the System tables (if they exist in Access???). Maybe someone else here knows how to query System tables in access?
my guess is that if these System tables exist, you won't have access to them like a full featured database(Oracle, DB2, etc.).
Can you upgrade databases to mySQL or postgreSQL (both free) or another full featured database?
Jamie
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
Currently I don't have installed any other database system on my computer. The only database server system I have is MS Access and that is the only one I must use because my client is using such.
I want to make general java application that will be able to read or even better to start any query found in the any MS Access database it can connect to, but I still can't find how many queries are in the appropriate database and what their names are.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Do you need to see these queries dynamically? are they always changing? are new ones being added all the time?
The only suggestion I can make is to open the MSAccess Query object, click on the "View" menu item and choose "SQL View". Then cut and paste this sql into your java app to replicate it using Statement/ResultSet. Is there any reason why this is not possible for you?
Why can't you just use the traditional SQL Query from java using a Statement/resultset?? These MSAccess queries are only saved SQL statements anyway.
I guess I just don't understand why you can't just use a statement/resultset in java instead of using the MSAccess Queries. Maybe you can explain why you need to do this to clarify things.
Jamie
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
OK, I'll explain my problem.
Note: I'm using JBuilder 4 Ent.
I want to make general Java application that will contain two tabs: TABLES and QUERIES. Each tab will have appropriate drop-down menu were user will be able to choose one of the tables (in TABLES tab) or one of the queries (in QUERIES tab). The result set he gets displayed in the grid will be completely updatable. This said so far is nothing different from using ordinary MS Access database directly.
Here comes business logic:
I want to track changes (updated, deleted, inserted rows) in any MS Access database. Every time the user click the "Save" button on the JbdNavigatorToolbar I want my application to save changes in appropriate .txt file. Each click on the "Save" button causes new .txt file to be generated.
I have done this easily with tables only, but what about queries?
And the most important: I want my application to be general as musch as posible; that means, this should be possible with any Access database.
p.s.
I have MS Access database at work, and the copy of it at home. My desire is to update my home database with changes only (updating with .txt files I made at work).
-----------------
I hope you understand me now. Sorry for my bad English.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

OK, now the picture is becoming clearer. I can't offer you the answer because I don't think it is possible. I can only suggest a work around:
can you create a separate table yourself and store all the querie SQL statements in a column there? This way you can populate your drop down box, you can update, delete, create new ones on the fly.
Problem:
you will be able to see the "query" table in your Tables drop down box. You can hard code this to not show up in the drop down list (if(!tablename.equals("query"){add to drop down list})
Am I getting a little warmer(at least warmer than an ice cube)?
Jamie
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I have MS Access database at work, and the copy of it at home. My desire is to update my home database with changes only (updating with .txt files I made at work).

How large is this .mdb? If you just want to Synchronize the Databases, why don't you just email a new copy to yourself every time you need to update it? (or if you have a burner, you could write it to the CD, and save it at home)
This is the easy way out
Jamie
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
Yes, I've been copying database all the time. But we are talking about making Java application here. Am I right?
I can not accept your advice to make separate "query" table because my application will not be general. It's gooooood idea, but that means that I'll have to update MS Access database first (create new table, or column only and enter SQL query strings) then to start my application. I mean I'll have to do such thing with evry new database I try to connect to. That way my application will not be so general (universal).
p.s.
I believe that SQL queries in MS Access database are already stored in some kind of a table, but what is the name of such table?
We are getting closer. Aren't we? ^2
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
Sorry for the first smilie.
I've triggered the wrong one.
I wanted to be
--------
p.s.
Don't get mad.
This is friendly forum I believe. That's why I came here.
I appreciate your interest for my problem.
Thank you!!!
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Finally! I think I have found what you need. Unfortunately I can't tell you how to use them so you'll still have some work to do to understand them:
To find the system tables:
- open MSAccess
- click 'Options' on the 'Tools' menu
- click the View tab
- select the System Objects check box.
Voila, you can see your System Tables. The information you need is here. It looks to me like you need to query the MSysObjects by the Query name to find the id. Once you have the id for that query you can query MSysQueries to rebuild the query (I believe it is stored in parts:
MSysQueries.Atrribute = 0 --> don't know what it is
MSysQueries.Atrribute = 5 --> looks like the table name that you are querying (...from tablename...)
MSysQueries.Atrribute = 6 --> looks like the columns to select on (select column, column2....)
MSysQueries.Atrribute = 8 --> looks like the where clause values
Anyways, it looks like you still have some work cut out for yourself, you may have to find an MSAccess DBA text book for the details if such a book exists
Jamie
[This message has been edited by Jamie Robertson (edited November 21, 2001).]
rasa rasa
Greenhorn

Joined: Nov 15, 2001
Posts: 9
Yes I've tried something and here is what I got:
Part of my code:
----------------
java.sql.Statement st = database1.getJdbcConnection().createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
java.sql.ResultSet rs = st.getConnection().getMetaData().getTables(null, null, null, null);
while (rs.next()){
jdbTextArea1.append(rs.getString("TABLE_NAME")+"\r\n");
}
jdbTextArea1.append(database1.getMetaData().getCatalogTerm()+"\r\n");
----------
here comes the output:
----------------------
MSysAccessObjects
MSysACEs
MSysObjects
MSysQueries
MSysRelationships
myTable1
myTable2
myQuery1
DATABASE
--------
As you can see I finally got what I was looking for.
Seems to be that JDBC driver for MS Access makes no difference between tables and queries. One of my frieds told me that there shouldn't be any diferences if I try the same thing with reports. Right now, i have to deal with master-detail relation ship between my tables (Table1 and Table2) to see if I get the right results in query table.
p.s.
In the previous code example you saw that I had made second connection to the database (the first was when I created database1 object).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MS Access - how to read query