Microsoft SQL Server
Crystal Reports
Delphi, C etc
Perl and the DBI
Unix Shell Scripts
Visual Basic
Corel Paradox
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish Plugins
Project jMaki
GlassFish WebTier
Java Development Tools
Java WS & XML Community News
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Mobile Developer Alliance
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Bunkhouse Porch
Teachers' Lounge
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Other Languages
Application Frameworks
Other Open Source Projects
Other Java Products and Servers
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
IntelliJ IDEA
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Reviews / Advertising
Suggestions & Feedback


Java Database
Software Development
Software Development



Database access, JDBC/ODBC, could even brush on some SQL

MS Access - how to read query

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.

      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.


      sounds like you need a complete lesson.  Everything you need is here: a little word of warning, when using the jdbc:odbc 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!)


      Suppose I've made MS Access database with the following objects:Three tables: table_1table_2table_3&Two queries:query_1query_2Then 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?


      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:code:String query = "SELECT COF_NAME, PRICE FROM COFFEES";ResultSet rs = stmt.executeQuery(query);while ({String s = rs.getString("COF_NAME");float n = rs.getFloat("PRICE");System.out.println(s + "   " + n);}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):


      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.


      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


      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.


      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


      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.


      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


      quote: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 outJamie


      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


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


      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 isMSysQueries.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 valuesAnyways, 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 existsJamie[This message has been edited by Jamie Robertson (edited November 21, 2001).]


      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 ({    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).

Related Links

error in connectivity with database
How to get ResultSet without jdbc connection
"select top * ..." with Access
Cancel JDBC Connection when Browser Cancel
Bad practice with datasource?
Problem with DataSource lookup through java client
Result set not showing the latest data
which lock should be used here ?
delete & select subquery.. giving me tough time.. plz hlep
A Tricky Stored Procedure
Getting totals of rows in SQL
which connection pooling to use?
Jdbc/Mysql dropped tcp connections
Connecting to two different dbs in the same application
Problem with preapred Statement
Oracle performance hit for 1 million record