Rectangle 27 125

UCanAccess is a pure Java JDBC driver that allows us to read from and write to Access databases without using ODBC. It uses two other packages, Jackcess and HSQLDB, to perform these tasks. The following is a brief overview of how to get it set up.

If your project uses Maven you can simply include UCanAccess via the following coordinates:

As mentioned above, UCanAccess requires Jackcess and HSQLDB. Jackcess in turn has its own dependencies. So to use UCanAccess you will need to include the following components:

UCanAccess (ucanaccess-x.x.x.jar)HSQLDB (hsqldb.jar, version 2.2.5 or newer)Jackcess (jackcess-2.x.x.jar)commons-lang (commons-lang-2.6.jar, or newer 2.x version)commons-logging (commons-logging-1.1.1.jar, or newer 1.x version)

Fortunately, UCanAccess includes all of the required JAR files in its distribution file. When you unzip it you will see something like

ucanaccess-4.0.1.jar  
  /lib/
    commons-lang-2.6.jar  
    commons-logging-1.1.1.jar  
    hsqldb.jar  
    jackcess-2.1.6.jar

NOTE: Do not add loader/ucanload.jar to your build path if you are adding the other five (5) JAR files. The UcanloadDriver class is only used in special circumstances and requires a different setup. See the related answer here for details.

Eclipse: Right-click the project in Package Explorer and choose Build Path > Configure Build Path.... Click the "Add External JARs..." button to add each of the five (5) JARs. When you are finished your Java Build Path should look something like this

NetBeans: Expand the tree view for your project, right-click the "Libraries" folder and choose "Add JAR/Folder...", then browse to the JAR file.

After adding all five (5) JAR files the "Libraries" folder should look something like this:

IntelliJ IDEA: Choose File > Project Structure... from the main menu. In the "Libraries" pane click the "Add" (+) button and add the five (5) JAR files. Once that is done the project should look something like this:

Now "U Can Access" data in .accdb and .mdb files using code like this

// assumes...
//     import java.sql.*;
Connection conn=DriverManager.getConnection(
        "jdbc:ucanaccess://C:/__tmp/test/zzz.accdb");
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]");
while (rs.next()) {
    System.out.println(rs.getString(1));
}

At the time of writing this Q&A I had no involvement in or affiliation with the UCanAccess project; I just used it. I have since become a contributor to the project.

Do you have any affiliation with this library? Might be worth disclosing it if you do.

Can you use this to connect to an Excel Workbook (.xlsx) in Java 8?

net.ucanaccess.jdbc.UcanaccessDriver

@GordThompson That is a great answer. My entire class was stuck with this issue believing it to be a file system privilege error. Helped me loads and it took a surprising amount of digging to realize Oracle done away with the JDBC-ODBC bridge from Java 8.

Manipulating an Access database from Java without ODBC - Stack Overflo...

java ms-access ucanaccess
Rectangle 27 14

Although the readme only mentions UnixODBC, this project: https://github.com/wankdanker/node-odbc will actually install and work on windows.

You can install it on windows with:

npm install odbc

but you will need the various requiements for building native modules on windows:

You'll likely need to rebuild odbc to work with your version of Electron. To do that, follow the steps here: stackoverflow.com/a/45930921/293280

Libraries for connecting node.js to odbc on Windows? - Stack Overflow

windows node.js ms-access odbc
Rectangle 27 11

Be sure to download the 1033\x64\msodbcsql.msi (4.4 MB) installer. Even though you are using PHP x86, you need the x64 installer since the OS is x64. I had the same issue and my app now works with PHP x86 after installing the x64 installer.

Thanks a lot, I'll try that and reply here.

The x64 was not an option for me, only x86 or amd64. x86 would not install, tried amd64 and it would install... any ideas what gives?

PHP 5.5 and MSSQL driver: Installing ODBC Driver 11 in Windows Server ...

sql-server-2012 odbc windows-server-2012 php-5.5 iis-8.5
Rectangle 27 10

You want a DSN-less linked table connection from Access. It is possible and I've done it but I don't have the code with me. I think it was something like the below (this uses a SQL Server source but Oracle would just have a slightly different connection string). To have the table(s) created on startup you'll need to check for the existence of each tabledef prior to attempting to create them again and call a subroutine like the below upon Access database open.

