• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Insert query to MS Access won't work - and returns no errors

 
Rancher
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm using the standard jdbc-odbc driver for a local Java program to manipulate an MSAccess database. I've set up the connections correctly and I can query information from the DB with no problems. I can also create tables through jdbc with no problems. But whenever I try to INSERT data into a table, the data never actually gets there. JDBC never returns an error, however, and the code runs just fine.

I'm baffled. The Access table is called 'test', and there is only one column called 'name' of type 'Text'. I've found that it doesn't matter whether there's a primary key or not, or what type of column I use, or whether it's auto-numbered or not. Here's the piece of code that's killing me:



I've also tried it with a standard Statement with no luck. Any help please?
 
Matthew Taylor
Rancher
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
After searching some more on the forums, I found this. And this statement answers this question:

Originally posted by Jamie Robertson:
I have seen this problem extensively with certain combinations of the jdbc dbc bridge and MSAccess. Stop pulling your hair out, there are 2 workarounds to force this last record to insert:

1) Close the connection each time afterwards.
2) Do a dummy select each time afterwards. That seems to be the better one.

That means: after your last insert (not after each record) do this "trick".(after "insert into emp..." execute a query "select something from emp...) Not sure if the select has to be from the same table as the insert, but you can try it both ways

Jamie



Thanks, Jamie.
[ October 05, 2004: Message edited by: Matthew Taylor ]
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, Matthew,

just a thought:

I think you'd not use a semi-colon IN the sql string.
Try



and also try using executeUpdate instead:


hth
Roberto
[ October 05, 2004: Message edited by: Roberto Spier ]
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yep, it's a common problem with the jdbc-odbc bridge. Better not to use it.
Another solution is to setAutocommit(true) since.
 
reply
    Bookmark Topic Watch Topic
  • New Topic