Thread: getting mdb(microsoft acess) file in postgresql

getting mdb(microsoft acess) file in postgresql

From
"nitin quick"
Date:
How can i read or import a mdb database file into postgresql?

regards
quickNitin

Re: getting mdb(microsoft acess) file in postgresql

From
Glenn Davy
Date:
On Tue, 2006-07-25 at 16:51 +0530, nitin quick wrote:
> How can i read or import a mdb database file into postgresql?
sor
its years since Ive done it, so there might be fine detail to sort along
the way. but you can...

* Connect to postgres with odbc and link tables into mdb file then make
'append' queries in access
*  Export to CSV from access then use the postgresql copy command to
import the csv data
* if your on *nix then you might be able to use  kexi to do a query that
copies from the mdb file to postgres as I believe it handles both. There
are also other similar tools in the gnome stable I believe

>regards
> quickNitin

Re: getting mdb(microsoft acess) file in postgresql

From
Richard Broersma Jr
Date:
> How can i read or import a mdb database file into postgresql?

From my experience,  you will have to hand create new ddl (data definition language) statements
for the purpose of making new tables in postgresql that will mirror the tables in the access
database.  I do not believe that it will be a simple "copy/paste" procedure.


However, I do believe this processes could be automated by creating a VBE script that would
generate DDL pass-though-queries to postgres by scanning all of the tables{columns, indexes,
constraints, and references), and queries.

Once this is completed you can export each table data into a csv file which will be copied into
the mirrored postgresql table using the copy command from psql.  Alternatively, if you wanted to
automate this process, you could use VBE and the PostgreSQL ODBC driver to push data from the
access table to the postgresql tables using insert statements.

additionally I googled this topic and it returned a few useful links that you can use for further
ideas.
http://www.greenleaftech.net/articles/2006/jun/03/migrate-msaccess-to-postgresql/
http://www.data-conversions.net/products.php?prod_num=3&&dest=MENU&&ID=110
http://convert-access-to-postgresql.qarchive.org/


Regards,

Richard Broersma Jr.


Re: getting mdb(microsoft acess) file in postgresql

From
"A. Kretschmer"
Date:
am  25.07.2006, um 16:51:46 +0530 mailte nitin quick folgendes:
> How can i read or import a mdb database file into postgresql?

Please read: http://techdocs.postgresql.org/#convertfrom


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Re: getting mdb(microsoft acess) file in postgresql

From
"David Chapman"
Date:
Post the folowing code as a new module in your Access database, then ececute the "createsql" sub. It will create a new file of commands that can be executed on the PostgreSQL server, that will create the tables and populate them with the Access data. I got the idea from PGdump. I did not bother with users and groups.
 
Regards David
------------------------------------------------------------------------------------------
 

Option Compare Database
Option Explicit
Public SetSequence As String
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Public Schema As String


