How to convert accdb to a postgres database

Question

I need to use an .accdb database, and to do that it needs to be imported into PostgreSQL. I believe this would be a simple and straightforward problem (I expected it had been already solved), but a simple solution escapes me.

I'll add that I don't have access to Access (lol), and my solution is loosely dependant on that. If thats impossible I can go find someone with access and get them to convert each table to .csv's or something like that.

Avow

Are you only interested in the data e.g. don't you need to extract the schema: tables, VIEWs, PROCEDUREs, etc? What about MS Access-specific features: Forms, Reports, etc? – Battleax

For now, just the data. But you bring up a very good point, as a general/utilitarian solution to this might include this though. – Deprecate

Possible duplicate of Convert Access to PostgreSQL? – Durative

Answer 1

You don’t need MS Access installed on a computer to read data out of an Access Database Engine file.

All copies of windows since windows 2000 ships with the Access Database Engine; however this will be the Jet 4.0 engine and you will need the components for the ACE (2007) engine. Happily, it is available for download from Microsoft as 2007 Office System Driver: Data Connectivity Components.

Any programming language that supports com objects will left you lift out data without having MS Access installed. You can even use windows scripting here and not even install ANY software on your windows box.

The following code is for the Jet mdb version of the Access Database Engine but you may be able to adapt it for your needs using ACEDAO:

  Set dbEng = CreateObject("DAO.DBEngine.36")
  strMdbFile = "C:\Documents and Settings\" & _
               "Albert\My Documents\Access\" & _
               "ScriptExample\MultiSelect.mdb"
  Set db = dbEng.OpenDatabase(strMdbFile)
  strQuery = "select * from contacts"
  Set rs = db.OpenRecordset(strQuery)
  rs.MoveFirst
  If rs.EOF = True Then
     Quit
  End If

  strTextOut = "C:\t5.txt"
  Set fs = Wscript.CreateObject("Scripting.FileSystemObject")
  Set ts = fs.OpenTextFile(strTextOut, 2, True)
  '2 = write, 1 = read

  Do While rs.EOF = False
     strOutText = rs("LastName")
     ts.Writeline strOutText
     rs.MoveNext
  Loop
  ts.Close
  rs.Close

However, really, if this is one time export, then finding someone with a copy of MS Access will make this less work, but you CAN read a access file without having to install ANY software. In fact, as noted above, even a virgin install of windows would allow you to use the above windows script file which also can be run without any software having been installed on the windows box.

Kavanaugh

Alright, this is awesome solution for windows users. I'm on linux, and I'm trying to figure out how to make it work with Wine. – Deprecate

Answer 2

One option would be Excel as it can read Access data files.

Another is MDB Tools is a set of open source libraries and utilities to facilitate exporting data from MS Access databases (mdb files) without using the Microsoft DLLs. Thus non Windows OSs can read the data.

Houppelande

Answer 3

If you can save each table as a CSV files, you can simply import those into many DBMS's, including PostgreSQL.

Kadi

I don't have Access with which to open the .accdb to save as .csv's. Does something else open .accdb's and save them as .csv's? – Deprecate

You might be able to find a free utility that converts .accdb to .csv Googling seemed promising. – Kadi

It might be easier to just ask a friend who has access to to Access to do it for you. (If the data isn't confidential) – Kadi