Function LinkTables()
    Dim DB As Database, tDef As TableDef
    Set DB = CurrentDb
    Set tDef = DB.CreateTableDef("YourAccessLinkedTableNameHere")
    tDef.Connect = "ODBC;Driver={SQL Server};Server=srvname;Database=dbname;UID=sqluserid;PWD=sqlpwd"
    tDef.SourceTableName = "dbo.YourSourceTableNameHere"
    DB.TableDefs.Append tDef
End Function

Note that the connection string format is different for every database out there. Look here for a reference: carlprothman.net/Default.aspx?tabid=81

If you don't need to be aware of what is happening in the UI layer of Access, then replace: Set DB = CurrentDb - to: Set DB = Workspaces(0).Databases(0) or to: Set DB = dbEngine(0)(0) Because it is considerably faster; as much as five thousand times faster.

oracle - Programatically create ODBC connection and link tables in MS ...

oracle ms-access odbc dsn
Rectangle 27 18

The simplest way to connect is through an OdbcConnection using code like this

using System.Data.Odbc;

using(OdbcConnection myConnection = new OdbcConnection())
{
    myConnection.ConnectionString = myConnectionString;
    myConnection.Open();

    //execute queries, etc

}
myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" + 
"Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

In alternative you could create a DSN and then use that DSN in your connection string

  • Open the Control Panel - Administrative Tools - ODBC Data Source Manager
  • Go to the System DSN Page and ADD a new DSN
  • Choose the Microsoft Access Driver (*.mdb) and press END
  • Set the Name of the DSN (choose MyDSN for this example)
  • Try the Compact or Recover commands to see if the connection works

now your connectionString could be written in this way

myConnectionString = "DSN=myDSN;"
OdbcConnection

when MDW is in picture, you can need different connection string. see here

Pretty good answer, you can also take a look to this other question to check how to use Microsoft Access with Entity Framewor.

How to connect to a MS Access file (mdb) using C#? - Stack Overflow

c# ms-access oledb
Rectangle 27 3

The native client has support for some additional (more advanced?) features of sql server 2008 (and 2005 I believe).

However, out of the box, you are far more likely to find the standard sql server driver installed on the computer.

Unless you are using some type of installer, or some other software installs this native client driver, then you are best to stick with the default non native driver for maximum compatibility. And, there is just the plain issue that the standard driver is most likely to be already installed on your client side computer.

