Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 13 of 13

Thread: Connecting to Access 2007 Database

  1. #1
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Connecting to Access 2007 Database

    Can anyone give me some sample code and explanation for how to correctly make a connection, read data, and write data to an Access 2007 Database (.accdb). I have been searching the internet all morning and can't find any advice that actually works. I am not using Netbeans, nor do I want to, so going through those tutorials don't do me any good.

    All I'm looking for is to create a connection to a database directly local to the executing code (same folder) and be able to add and read data from it. Should be fairly simple, but I haven't gotten anything to connect to anything. I have no real code to show, since I have about a thousand different things I'm trying and none of it is working.

    Thanks in advance.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/


  2. #2
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,302
    Thanks
    181
    Thanked 824 Times in 767 Posts
    Blog Entries
    5

    Default Re: Connecting to Access 2007 Database

    Never had to connect to Access, but typically you need the JDBC driver to create a database connection (sun has a tutorial online for JDBC basics). A quick google turned up suggests in other locations that you need a JDBC-ODBC bridge (see this for example - there's also an open source library available that is linked in that post). Hope this helps at least a teeny bit

  3. #3
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    Ok, so I have gone leaps and bounds with this. My problem wasn't with JAVA but with drivers and all that other stupid crap.

    Now I have a query question that may be easier to answer.

    I have several columns (and tables) whose name is multiple words. For example: Oct 2009. How do I query that value?

    This statement:
    boolean foundResults = statement.execute("SELECT Market, Oct 2010, Oct 2009 FROM REVPAXperDEPT");
    Throws an error because it doesn't know what to do with the Oct 2010 value.

    When I tried this (hoping perhaps quotes would work):
    boolean foundResults = statement.execute("SELECT Market, 'Oct 2010', 'Oct 2009' FROM REVPAXperDEPT");
    It didn't query those columns, but instead set Oct 2010 as the value in the ResultSet I got.

    When I tried this(and changed the database accordingly):
    boolean foundResults = statement.execute("SELECT Market, Oct2010, Oct2009 FROM REVPAXperDEPT");
    it worked. But, I do not want to change all of my column heads and there are other complications from changing those. Is there any way to query a multi-word column?
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  4. #4
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,302
    Thanks
    181
    Thanked 824 Times in 767 Posts
    Blog Entries
    5

    Default Re: Connecting to Access 2007 Database

    Try surrounding the column name with double quotes
    boolean foundResults = statement.execute("SELECT Market, \"Oct 2010\", \"Oct 2009\" FROM REVPAXperDEPT");

  5. The Following User Says Thank You to copeg For This Useful Post:

    aussiemcgr (December 10th, 2010)

  6. #5
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    Ah, worked like a charm. Cheers mate.

    I imagine I will run into more problems along the way and I'll post questions as they occur.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  7. #6
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    Ok, here are 2 Database questions again.

    How do I add columns to my database tables?

    Also, on that note, I understand how to insert new rows, but how do I insert new rows in alphabetical order (based on the text in the first column)?
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  8. #7
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,302
    Thanks
    181
    Thanked 824 Times in 767 Posts
    Blog Entries
    5

    Default Re: Connecting to Access 2007 Database

    I'd recommend browsing something like W3 Schools SQL. Shouldn't take too long to learn the basic queryformat to accomplish things like this as the syntax is fairly straightforward (the queries can get complex really quick however). To add a column, you call
    ALTER TABLE mytablename ADD mycolumname datatypes
    I'd recommend not necessarily worrying about inserting in order but just use the ORDER BY in your select query
    SELECT my_column FROM my_table WHERE my_column=my_value ORDER BY my_column

    As long as you are not selecting huge volumes of data and/or the tables are indexed appropriately the ORDER BY shouldn't increase query duration noticeably.

  9. #8
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    Quote Originally Posted by copeg View Post
    I'd recommend browsing something like W3 Schools SQL. Shouldn't take too long to learn the basic queryformat to accomplish things like this as the syntax is fairly straightforward (the queries can get complex really quick however). To add a column, you call
    ALTER TABLE mytablename ADD mycolumname datatypes
    I'd recommend not necessarily worrying about inserting in order but just use the ORDER BY in your select query
    SELECT my_column FROM my_table WHERE my_column=my_value ORDER BY my_column

    As long as you are not selecting huge volumes of data and/or the tables are indexed appropriately the ORDER BY shouldn't increase query duration noticeably.
    When using the ORDER BY query, is there a way to single out one specific value and make it last? I ask because I have 200 or so rows of data, each with a unique name, and then at the end a row named Total. That total row needs to be last every time. Any thoughts on how to do that?


    Also, I am getting an error retrieving data from the database. I am retrieving numbers from a database which are, for whatever reason, in scientific notation. As it turns out, when it reads in the values from the database, it is not reading in the whole number, just the E-2. Is there any way to fix this?

    I am reading in the number with the ResultSet.getString(int) method and attempting to parse it. I have tried using the ResultSet.getBigDecimal(int) method, but that throws the following error:
    java.lang.NumberFormatException
    	at java.math.BigDecimal.<init>(BigDecimal.java:505)
    	at java.math.BigDecimal.<init>(BigDecimal.java:728)
    	at sun.jdbc.odbc.JdbcOdbcResultSet.getBigDecimal(JdbcOdbcResultSet.java:1957)
    	at RunDB.executeChangeQuery(RunDB.java:206)
    	at RunDB.<init>(RunDB.java:58)
    	at CPLFProgram.main(CPLFProgram.java:17)


    Since the problem has actually merged with my other current issues, I'm going to cross-post this with the topic I created for the parsing scientific number the other day: Parsing Scientific Notation from String
    Last edited by aussiemcgr; December 14th, 2010 at 08:45 AM.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  10. #9
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,302
    Thanks
    181
    Thanked 824 Times in 767 Posts
    Blog Entries
    5

    Default Re: Connecting to Access 2007 Database

    When using the ORDER BY query, is there a way to single out one specific value and make it last? I ask because I have 200 or so rows of data, each with a unique name, and then at the end a row named Total. That total row needs to be last every time. Any thoughts on how to do that?
    Several ways to go about this, the first and more proper being that total should not be a row (and if it is should be a row in a different table - one of my pet-peeves with Access because it behaves visually like an excel spreadsheet and as a result sometimes can be treated as such, but a relational database is not). You can get the total of all columns readily by using the SUM sql function. Some quick hacks would be to select everything except the total (I presume the total row can somehow be distinguished so in the WHERE clause us a NOT to exclude this row), then query the total separately (or just get the SUM dynamically); select everything and sort/parse in java.

    Still not quite sure about the scientific notation. What data type is the scientific notation in the database (varchar, float, text, etc...)?

  11. #10
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    The Total row is actually not calculated from the other rows. It is data configured from the rows and other things that we retrieve when we do a data pull for the rest of the rows. For that reason, it is actually supposed to be a row.

    The data type in the scientific notation is a Number, to my knowledge. Thats what it says anyway in Access. If there is something more specific, how would I go about finding that? To create the tables originally, we simply imported Excel sheets from the Workbook where we were storing our data.

    I suspected that the empty cells we have, combined with the scientific notation, were causing problems, so I tried to do what I could to reduce as much of that as possible. I wrote this piece of code:
    double ov = 0;
    					   		double nv = 0;
    					   		try{
    					   			ov = rs.getDouble(2);
    					   		}catch(Exception ex){; 		}
    					   		try{
    					   			nv = rs.getDouble(3);
     
    					   		}catch(Exception ex){;}

    So, naturally, that should theoretically deal with most of our issues, which it seems to have. But, there is something very interesting I just noticed.

    On the output, some of the double values are the scientific notation exponents instead of their values. That doesn't make any sense because if there was any problem parsing the values, they should have just been represented with 0. And if they were in fact Strings, they would not be able to be stored in a double variable.

    For example, in my database, the a cell whose value reads: 6.45161290322581E-02 has a value of E-2 in my output.

    Any thoughts?
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  12. #11
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    The Total row is actually not calculated from the other rows. It is data configured from the rows and other things that we retrieve when we do a data pull for the rest of the rows. For that reason, it is actually supposed to be a row.

    The data type in the scientific notation is a Number, to my knowledge. Thats what it says anyway in Access. If there is something more specific, how would I go about finding that? To create the tables originally, we simply imported Excel sheets from the Workbook where we were storing our data.

    I suspected that the empty cells we have, combined with the scientific notation, were causing problems, so I tried to do what I could to reduce as much of that as possible. I wrote this piece of code:
    double ov = 0;
    					   		double nv = 0;
    					   		try{
    					   			ov = rs.getDouble(2);
    					   		}catch(Exception ex){; 		}
    					   		try{
    					   			nv = rs.getDouble(3);
     
    					   		}catch(Exception ex){;}

    So, naturally, that should theoretically deal with most of our issues, which it seems to have. But, there is something very interesting I just noticed.

    On the output, some of the double values are the scientific notation exponents instead of their values. That doesn't make any sense because if there was any problem parsing the values, they should have just been represented with 0. And if they were in fact Strings, they would not be able to be stored in a double variable.

    For example, in my database, the a cell whose value reads: 6.45161290322581E-02 has a value of E-2 in my output.

    Any thoughts?
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

  13. #12
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,302
    Thanks
    181
    Thanked 824 Times in 767 Posts
    Blog Entries
    5

    Default Re: Connecting to Access 2007 Database

    The Total row is actually not calculated from the other rows. It is data configured from the rows and other things that we retrieve when we do a data pull for the rest of the rows. For that reason, it is actually supposed to be a row.
    Which means that perhaps it should be a row, but perhaps not in this particular table Database Normalization 101.

    The data type in the scientific notation is a Number, to my knowledge. Thats what it says anyway in Access. If there is something more specific, how would I go about finding that?
    See SQL Data Types, which Number does not seem to be a member of. How you get the actual data type I don't know as I'm not familiar with the Access 2007 syntax to do so.

  14. #13
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,660
    Thanks
    25
    Thanked 325 Times in 302 Posts

    Default Re: Connecting to Access 2007 Database

    Quote Originally Posted by copeg View Post
    Which means that perhaps it should be a row, but perhaps not in this particular table Database Normalization 101.
    Well the database is designed where we have 16 different categories of data for 285 things. Each category of data holds monthly data for each thing, going back to January 2005.

    So we have it designed with 16 tables. Each table has columns which represent the date and rows which represent a thing and its data for each time period.

    Then the data is put into an Excel sheet (same format as database), average columns are added at the end that average the two most recent years for each month, then relationship average rows are added to the bottom to average each thing based on their relationship for each category in each time period (and those have yearly averages for the most recent months). Then, 2 giant Excel sheets are made: 1) that is a report breakdown for each thing and its statistics for the past 5 years for each month and quarter. 2) that is a report of a different type of breakdown for each thing. When printed, those 2 Excel sheets combined creates over 500 pages. Then another giant Excel sheet is made of a summary of the data. Then a final sheet is added as a comparison of the changes over time for each thing and notes all the significant things about the data. On that sheet is also generated a list of comments to describe certain things with the data.

    Before I came along, this report was largely generated with the use of a complex Excel document of Excel formulas. Those Excel documents were about 15mb in size and took like 10 minutes to open b/c of the calculations. With my first attempt of going JAVA all over this report's ass, I narrowed the size down to 8mb by taking almost all of the calculations out of it and having the values calculated in the JAVA code and hardcoded into the Excel document. It is far better now.

    See SQL Data Types, which Number does not seem to be a member of. How you get the actual data type I don't know as I'm not familiar with the Access 2007 syntax to do so.
    I am basing the data type from the Access, where if you go to Datasheet and click of cell it has Data Type: and it says Number for those values. Under that has Format: where it says #.00 as the value there. Still, that is very vague as there are several decimal data types.
    NOTE TO NEW PEOPLE LOOKING FOR HELP ON FORUM:

    When asking for help, please follow these guidelines to receive better and more prompt help:
    1. Put your code in Java Tags. To do this, put [highlight=java] before your code and [/highlight] after your code.
    2. Give full details of errors and provide us with as much information about the situation as possible.
    3. Give us an example of what the output should look like when done correctly.

    Join the Airline Management Simulation Game to manage your own airline against other users in a virtual recreation of the United States Airline Industry. For more details, visit: http://airlinegame.orgfree.com/

Similar Threads

  1. Access database SQL query help
    By mjpam in forum JDBC & Databases
    Replies: 3
    Last Post: September 8th, 2010, 08:48 AM
  2. Looking at Exploring Database Connecting
    By aussiemcgr in forum JDBC & Databases
    Replies: 2
    Last Post: August 12th, 2010, 03:40 PM
  3. Connecting to a database
    By fwashington in forum JDBC & Databases
    Replies: 5
    Last Post: March 15th, 2010, 01:37 PM
  4. How can i store ArrayList objects in Access database
    By frankycool in forum JDBC & Databases
    Replies: 0
    Last Post: November 3rd, 2009, 11:44 PM
  5. access database connectivity from outside an application
    By suchirag in forum JDBC & Databases
    Replies: 0
    Last Post: October 29th, 2009, 02:03 AM