Sub CreateSQL()
Dim dbs As Database
Dim tdf As TableDef
Dim ConnectPath
    Schema = LCase(Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1, InStrRev(CurrentDb.Name, ".") - InStrRev( CurrentDb.Name, "\") - 1))
   
 '   Schema = Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1)

    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs    ' Loop through all tables in the database.
                                ' If the table has a connect string, it's a linked table.
        If Len( tdf.Connect) > 0 Then SQL (LCase(tdf.Name))
    Next
End Sub

Public Function SQL(TableName As String)
    CreateTableCommand TableName
    CreateInsertCommands TableName
    If Not SetSequence = "" Then
        Dim outfile As String
        outfile = "Sql" & TableName & ".txt"
        Dim fs, F, ts, S, Field
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set F = fs.GetFile(outfile)
        Set ts = F.OpenAsTextStream(ForAppending, TristateUseDefault)
        ts.write SetSequence
        ts.Close
    End If
End Function

Sub CreateTableCommand(TableName As String)
    SetSequence = ""
Dim outfile As String
    outfile = "Sql" & TableName & ".txt"
Dim outTable As String
'    outTable = LCase(TableName) & "Access"
    outTable = LCase(TableName)
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim RS As Recordset
    Set RS = CurrentDb.OpenRecordset (TableName)
    Dim OutString As String
    Dim Field As Field, firstfield As Boolean
    firstfield = True
    OutString = "CREATE TABLE " & Schema & "." & outTable & " ("
    For Each Field In RS.Fields
        If firstfield Then
            firstfield = False
            OutString = OutString & vbNewLine & Left(LCase(Field.Name) & "                   ", 20)
        Else
            OutString = OutString & "," & vbNewLine & Left(LCase(Field.Name) & "                   ", 20)
        End If
        Select Case Field.Type
            Case Is = 3
                    OutString = OutString & "integer"
            Case Is = 4
                If AutoIncField(Field.Attributes) Then
                    OutString = OutString & "SERIAL"
                    SetSequence = SetSequence & "SELECT  Setval('" & Schema & "." & TableName & "_" & Field.Name & "_seq',max(" & Field.Name & ")) from " & Schema & "." & TableName & ";" & vbNewLine
                Else
                    OutString = OutString & "integer"
                End If
            Case Is = 10
                If Field.Size = 1 Then
                    OutString = OutString & "char"              ' Will import to char
                ElseIf Field.Size < 256 Then
                    OutString = OutString & "varchar(" & Field.Size & ")"      ' Will import back to text
                Else
                    OutString = OutString & "text"                  ' Will import back to Memo
                End If
            Case Is = 8
                OutString = OutString & "date"
            Case Is = 1
                OutString = OutString & "boolean"
            Case Else
                OutString = OutString & "text"      ' Will import back to memo - case else includes memo fields
        End Select
        If Not Field.defaultvalue = "" Then         ' The DefaultValue property doesn't apply to AutoNumber and Long Binary fields.
            OutString = OutString & " Default '" & Field.defaultvalue & "'"
        End If
    Next
    RS.Close
    Set RS = Nothing
    OutString = OutString & ");" & vbNewLine & vbNewLine
    Dim fs, F, ts, S
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CreateTextFile outfile            'Create a file
    Set F = fs.GetFile(outfile)
    Set ts = F.OpenAsTextStream (ForWriting, TristateUseDefault)
    ts.write OutString
    ts.Close

End Sub
Sub CreateInsertCommands(TableName As String)
Dim outfile As String
    outfile = "Sql" & TableName & ".txt"
Dim outTable As String
'    outTable = LCase(TableName) & "Access"
    outTable = Schema & "." & LCase(TableName)
Dim fs, F, ts, S, Field
Dim firstfield As Boolean
    Set fs = CreateObject("Scripting.FileSystemObject")
'    fs.CreateTextFile " SqlCmds.txt"            'Create a file
    Set F = fs.GetFile(outfile)
    Set ts = F.OpenAsTextStream(ForAppending, TristateUseDefault)
Dim RS As Recordset
    Set RS = CurrentDb.OpenRecordset(TableName)
    Dim OutString As String, qw As String
    While Not RS.EOF
        OutString = "INSERT INTO " & outTable & vbNewLine & "VALUES ("
        firstfield = True
        For Each Field In RS.Fields
            If firstfield Then
                firstfield = False
            Else
                OutString = OutString & ","
            End If
            If IsNull(Field.Value ) Or IsEmpty(Field.Value) Then
                OutString = OutString & "Null"
            Else
                Select Case Field.Type
                    Case Is = 3
                        OutString = OutString & Field.Value
                    Case Is = 4
                        OutString = OutString & Field.Value
                    Case Is = 10
                        OutString = OutString & "'" & Replace( Field.Value, "'", "\'") & "'"
                    Case Is = 8
                        If IsDate(Field.Value) Then
                            OutString = OutString & "'" & Year( Field.Value) & "-" & Month(Field.Value) & "-" & Day(Field.Value) & "'"
                        Else
                            OutString = OutString & "Null"
                        End If
                    Case Is = 1
                        If Field.Value Then
                            OutString = OutString & "'true'"
                        Else
                            OutString = OutString & "'false'"
                        End If
                    Case Else
                        OutString = OutString & "'" & Replace( Field.Value, "'", "\'") & "'"
                End Select
            End If
        Next
        OutString = OutString & ");" & vbNewLine
        ts.write OutString
        RS.MoveNext
    Wend
    RS.Close
    Set RS = Nothing
    ts.Close
End Sub
Function ConvertToDate(DS) As Date
    If IsNull(DS) Or Len(DS) = 0 Then
        ConvertToDate = "1/1/1800"
        Exit Function
    End If
    Dim ddot As Integer, ydot As Integer, yy, mm, dd
    ddot = InStr(DS, ".")
    If ddot = 0 Then ddot = InStr(DS, "/")
    If ddot = 0 Then ddot = InStr(DS, "-")
    If ddot = 0 Then
        ConvertToDate = "1/1/1800"
        Exit Function
    End If
    ydot = InStr(ddot + 1, DS, ".")
    If ydot = 0 Then ydot = InStr(ddot + 1, DS, "/")
    If ydot = 0 Then ydot = InStr(ddot + 1, DS, "-")
    If ydot = 0 Then
        ConvertToDate = "1/1/1800"
        Exit Function
    End If
    dd = Left(DS, ddot - 1)
    mm = Mid(DS, ddot + 1, ydot - ddot - 1)
    yy = Mid(DS, ydot + 1)
    ConvertToDate = DateSerial(yy, mm, dd)
   
End Function
Function AutoIncField(nbr As Long) As Boolean
Dim NBRstr As String, v As Integer
    AutoIncField = False
    For v = 1 To 5
        If v = 5 Then AutoIncField = nbr Mod 2
         If nbr < 1 Then
            Exit For
        End If
        nbr = Int(nbr / 2)
   Next v
End Function


 

 

On 7/25/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
am  25.07.2006, um 16:51:46 +0530 mailte nitin quick folgendes:
> How can i read or import a mdb database file into postgresql?

Please read: http://techdocs.postgresql.org/#convertfrom


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===    Schollglas Unternehmensgruppe    ===

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match



--

David Chapman
David's Backyard Computing

PLEASE RECORD MY NEW EMAIL ADDRESS
david.luckychap@gmail.com