JDBC-ODBC - Running Queries on MS Access Database

This section describes how to run queries on MS Access database through the JDBC-ODBC driver.
To connect Java with MS Access, you need a JDBC driver. Although Microsoft do not produce a JDBC driver for MS Access, you can use the Easysoft JDBC-ODBC Bridge as a MS Access JDBC driver. Use the JDBC-ODBC Bridge to provide the connectivity layer between your Java code and MS Access database. The MS Access database can be on the same machine as your Java or a remote machine.

Sponsored Links:

Related Topics

MS Access JDBC Driver -- Connecting MS Access with Java
To connect Java with MS Access, you need a JDBC driver. Although Microsoft do not produce a JDBC driver for MS Access, you can use the Easysoft JDBC-ODBC Bridge as a MS Access JDBC driver. Use the JDBC-ODBC Bridge to provide the connectivity layer between your Java code and MS Access database. The MS Access database can be on the same machine as your Java or a remote machine.


JDBC-ODBC - Creating New Tables in MS Access Database
This section describes how to create a new table in a MS Access database through the JDBC-ODBC driver.


Needed - JDBC Derby or ODBC-JDBC bridge
I would like to use Access (2003) as a front end for a Java DB I am working on in a separate java project. These speed and ease of using Access would be a godsend.

I would be extremely grateful if anyone could point me in the right direction.

So far I found one commercial ODBC-JDBC bridge that failed to run on 64 bit Win 7. Other interfaces I tried either didn't work, were just hard to set up, or didn't provide the functionality you get with Access.

Hopefully I am overlooking something easy.

Any tips, clues, or links to a driver or method to get Access connected to a jdbc DB would be extremely appreciated


JDBC-ODBC Bridge Driver
This chapter provides tutorial notes on using the JDBC-ODBC Bridge driver with MS Access database. Topics include creating MS Access database file; creating DSN for MS Access database files; inserting rows to and retrieving rows from Access database.


On Using Pass-through Queries in MS Access
You might want to send a SQL specific query in Microsoft Access directly to the underlying ODBC database server to improve performance. You may do this by using a pass-through query. Running such queries is the topic of this tutorial. TOC: On Using Pass-through Queries in MS Access; Providing the ODBC Connectivity information; Creating a Query; Saving Connection information with the query.


Question Changing SQL user pw causes ODBC connection issues
I have a network that I am running with a SQL 2008 R2 server where I want to change the pw on the user associated with a SQL DB that my Access 2003 database accesses. I changed the pw on the SQL user and when I go into the ODBC connection in Admin tools I get a successful connection with the new pw, but when I try to open the Access database that is using this DSN I get an ODBC connection error. I tried relinking the tables with the Link Table Manager, but I still seem to have this issue.

What do I need to do in order to change a SQL user so that the ODBC connection for my Access database works? The SQL DB is running SQL 2008 R2 running on Server 2008 R2 and the client machines are running Access 2003 on Windows XP SP3.


UNION QUERY to MYSQL Fails
I have an Access 2003 database linked via ODBC using MYSQL ODBC version 5.1 It works very well apart from UNION queries with more than 1 UNION

If I run a query with 2 UNIONS then I get an Error

ODBC – call failed. (Error 3146)
....

Looks like an issue with the MYSQL ODBC Adapter.

IF I write it as a PASS THROUGH query running on the server with the same adapter DSN, then it is fine.

Anyone any ideas of whether it is possible to get round this issue IN ACCESS without resorting to temporary tables OR PASS Through query.


Access 2000 change ODBC query
first time poster.

I have several Access(2000) Queries created that are querying data from a SQL database.

I inherited these queries and everything was working fine. We recently changed our SQL server and our IT person deleted the old ODBC connection and created a new one to the new SQL server.

Now my queries do not work.they see data but it's data from the old server (which is still running) and not the new server.

I work alot with Crystal Reports and it's very easy to 'change' database location. However, in Access I can't seem to find a location/screen where I could edit the connection string associated with the Query


query fails
I have a client running MS Access 2003. The have a database that runs queries against a ERP system. They MS Access database resides on a network share. They access the ERP data via a ODBC connection. They had a user whos hard drive crashed and they had to give her a new system, when she tried to run her queries they fail with a jet error that a fields does not exist in the table. Someone made a copy of the queriy, when she runs the copy it succeeds? Thereis no difference in the queries and I do not know why one fails and the copy does not


ODBC -- call failed. | Oracle ODBC
I have an Access 2003 Database that resides on a virtual windows 2003 server that is giving me an error when I attempt to create a linked table or update any linked table that is using an odbc file dsn. The database being connected to is Oracle.

Here's the full error.