So, that new native driver not going to be installed by default, and you likely have somewhat better luck with the non native default driver. I had a few issues come up with exporting date columns when using the new native driver (can't recall just right now what the issue was, but there was an issue).

Note that your connection strings are/will be slightly different for the native driver, and if you have some re-link code, that code will fail on computers without the native driver. So, while you have both on your computer, you can't assume this will be the case on other computers. So, you should have special and good reaons to choose/use the new native drivers for 2008/2005, but if not, then use the standard ones.

If I were using SQL Server 2008, I'd unquestionably make sure the native client was installed on all the workstations, as it's so vastly superior, not just in what it supports, but in terms of speed. That is, for an app using SQL Server 2008, I'd make it a requirement for the installation of the app.

ODBC: SQL Server 2008 Driver for MS Access - Stack Overflow

sql-server-2008 ms-access odbc ms-access-2007 odbc-sql-server-driver
Rectangle 27 1

It appears you can simultaneously install ODBC 11 and 13|13.1 since the filenames are different and even Microsoft suggests you can change the connection string. (I do not have yet a server with SQL 2016 on testing environment. Therefore, I can not test it).

To quote from the Microsoft's pages:

When an application uses the driver, the application should indicate that it depends on the driver through the install option APPGUID. Doing so enables the driver installer to report dependent applications before uninstalling. To specify a dependency on the driver, set the APPGUID command-line parameter to your product code when silently installing the driver. (A product code must be created when using Microsoft Installer to bundle your application setup program.) For example:

msiexec /i msodbcsql.msi APPGUID={ <Your dependent application's APPGUID> }

sql server 2008 - ODBC Driver 13 and ODBC Driver 11 - Stack Overflow

sql-server-2008 odbc sql-server-2016
Rectangle 27 5

I've gotten pyodbc to work with my SQL Server instance, then, with some help from this thread, I got the sql return to load a dataframe.

I already setup a pyodbc connection, then made a call to it.

import pyodbc

import pandas.io.sql as psql

cnxn = pyodbc.connect(your_connection_info) 
cursor = cnxn.cursor()
sql = ("""SELECT * FROM Source""")

df = psql.frame_query(sql, cnxn)
cnxn.close()

df should return your dataframe now. The hardest part for me was getting pyodbc up and running - I had to use freetds and it took a lot of trial and error to get it work.

perfect works. saves a step... Now if only there was a way to do it from a pyTable.root.table.where('field == value') then I could avoid all intermediate csv files.

pyodbc - itter from some odbc connection to pandas table with out a cs...

pandas pyodbc
Rectangle 27 2

New version containing a bugfix has been released by Oracle: 5.3.8

This error was a bug that seems to have been introduced in version 5.1.11.

Reply from Oracle about the fix: "For your information the fix approach was that in C or C++ it is possible to read or write DATE type using SQL_TIMESTAMP_STRUCT. This struct can hold both date and time. The error (Date overflow) was generated when with operations that are supposed to be DATE-only this struct got non-zero values for time. That is the canonical approach as ODBC API requires, however, it causes inconveniences sometimes when for instance the app did not bother to initialize the whole structure with 0 values because it knows it will only need the DATE part but the random values for TIME fraction could cause the errors despite of being truncated. A new option was introduced to continue with the query execution rather then return error. The server will ignore the TIME part and the result is the same as if there were zeroes."

mysql - ODBC call failed - Random date overflow errors after moving fr...

mysql datetime ms-access odbc ms-access-2003
Rectangle 27 2

ODBC is designed to abstract away the implementation details of the server. You can use ODBC specific syntax that will be translated to a statement of the appropriate SQL flavour for the server. Here you can substitute :

... { fn CONVERT( VERSION, SQL_VARCHAR ) } AS OBJECT_NAME FROM ANALYSIS

These substitutions are known as ODBC Escape Sequences and can be substituted in queries where there are vendor-specific syntax differences.

sql server - Determine ODBC database driver from TADOConnection object...

sql-server delphi ms-access odbc ado
Rectangle 27 2

You have a chance for Slovenian letters according to this mapping and an excerpt from Windows-1252 wiki article:

According to the information on Microsoft's and the Unicode Consortium's websites, positions 81, 8D, 8F, 90, and 9D are unused; however, the Windows API MultiByteToWideChar maps these to the corresponding C1 control codes.

The euro character at position 80 was not present in earlier versions of this code page, nor were the S, s, Z, and z with caron (hek).

I've tested the following script and it works like a charm.

<%@Language=VBScript%>
<% 
Option Explicit

Response.CodePage = 1252
Response.LCID = 1060
Response.Charset = "utf-8"

Const adCmdText = 1, adVarChar = 200, adParamInput = 1, adLockOptimistic = 3

Dim Connection
Set Connection = Server.CreateObject("Adodb.Connection")
    Connection.Open "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDb;User=myUsr;Password=myPwd;stmt=SET NAMES 'utf8';"

If Request.Form("name").Count = 1 And Len(Request.Form("name")) Then 'add new
    Dim rsAdd
    Set rsAdd = Server.CreateObject("Adodb.Recordset")
        rsAdd.Open "names", Connection, ,adLockOptimistic
        rsAdd.AddNew
        rsAdd("name").Value = Left(Request.Form("name"), 255)
        rsAdd.Update
        rsAdd.Close
    Set rsAdd = Nothing
End If

Dim Command
Set Command = Server.CreateObject("Adodb.Command")
    Command.CommandType = adCmdText
    Command.CommandText = "Select name From `names` Order By id Desc"

    If Request.QueryString("name").Count = 1 And Len(Request.QueryString("name")) Then
        Command.CommandText = "Select name From `names` Where name = ? Order By id Desc"
        Command.Parameters.Append Command.CreateParameter(, adVarChar, adParamInput, 255, Left(Request.QueryString("name"), 255))
    End If

    Set Command.ActiveConnection = Connection
    With Command.Execute
        While Not .Eof
            Response.Write "<a href=""?name=" & .Fields("name").Value & """>" & .Fields("name").Value & "</a><br />"
            .MoveNext
        Wend
        .Close
    End With

    Set Command.ActiveConnection = Nothing
    Set Command = Nothing

Connection.Close
%><hr />
<a href="?">SHOW ALL</a><hr />
<form method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
Name : <input type="text" name="name" maxlength="255" /> <input type="submit" value="Add" />
</form>

When you need to apply html encoding to strings fetched from the database, you shouldn't use Server.HTMLEncode anymore due to Response.Codepage is 1252 on server-side and since Server.HTMLEncode is dependent context codepage this will cause gibberish outputs. So you'll need to write your own html encoder to handle the case.

Function MyOwnHTMLEncode(ByVal str)
    str = Replace(str, "&", "&amp;")
    str = Replace(str, "<", "&lt;")
    str = Replace(str, ">", "&gt;")
    str = Replace(str, """", "&quot;")
    MyOwnHTMLEncode = str
End Function
'Response.Write MyOwnHTMLEncode(rs("myfield").value)

I have implemented your suggestion, but now I have a problem with hard-coded Slovenian letters. Data from MySQL is ok now, but hard-coded text does not show correctly: slotenis.si/test.asp

ANSI as UTF-8 Encoded
UTF-8 without BOM
Encode > Convert to UTF-8 without BOM

Actually the hard-coded data is correct, as long as it is really hard-coded, that menas written as HTML. If I use ASP/VBscript command Response.Write ..., than Slovenian characters are not printed ok. Anyway, this is solved now. But there is another problem. How to INSERT data in MySQL with ? I have problems with , which becommes c in db, and becommes C.

@gdolenc I have no any encoding problems including INSERT and Response.Write's. Everything is just fine for me. Here is another test page : aspfiddle.com/gdolenc2.asp can download the source btw. May have missed out something.

utf 8 - Classic ASP, MySQL or ODBC UTF8 encoding - Stack Overflow

mysql utf-8 asp-classic character-encoding odbc
Rectangle 27 2

The ORA-12154 error indicates that you are specifying a TNS alias that is not resolving. Are you using a DSN for your connection to Oracle? If so, what is the TNS alias you're specifying there? Does that alias exist in your tnsnames.ora file? Are there multiple tnsnames.ora files on the client?

The tnsnames.ora file is on the client machine where Access is installed. Without knowing what version of the Oracle client is installed, the simplest way to find out which tnsnames.ora file is being used is probably to open up a DOS prompt and type "tnsping service_name" where `service_name" is whatever TNS alias is specified in your DSN. You'll get something like

C:\Users\jcave>tnsping fuddy_duddy

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-OCT-2
010 08:10:51

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora

TNS-03505: Failed to resolve name

The "Used parameter files" line tells you the directory where the TNS related files are stored. There should be a tnsnames.ora and a sqlnet.ora file in that directory.

Can you tell me where I would find the tnsnames.ora files? Because I don't have access to the server except through this ODBC connection.

Strange thing is that I cannot connect using the 'Microsoft ODBC for Oracle' driver but I can connect with the 'Oracle in orant' driver. See any issues in using that with Access?

Using the Oracle driver is generally the preferred approach. The Microsoft ODBC driver for Oracle is obsolete-- it was never updated to stop using the Oracle 7 call interface API which has been depricated for more than a decade.

ODBC Oracle Connection error from MS Access - Stack Overflow

oracle ms-access odbc tns
Rectangle 27 3

At first make sure you can access that database via ODBC. Make DSN in odbcad32 for both 64 and 32 bit systems. Then as JDBC connect string use: jdbc:odbc:[CreatedDSN]. If you cannot connect to Access in 64 bit version of odbcad32 then make sure it works in 32 bit version of odbcad32 and make sure you use 32 bit version of Java.

Microsoft Access and Java JDBC-ODBC Error - Stack Overflow

java jdbc odbc
Rectangle 27 4

Provider "MSDASQL" is Microsoft's OLE DB Provider for ODBC Drivers. It is quite old, and is now deprecated. It is really only for use with older databases for which there are no OLE DB providers. It is also 32-bit-Only, so it won't work with 64 bit providers (such as the one you are trying to use). You would be better off trying an OLE DB driver.

The MS OLEDB provider for Oracle is "MSDAORA" (which should be pre-installed on your machine) and Oracle's own OLEDB provider is "OraOLEDB.Oracle". You would be best advised to download the latest Oracle-provided provider, as MSDAORA is also deprecated.

You would need to download and install the Oracle provider (if you haven't already)

.Provider = "OraOLEDB.Oracle"
.ConnectionString

Updating Oracle Table from Excel VBA Macro using ODBC connection - Sta...

oracle excel vba odbc ado
Rectangle 27 3

You can configure 32bit odbc settings through admin snap in only by calling it from command line or browse to it in Explorer in folder

C:\Windows\SysWOW64\odbcad32.exe

By default in Administration Tools in Control Panel you will start only 64bit version which PHP will not use.

When I go to administrative tools, the 64bit box pops up. I have to manually go to the 32bit version. So you are saying because PHP is a 32 bit install it will be using the 32 bit driver?

Right, thats the confirmation I needed, thanks. :)

Just a quick follow up, if only a 64bit ODBC driver is installed, PHP will use that right?

The path you provided for the 32-bit odbc administrator is incorrect. Counterintuitively, that is the 64-bit one. The 32-bit one is located in C:\Windows\SysWOW64.

apache - 32bit and 64bit ODBC Drivers playing nice with PHP - Stack Ov...

php apache odbc 32bit-64bit
Rectangle 27 3

The below link has a step by step guide that worked well for me

sql server - How to install IBM db2 ODBC driver in Windows 7 - 64 bit?...

sql-server db2 odbc driver
Rectangle 27 1

This appears to be an issue with MySQL Connector/ODBC versions 5.2 and later. A web search led to this thread on another site, which in turn led to this unresolved bug report. Note that this is a broader MySQL Connector/ODBC issue; it is not specific to Access applications.

MySQL Connector/ODBC 5.1.13 is still available for download, so your most expedient solution would probably be to simply use that version until the code in question is taken out of service. Your other alternatives might include:

  • using a newer version of MySQL Server (with [better?] support for fractional seconds), or
  • tweaking the Access SQL queries to use something other than the Now() function (which you, understandably, would like to avoid).

mysql - ODBC call failed - Random date overflow errors after moving fr...

mysql datetime ms-access odbc ms-access-2003
Rectangle 27 6

After turning on ODBC logging got the event message A fatal error occurred while creating an SSL client credential. The internal error state is 10013.

this didnt lead me to the exact problem but did lead me to the fix from a similar error.

Thanks! Wasted most of my day troubleshooting this. Now, looking for a way to enable TLS 1.2 instead of having to enable open this vulnerability.

Unable to connect to SQL Server 2008 using ODBC connection via system ...

sql-server-2008 odbc windows-server-2012
Rectangle 27 2

protected void EditExam(object sender, GridViewEditEventArgs e)
    {
        ExamEditGridView.EditIndex = e.NewEditIndex;
        ExamEditGridView.DataSource = Session["data"];
        ExamEditGridView.DataBind();
    }

protected void CancelEdit(object sender, GridViewCancelEditEventArgs e)
    {
        ExamEditGridView.EditIndex = -1;
        ExamEditGridView.DataSource = Session["data"];
        ExamEditGridView.DataBind();
    }

asp.net - Editable Gridview with code behind data source from ODBC - S...

asp.net gridview
Rectangle 27 2

Both answers are correct, but only have the links to were the answers are.

  • download the correct driver (called fix pack most of the time) you need from IBM
c:\program files\IBM
c:\program files\IBM\clidriver
  • open a command prompt as administrator and change to the directory you just created and then go in the bin folder

execute the following commands to install and register the odbc drivers

db2oreg1 i

db2oreg1 setup

check your odbc manager if the drivers are installed, this is depending on the version you selected (32 bit or 64 bit)

for the 32 bit version open your odbc data sources 32 bit otherwise open the odbc data sources 64 bit.

odbc
  • click on the version you need (32 bit or 64 bit)

sql server - How to install IBM db2 ODBC driver in Windows 7 - 64 bit?...

sql-server db2 odbc driver