Linux microsoft access driver

PDO: How to access Microsoft Access file on Linux?

I have managed to read data of Microsoft Access file (.accdb) on Windows using PDO but I’m having problem getting to work on Linux (CentOS). I can see modules is installed:

[root@rapid host]# php -m | grep PDO PDO PDO_ODBC [root@rapid host]# php -m | grep odbc odbc 

;Dbq=/root/access/data.accdb;Uid=Admin"); > catch(PDOException $e)< echo $e->getMessage(); exit(); > 

[root@rapid host]# php access.php SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified 

5 Answers 5

you should install the MDB driver.

I have no way to try it now, but I think that MDB Tools (specifically the ODBC driver) can do what you’ re interested to.

I’ve never worked with a Microsoft Access database but I do regularly connect to DB2 (on IBM AS/400’s) and MS SQL servers from Linux (Ubuntu) servers. Your error seems to indicate you don’t have a MS Access driver installed — the only one I’m aware of is: http://www.easysoft.com/products/data_access/odbc-access-driver/index.html

Your error message also says the «Data source name not found» — in order for me to connect to DB2 or MSSQL I have to add some information to /etc/odbc.ini and /etc/odbcinst.ini.

/etc/odbcinst.ini — this is where you describe where to find the drivers for ODBC. Here’s an example of what I use for DB2 and MSSQL:

[iseries] Description = iSeries Access for Linux ODBC Driver Driver = /usr/lib/libcwbodbc.so Setup = /usr/lib/libcwbodbcs.so NOTE1 = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's, NOTE2 = the following Driver64/Setup64 keywords will provide that support. Driver64 = /usr/lib/lib64/libcwbodbc.so Setup64 = /usr/lib/lib64/libcwbodbcs.so Threading = 2 DontDLClose = 1 UsageCount = 1 # Define where to find the driver for the Free TDS connections. [freetds] Description = MS SQL database access with Free TDS Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so UsageCount = 1 [ODBC] Trace = no TraceFile = /tmp/odbc.log 

In that last section ([ODBC]) I currently have Trace = no — if you change that to Trace = yes you will get some helpful debugging information in the /tmp/odbc.log file.

/etc/odbc.ini — this is where you define your data sources. Here’s an example of what I use for DB2 and MSSQL:

[primary] Description = primary Driver = iseries System = XXX.XXX.XXX.XXX UserID = XXXXXXXXXX Password = XXXXXXXXXX Naming = 0 DefaultLibraries = QGPL Database = MYLIB ConnectionType = 0 CommitMode = 2 ExtendedDynamic = 0 DefaultPkgLibrary = QGPL DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512 AllowDataCompression = 1 LibraryView = 0 AllowUnsupportedChar = 0 ForceTranslation = 0 Trace = 0 # Define a connection to the MSSQL server. # The Description can be whatever we want it to be. # The Driver value must match what we have defined in /etc/odbcinst.ini # The Database name must be the name of the database this connection will connect to. # The ServerName is the name we defined in /etc/freetds/freetds.conf # The TDS_Version should match what we defined in /etc/freetds/freetds.conf [mssql] Description = MSSQL Server Driver = freetds Database = MyDatabase ServerName = mssql TDS_Version = 8.0 

I’ve seen a number of questions here on StackOverflow talking about using MSFT Access databases from a Linux machine — and there doesn’t seem to ever be a happy ending. If there is any way you can port the data to a different, better supported, database system (like MySQL) I think you’ll save yourself some headaches. Good luck!

Источник

Microsoft Access

Чтобы получить доступ к файлам .accdb и .mdb под Linux, необходимо установить пакет ucanaccess и добавить классы в LibreOffice. Подразумевается, что пакеты LibreOffice (или LibreOffice-still), а также java-1.8.0-openjdk уже установлены.

apt-get update apt-get install ucanaccess

2. Открываем LibreOffice Writer из меню и переходим в Сервис → Параметры… → Расширенные возможности. Нажимаем кнопку «Путь класса…». В появившемся окне, нажимая кнопку «Добавить архив…» поочерёдно добавляем пять архивов .jar из /usr/share/java:

3. Перезапускаем LibreOffice.

4. Запускаем LibreOffice Base и выбираем «Соединиться с существующей базой данных» → JDBC.

5. Заполняем поля источника и драйвера:

URL источника данных (открываем файл /home/cas/Contacts_Demo_V1.01.mdb )

