0
$\begingroup$

I am able to connect to a specific MS Access Database when it is on the same Windows computer as Mathematica via the command

<< DatabaseLink`;
conn = OpenSQLConnection[JDBC["odbc", "Databasename"]];

However, I cannot figure out how to connect to this database over a local area network -- disregarding trivial network problems (the computers can ping eachother), is this even possible?

Improve this question
$\endgroup$
4
  • 6
    $\begingroup$ I think your question is on-topic if you're able to connect to the remote DB from any other software and off-topic if you can't. $\endgroup$ Commented Apr 28, 2013 at 16:03
  • $\begingroup$ AFAIK an Access database is just a plain file and the usual approach is to make the file accessable from the remote computer via e.g. a "network drive". It then can be used like a local file with the usual ODBC driver, but you'll probably have to configure that. As there is no real database server involved, concurrent usage of the same database file with several processes/programs will only be possible within certain limits (which I don't know about), and if you need concurrent read/write you certainly should consider installing/running a real database server... $\endgroup$ Commented Apr 29, 2013 at 13:11
  • $\begingroup$ @AlbertRetey Sorry I just read your comment now. I have to use Linux to run MMA and so far I did not find a way to access the database like you described. My current idea is to migrate the db to MySQL (a "real" database) and only use Access as a frontend on the windows side. As a non-IT person, I hope this will not result in more pain... $\endgroup$ Commented May 1, 2013 at 23:15
  • $\begingroup$ @R.S.: As there still are no other answers, I have created one with additional remarks. I also think you should add the information that you want to access the database from a linux client to your questions. $\endgroup$ Commented May 2, 2013 at 9:30

2 Answers 2

Reset to default
0
$\begingroup$

This is not really an answer yet, but definitely too long for a comment.

Access databases are designed for single user access and store data, forms and code in a single file. It is possible to access such a file from a remote computer via e.g. a network drive. As there is no real database server involved, concurrent usage of the same database file with several processes/programs will only be possible within certain limits (which I don't know about).

It should be possible to access such a file from a network drive via the usual ODBC drivers, but you might need to configure those accordingly. You have mentioned that you want to access the database from a linux client: There are OBDC drivers for access databasefiles which work under linux, but I don't know if and how it is possible to use those from Mathematica, but would expect that should be possible. If the file lives on a windows server you could mount a network file with the samba client programs, but that might introduce additional restrictions on concurrent usage as there could be differences in how the file locking and other details are handled. I would be very careful when experimenting with such a setup. On the other hand I wouldn't expect insurmountable problems if only one program accesses the database file at a time.

Depending on how you are using that database converting the database is probably the best you can do, especially if you need concurrent access. As a first try you could try to use e.g. openoffice or kexi to convert the database, but of course you could also do that with Mathematica on a Windows machine. Depending on the target format there might be even better options, e.g. specialized import functions/scripts for certain database systems.

If you don't need concurrent access you could consider SQLite as a target format which like Access stores the data in plain files and also is "serverless", so using it might be simpler than setting up a database server. There is a JDBC driver for SQLite, which you should be able to use from Mathematica. There is also an undocumented more direct implementation to access a SQLite database from within Mathematica even without DatabaseLink, which you should find some notes about in the web.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Thank you very much for this extensive answer. I tried my best using these ODBX drivers for Linux. However there was a problem using newer MS Access accdb files (see here stackoverflow.com/questions/13473149/…), and I lost my trust in the reliability of this solution. As you said, converting / migrating the database is probably a better idea. I have already set up a MySQL server + DB on the WinXP side, which I can query via MMA running on Linux. Actually migrating the data will be the final step tomorrow or so. $\endgroup$ Commented May 3, 2013 at 17:33
  • $\begingroup$ @R.S.: if you have a MySQL server available, migrating the database seems definitely to be the way to go. Good luck with this anyway, this usually isn't the joy part of the work we all have to do :-)... $\endgroup$ Commented May 3, 2013 at 17:56
0
$\begingroup$

Empirically, it is not possible to directly connect Linux (and therefore also not MMA under Linux) to a MS Access data source in an acceptable amount of time. An alternative solution is to migrate the .accdb -> MySQL DB, which then can be queried platform independently. How to connect to this DB is described in the MMA documentation (works out of the box). The migration step is, according to MySQL whitepapers, easily possible - I will update this post if this is the case. MS Access can still be used as a convenient frontend to the MySQL DB.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Update concerning migration step: I used the free tool "Bullzip MS Access to MySQL", which is also recommended in a MySQL whitepaper, to create a dump file. This file was then imported into a new SQL-DB using MySQL workbench under Windows. I can see the tables from a Linux MMA installation. So the whole approach works, case closed :) $\endgroup$ Commented May 6, 2013 at 15:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.