supermanhamuerto.com

User Tools

Site Tools

Trace:
projects:microsoftaccessandjava

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
projects:microsoftaccessandjava [2010/12/25 16:00] – rlunaroprojects:microsoftaccessandjava [2022/12/02 21:02] (current) – external edit 127.0.0.1
Line 2: Line 2:
  
 ===== Background ===== ===== Background =====
 +
 +I have tons of msAccess databases, each of one runs a query. All of the queries are action queries, i.e. queries that dump its results into a new table, that must be erased each time. 
 +
 +I need to run all the queries in an ordered manner (some queries depend on others and so on..), so I quickly thought on an updating application that should be done in order to achieve the task.
 +
 +For this task, and because I am orienting muy development skills through Java, I want to try to do this in java. 
 +
 +Let's see if it possible. 
 +
 +===== Result of the project =====
 +
 +
 +**Failed:** it is not possible to run an action query from java. Nor to execute a select * into table from query it is possible, because the new table appears empty (¿due to security constraints???). 
 +
 +**Pending:** to try it with access 2007, just to check if this will work in a newer database version. But I suspect that there is no difference. 
  
  
Line 14: Line 29:
 The sql engine of access is called Jet SQL. Here is a manual:  The sql engine of access is called Jet SQL. Here is a manual: 
  
-http://www.devguru.com/technologies/jetsql/quickref/jet_sql_intro.html+http://www.devguru.com/technologies/jetsql/home
  
 +{{:projects:jet_database_engine_reference_manual.odt|Download a manual taken from Microsoft website}}
  
-===== How to manipulate a Microsoft Access Database via JDBC =====+There is the manual online: 
  
 +  * [[http://technet.microsoft.com/en-us/library/cc966378.aspx]]
 +
 +
 +===== How to manipulate a Microsoft Access Database via JDBC =====
  
-| I am reproducing here literally the article I've found in Planet Source Code | +** 
-| submitted by [[http://www.planet-source-code.com/vb/authors/ShowBio.asp?lngAuthorId=3320781122&lngWId=2|Jef Patterson]]|+I am reproducing here literally the article I've found in Planet Source Code 
 +submitted by [[http://www.planet-source-code.com/vb/authors/ShowBio.asp?lngAuthorId=3320781122&lngWId=2|Jef Patterson]] 
 +**
  
   
Line 27: Line 49:
  
 Sorry if the formatting is a little screwed up on this - PlanetSourceCode seems to modify my HTML just a little when I upload it...it should still all be readable enough... Sorry if the formatting is a little screwed up on this - PlanetSourceCode seems to modify my HTML just a little when I upload it...it should still all be readable enough...
- 
  
  
 If you find this useful, please vote for me! If you find this useful, please vote for me!
  
-How to manipulate a Microsoft Access Database via JDBC +==== How to manipulate a Microsoft Access Database via JDBC ==== 
-and it's also + 
-A Super Quick Overview of JDBC Basics+**and it's also** 
 + 
 +====A Super Quick Overview of JDBC Basics====
  
 This will teach you how to connect to a Microsoft Access database. Once you are connected, you may run any SQL statement that is allowable on Access, such as: This will teach you how to connect to a Microsoft Access database. Once you are connected, you may run any SQL statement that is allowable on Access, such as:
Line 43: Line 66:
     * an CREATE TABLE statement to build a new table     * an CREATE TABLE statement to build a new table
     * a DROP TABLE statement to destroy a table      * a DROP TABLE statement to destroy a table 
- 
  
  
Line 51: Line 73:
  
 There are three things we need to do to manipulate a MS Access database: There are three things we need to do to manipulate a MS Access database:
-1) Set up Java to undestand ODBC, + 
-2) Get a connection to our MS Access Database, +  - Set up Java to undestand ODBC, 
-3) Run a SQL statement.+  - Get a connection to our MS Access Database, 
 +  - Run a SQL statement.
  
 1) First we need to set up Java to understand how to communicate with an ODBC data source 1) First we need to set up Java to understand how to communicate with an ODBC data source
  
-    * Set up your DriverManager to understand ODBC data sources+    * [[microsoftaccessandjava#Set up your DriverManager to understand ODBC data sources|Set up your DriverManager to understand ODBC data sources]]
  
  
Line 64: Line 87:
 There are two ways to get a connection from your Microsoft Access Database: There are two ways to get a connection from your Microsoft Access Database:
  
-   1. Get a connection by accessing the Database Directly +  - Get a connection by accessing the Database Directly 
- +  - The simpler way, but may not work on all systems! Set the Access Database up as an ODBC DSN and get a connection through that.A little more complex, but will work on any system, and will work even if you don't already have a Microsoft Access Database! 
-   2. The simpler way, but may not work on all systems! Set the Access Database up as an ODBC DSN and get a connection through that +
- +
-      A little more complex, but will work on any system, and will work even if you don't already have a Microsoft Access Database!  +
  
 3) Once you have gained access to the Database (been granted a connection), you are ready to try: 3) Once you have gained access to the Database (been granted a connection), you are ready to try:
Line 76: Line 95:
  
       This is the section that you will be most interested in - if you're impatient, you might want to start here...but please come back and read it all!        This is the section that you will be most interested in - if you're impatient, you might want to start here...but please come back and read it all! 
- 
- 
- 
- 
  
 In addition, please refer to the section at the end of this document: In addition, please refer to the section at the end of this document:
  
-    * What I assume you already know +    * "What I assume you already know" plus a little additional reading. 
- +
-      Plus a little additional reading.  +
  
  
-Step 1) Set up your DriverManager to understand ODBC data sources  BACK TO TOP+=== Set up your DriverManager to understand ODBC data sources ===
  
 The first thing we must do in order to manipulate data in the database is to be granted a connection to the database. This connection, referenced in the Java language as an Object of type java.sql.Connection, is handed out by the DriverManager. We tell the DriverManager what type of driver to use to handle the connections to databases, and from there, ask it to give us a connection to a particular database of that type. The first thing we must do in order to manipulate data in the database is to be granted a connection to the database. This connection, referenced in the Java language as an Object of type java.sql.Connection, is handed out by the DriverManager. We tell the DriverManager what type of driver to use to handle the connections to databases, and from there, ask it to give us a connection to a particular database of that type.
projects/microsoftaccessandjava.1293292858.txt.gz · Last modified: 2022/12/02 21:02 (external edit)