CODEWITHSUNDEEP
sqlms-accesspostgresql
machinaut
machinaut

Reputation: 495

How to convert accdb to a postgres database

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.

Upvotes: 8

Views: 12324

Answers (3)

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

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.

Upvotes: 4

Tony Toews
Tony Toews

Reputation: 7882

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.

Upvotes: 3

Ben S
Ben S

Reputation: 69412

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

Upvotes: 1

Related Questions