7

A primary goal of a project I plan to bid on involves creating a Microsoft Access database using python. The main DB backend will be postgres, but the plan is to export an Access image.

This will be a web app that'll take input from the user and go through a black box and output the results as an access db. The web app will be built on a linux server.

I have a few related questions:

  • Is there a reliable library or module that can be used?
  • What has your experience been using Access and python?
  • Any tips, tricks, or must avoids I need to know about?

Thanks :)

abbot
  • 25,012
  • 5
  • 49
  • 56
dassouki
  • 5,921
  • 7
  • 47
  • 79
  • If Access is installed you can automate it from Python using Python for Windows extensions: http://sourceforge.net/projects/pywin32/ – HansUp May 05 '11 at 00:40
  • @HansUp - No it won't be installed as the web app will go on a linux server – dassouki May 05 '11 at 00:42
  • Duplicate of http://stackoverflow.com/questions/256459/populate-a-ms-access-database-in-linux – abbot May 05 '11 at 06:28
  • This is a very difficult problem. IIRC not even Access can read or write access databases correctly ;-) .... their history about backward compatibility is close to a nightmare. – 6502 May 11 '11 at 06:02

8 Answers8

4

Could you use an sqlite database instead?

edit:

If it HAS to be on linux and it HAS to be to MS Access, then I'm pretty sure this is your only choice, but it costs $1,550.

You are either going to have to shell out the money, or convince the client to change one of the other two parameters. Personally, I would push to change the database file to sqlite.

Of course you could always code up your own database driver, but it would probably be worth the time to shell out the $1,550. mdbtools has been working on this for years and the project has been pretty much abandoned.

found it, kinda

Ok, so I just couldn't let this go and found that there is a java library called Jackcess that will write to MS Access mdb files on any platform that can run the jvm. Granted, it's java and not python, but maybe you could learn just enough java to throw an application together and execute it from python? Or just switch the whole app to java, whatever.

James
  • 355
  • 2
  • 11
  • Strictly, the output to the app **must** be an MS Access MDB file. – dassouki May 10 '11 at 17:36
  • I thought you would say that. – James May 10 '11 at 21:04
  • Thanks for the suggestion. I haven't touched java in 8 years now, but I'm sure I can pick enough to be able to run it as an api that i can from python – dassouki May 11 '11 at 01:25
  • 2
    Ok, you have Jackcess, so supporting MS Access files on Linux seems to be solved. But remains problem that it is in Java, but the code has to be in Python. Is not [Jython](http://www.jython.org/index.html) a solution here? If not, why? – Tadeck May 12 '11 at 02:15
  • *Java in Python*: You can use the Python module `jpype` to instantiate a JVM with your jackess jar, calling MSAccess. – Martin Thøgersen Apr 03 '20 at 14:53
2

The various answers to the duplicate question suggest that your "primary goal" of creating an MS Access database on a linux server is not attainable.

Of course, such a goal is of itself not worthwhile at all. If you tell us what the users/consumers of the Access db are expected to do with it, maybe we can help you. Possibilities: (1) create a script and a (set of) file(s) which the user downloads and runs to create an Access DB (2) if it's just for casual user examination/manipulation, an Excel file may do.

John Machin
  • 75,436
  • 11
  • 125
  • 178
  • Strictly, the output to the app **must** be an MS Access MDB file. – dassouki May 10 '11 at 17:36
  • The goal seems to be attainable by using Jython/Jackcess, Python and a java/Jackcess converter, or a port to python of Jackcess. It will doubtlessly be a pain though. Or pay 1500 and use unixODBC (probably cheaper than developing in-house iwth Jackcess) – extraneon May 14 '11 at 19:21
2

If you know this well enough:

  • Python, it's database modules, and ODBC configuration

then you should know how to do this:

  • open a database, read some data, insert it in to a different database

If so, then you are very close to your required solution. The trick is, you can open an MDB file as an ODBC datasource. Now: I'm not sure if you can "CREATE TABLES" with ODBC in an MDB file, so let me propose this recipe:

  1. Create an MDB file with name "TARGET.MDB" -- with the necessary tables, forms, reports, etc. (Put some dummy data in and test that it is what the customer would want.)
  2. Set up an ODBC datasource to the file "TARGET.MDB". Test to make sure you can read/write.
  3. Remove all the dummy data -- but leave the table defs intact. Rename the file "TEMPLATE.MDB".
  4. When you need to generate a new MDB file: with Python copy TEMPLATE.MDB to TARGET.MDB.
  5. Open the datasource to write to TARGET.MDB. Create/copy required records.
  6. Close the datasource, rename TARGET.MDB to TODAYS_REPORT.MDB... or whatever makes sense for this particular data export.

Would that work for you?

It would almost certainly be easier to do that all on Windows as the support for ODBC will be most widely available. However, I think in principle you could do this on Linux, provided you find the right ODBC components to access MDB via ODBC.

Dan H
  • 11,508
  • 5
  • 35
  • 32
1

You could export to XML using MS's officedata namespace. Access shouldn't have any trouble consuming that. You can provide a separate xsd schema, or encode types and relationships directly in the document tree. Here's is a simple example:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns="urn:schemas-microsoft-com:officedata">

<Table1><Foo>0.00</Foo><Bar>2011-05-11T00:00:00.000</Bar></Table1>
<Table1><Foo>3.00</Foo><Bar>2011-05-07T00:00:00.000</Bar></Table1>

<Table2><Baz>Hello</Baz><Quux>Kitty</Quux></Table2>
</dataroot>

Googling "urn:schemas-microsoft-com:officedata" should turn up some useful hits.

user474933
  • 11
  • 1
0

I would suggest moving the data into a Microsoft SQL database, then linking or importing the data to access.

Nick ODell
  • 5,641
  • 1
  • 23
  • 47
0

Could you create a self-extracting file to send to the Windows user who has Microsoft Access installed?

  1. Include a blank .mdb file.
  2. dynamically build xml documents with tables, schema and data
  3. Include an import executable that will take all of the xml docs and import into the Access .mdb file.

It's an extra step for the user, but you get to rely on their existing drivers, software and desktop.

JeffO
  • 7,649
  • 3
  • 39
  • 52
0

Well, looks to me like you need a copy of vmware server on the linux box running windows, a web service in the vm to write to access, and communications to it from the main linux box. You aren't going to find a means of creating an access db on Linux. Calling it a requirement isn't going to make it technically possible.

bmargulies
  • 91,317
  • 38
  • 166
  • 290
0

http://adodb.sourceforge.net/ - installs on linux, written in php or python, connects to Access and PostgreSQL.

We've been using it for years, and it works beautifully.

SickHippie
  • 1,374
  • 8
  • 19