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

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?

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.

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)?

Originally Posted by copeg

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: http://www.javaprogrammingforums.com...on-string.html

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?

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?

Originally Posted by copeg

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.