ucanaccess:///home/cas/Contacts_Demo_V1.01.mdb

net.ucanaccess.jdbc.UcanaccessDriver

Примечание: Для проверки работы драйвера на этом этапе можно нажать кнопку «Тест класса». При успешной установке появится окно «JDBC драйвер успешно загружен».

6. Нажмите «Готово». Будет предложено создать новый файл базы данных. Укажите имя и ваш файл mdb будет открыт в LibreOffice Base:

Использование Kexi

Также файлы .mdb вы можете открыть в программе Kexi. Установите

Примечание: Если вы не используете KDE5, то также установите kde5-profile и перезапустите сеанс.

Ссылки

Источник

How to use an ms-access file from linux?

Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. Although it is primarily designed for use on Windows, it can also be used on other platforms such as Linux, with some limitations. If you want to use an MS-Access file on a Linux system, there are a few options available to you.

Method 1: Wine

To use an MS-Access file from Linux, we can use Wine. Wine is a compatibility layer capable of running Windows applications on Linux. Here are the steps to use an MS-Access file from Linux with Wine:

Step 1: Install Wine

First, we need to install Wine on our Linux system. Here is the command to install Wine on Ubuntu:

Step 2: Install MS-Access

Next, we need to install MS-Access on Wine. We can install it by following these steps:

1. Download the MS-Access installer from Microsoft website.
2. Open the terminal and navigate to the directory where the installer is downloaded.
3. Run the following command to start the installation:

Step 3: Open MS-Access file

Once MS-Access is installed, we can open the MS-Access file by following these steps:

1. Open the terminal and navigate to the directory where the MS-Access file is located.
2. Run the following command to open the MS-Access file:

wine msaccess.exe /path/to/access/file.accdb

This will open the MS-Access file in Wine, and we can use it just like we would on a Windows system.

Method 2: Virtual Machine

1. Install VirtualBox on your Linux machine.
2. Download the Windows ISO file and create a new virtual machine on VirtualBox with Windows as the guest OS.
3. Install Microsoft Access on the Windows virtual machine.
4. Share the MS-Access file from the Windows virtual machine to the Linux host machine by adding a shared folder in VirtualBox.
5. Mount the shared folder on the Linux machine by running the following command:

sudo mount -t vboxsf shared_folder_name /mnt/mount_point

1. Access the MS-Access file from the mounted folder using ODBC connection. Install ODBC drivers for MS-Access on the Linux machine and configure the connection string in your code.

import pyodbc conn_str = ( r'DRIVER=;' r'DBQ=/mnt/mount_point/path/to/access_file.accdb;' ) conn = pyodbc.connect(conn_str) cursor = conn.cursor() cursor.execute('SELECT * FROM table_name') rows = cursor.fetchall() for row in rows: print(row)

That’s it! You can now use the MS-Access file from your Linux machine using VirtualBox.

Method 3: Remote Desktop Connection

sudo apt-get update sudo apt-get install xrdp

rdesktop windows_machine_ip>

cd "C:\Program Files\Microsoft Office\OfficeXX" msaccess.exe "C:\path\to\your\file.accdb"

1. You can now use your MS-Access file from your Linux machine through the Remote Desktop Connection.

Note: Make sure that your Windows machine has Remote Desktop enabled and that your Linux machine can connect to it. Also, make sure that you have the necessary permissions to access the MS-Access file.

Method 4: Converting to Other Database Format

sudo apt-get install mdbtools

mdb-schema file.mdb | sqlite3 file.sqlite for i in $(mdb-tables file.mdb); do mdb-export -I sqlite file.mdb $i > $i.sql; done for i in $(ls *.sql); do sqlite3 file.sqlite  $i; done

mdb-schema file.mdb mysql | mysql -u username -p -h localhost dbname for i in $(mdb-tables file.mdb); do mdb-export -I mysql file.mdb $i | mysql -u username -p -h localhost dbname; done

mdb-schema file.mdb postgres | psql -h localhost -U username dbname for i in $(mdb-tables file.mdb); do mdb-export -I postgresql file.mdb $i | psql -h localhost -U username dbname; done

Note: Replace «file.mdb» with the name of your MS-Access file, and replace «username», «dbname», and «localhost» with your own MySQL/PostgreSQL settings.

This method allows you to use an MS-Access file from Linux by converting it to another database format. You can then access the converted data using the appropriate database tools and libraries.

Источник