aspose file tools*
The moose likes JDBC and the fly likes MSAccess slow? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MSAccess slow?" Watch "MSAccess slow?" New topic
Author

MSAccess slow?

Sam Smoot
Ranch Hand

Joined: Apr 18, 2002
Posts: 238
I am having a problem that is not good. I am TRYING to write an application to search and update an M$Access database that is on a network drive (not a file server, but a network shared drive). When I run the add code, it runs, but doesn't add the record to the database. HOWEVER, if I put the code through the Debugger process in Eclipse or one of my other IDE's and step through it, it updates the database. The Search part works fine as is.

Has anyone else had this problem?


CNSS/NSA Infosec Professional,<br />Software Engineer
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I believe the problem is that access is a file, not a database. It is the ODBC part which provides the RDBMS-like behaviour. Any time you cause something to happen that results in a file change, the DBMS stuff happens locally but all file operations happen at network speeds.

Have you tried (for profiling) copying the file locally to test the behaviour?

You may be able to use some network drive trickery to provide a local synchronised copy of the access file, but this would cause problems if the file was also accessed from somewhere else.
Jon Egan
Ranch Hand

Joined: Mar 24, 2004
Posts: 83
Sounds to me more like the problem is that the change is not being committed at the end. When you say the insert is made when you run the code through the debugger, do you mean:


* that you see the code reach that point, and through another part of the app in the same debugger session you can query the same record back out?

* Or, do you mean that you are able in a separate process to verify the data is added, either during the debugging run or sometime after?


If it's the first one, but not the second, I'd suspect that the database connection is not in auto-commit mode (don't know what the default would be for JDBC-ODBC bridge driver, and ACCESS database). Then you have the option of either changing the database connection to auto-commit, or explicitly committing after the insert.


If that's NOT the case, .... hrmmm.... then I'm stumped right along with you

-- Jon
Sam Smoot
Ranch Hand

Joined: Apr 18, 2002
Posts: 238
Well, after a rather (IMO) rude discussion at Sun, I found out it is the fact I need to close the connection after EVERY access to Access.

For example, I need to tie multiple items from one table to a central table from a list, and the code wound up like this:



The value thsID was the foreign key from the "parent" table so that they could be retrieved and viewed at one time. Of course, if you have 12 items in the list, the database is cycled 12 times. This is one of 2 tables that can have multiple entries for 1 parent record in this particular application.

NOW all I have to do is figure out how to make it into a GUI by Monday :roll:


Thanks again.. I hate having to go elsewhere... Non-rancher's are scary
Jon Egan
Ranch Hand

Joined: Mar 24, 2004
Posts: 83
Wow... that is sort of ridiculous - I found a brief discussion of the problem in the Sun JDBC FAQ. The entry also references something called a RMI-JDBC bridge, (including a link to a vendor) which I suppose would be an RMI server that gives network access via RMI to a desktop database. Not something I think I would try to cram in before monday though

But this seemed more promising: you may not need to be using the JDBC-ODBC bridge at all in the first place - I did a search in the Sun listing of JDBC drivers available for MS Access. Since it's still Access behind the scenes, I'm not sure it wouldn't have the same problem... but if you get done sometime late Sunday, and there's too little time to get any valuable amount of sleep anyway, maybe you can look into one of those

-- Jon
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: MSAccess slow?