Now the weird thing is, the error is only occurring on a virtual machine while on my local development box, it works fine.

Both environments are using identical tnsfilename.ora files.

The failing server is running Windows Server 2003 with MS ODBC for Oracle v2.576.3959.000.
The dev box is running WinXP Pro with MS ODBC for Oracle v


Database/Queries Running Very Slow?
I am making an Ms-Access database for Real Estate Management? I have succeeded in doing all that which is enlisted below, the problem is that the database is running very slow? I don't know what can I do now? The queries is running very slow thatmakes me to wait too much, I want to make the database run very fast?


Access Database Manipulation via JDBC
This will teach you how to connect to a Microsoft Access database. It's also a great overview of JDBC. Once you are connected, you may run any SQL statement that is allowable on Access, such as SELECT, etc. You don't even have to have MS Access installed to run this tutorial - it shows you how to make a blank one without Access!


odbc connection in windows 7
with access 2007 on windows 7 Passthrough queries dose not connect to sql server. for waking up the connection I must first link manualy an sql table and then alll Passthrough queries are live.
the odbc string I use is: "ODBC;DSN=Focus;APP=2007 Microsoft Office system;DATABASE=mydb;uid=sa;pwd=123"

is there any security metter? I have to define something in access or in wondows


Sharing an ODBC Connection
I have an MS Access application that includes obtaining data from an SQL backend database. I have defined an ODBC connection to the SQL database and everything works fine on my system. I have a number of queries that include code like this "c:\documents and settings\<userid>\My Documents\<odbc database name>". I want to share this application with other members on my team. I understand that they have to set up their own ODBC connection and this is normally stored in their own My Documents folder. How do I modify the code sample above so that it is no longer specific to a <userid> \\My Documents folder? I tried copying the ODBC database to a shared folder but other people who try to access it can't open the tables in the SQL backend database when executing a query.


Linking between Access and an ODBC Database
I created a link between my Access database and an ODBC database. The wizard created a new table in Access based on the table in the ODBC database I wanted to import. So anytime that tablein the ODBC database is modified, the new table in my Access database will similarly be updated, correct?

But I need a table I created in Access to be also be automatically updated anytime someone enters information into the ODBC database - not just the table that was imported. How can I dothat? How can I automatically copy tables from the ODBC table into one of my own Access tables?


Linking between Access and an ODBC Database
I created a link between my Access database and an ODBC database. The wizard created a new table in Access based on the table in the ODBC database I wanted to import. So anytime that table in the ODBC database is modified, the new table in my Access database will similarly be updated, correct?

But I need a table I created in Access to be also be automatically updated anytime someone enters information into the ODBC database - not just the table that was imported. How can I do that? How can I automatically copy tables from the ODBC table into one of my own Access tables


Try to remove everything to the right of SF
I have a query where I want to remove everything to the right of SF in a column. I am using access 2007

SELECT Left(STORE_SIZE,InStr(1,STORE_SIZE,"SF") +1) AS R_STORE_SIZE FROM TENANT_TBL
WHERE DISPLAY = 1

The error that is returned to me is
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.


Try to remove everything to the right of SF
I have a query where I want to remove everything to the right of SF in a column. I am using access 2007

SELECT Left(STORE_SIZE,InStr(1,STORE_SIZE,"SF") +1) AS R_STORE_SIZE
FROM TENANT_TBL
WHERE DISPLAY = 1

The error that is returned to me is
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected


Oracle ODBC 11g Driver
I'm having difficulties with an ODBC connection to an Oracle 11g database. I'm running Windows XP with Office 2010. I've installed the Oracle ODBC drivers and successfully created a DSN to the Oracle database. We've been able to ping the database, connect via SQL Plus and even link tables from within an Access 2002-2003 database. When I attempt to link from an Access 2007 database I get the error -

*****
ODBC--call failed.
Specified driver could not be loaded due to system error 127 (Oracle in OraClient11g_home1). (#160)
*****
I can't find anything to tell me what driver is not being loaded or what system error is occurring.


ODBC 32 to 64 Bit connection error
We currently run an ODBC connection to Access that is stored on a network drive. We are currently upgrading from a 32 bit computer to 64 bit. Before I simply imported the registry from computer to computer to save time on setting up numerous ODBC connections. Whenever I import onto the new 64 bit system it is importing into the 64 bit ODBC and not into the 32 bit odbc. When I test the connection it is fine but whenever I run the access report it gives the following error:

ODBC--connection to 'database' failed

I am almost certain that it is before the two ODBC connections. My question is will I have to recreate the Access report for 64 bit or is there a way to import on a 64 bit machine to the 32 bit ODBC?

I am not a database guru and limited knowledge of Access and ODBC so please bare with me