Extracting data from a Database that was built on access

Question

How can I open up a database using access and not the GUI that it runs through?

Hi, I am trying to extract the data from a Database that was built on access but does not use access to run. It is an old software called "Gemma" that was released by Castle Personnel back in 1999. They no longer offer support for their database and we as a company are trying to migrate over to a different browser based online database.

When opening Gemma it does not open via Access but has its own GUI. When I open the location of the database, it is saved as a .mdb I was playing around with these files one day and clicked shift enter when I had a database selected. It opened the whole database in Access which obviously gave me access to all of the data. However, this method does not seem to work anymore.

Any help would be much appreciated

See the info on opening an MDB file from Excel at the following link: http://stackoverflow.com/questions/24116593/access-2013-cannot-open-a-database-created-with-a-previous-version-of-your-app — tlemaster, Dec 19 '16 at 21:14
If think I can download Access95 from my Open Licenses portal or from MSDN professional. — McNets, Dec 19 '16 at 22:23

score 1 · Answer 1 · answered Dec 20 '16 at 18:29

Simply fire up a copy of Access, and THEN open the database in question. As you note, holding down the shift key works in most cases (but it can be disabled). So NOTE how I am suggesting to open Access first (not launch access by clicking on an mdb file which THEN launches Access based on the file extension).

If above does not work (holding down shift key), then simply create a blank new database and then import all of the objects from your mdb. I recommend this process since:

You don’t have to write code (as per other posts here)

Importing removes all of the start-up settings (and thus gets around any start up code settings).

When you import the tables from that application, you can (should) be using a newer version of Access.

So don’t try and open that access file – fire up Access and create a blank file and THEN import the tables.

score 0 · Answer 2 · answered Dec 20 '16 at 14:24

Since it's only back end stuff, you don't have to worry about forms or reports. You can programmatically open the database using ODBC, read the tabledefs, read the fields in each table and then create the table in a blank database. Something like:

For Each tblRep In dbRep.TableDefs
Set rec1 = dbRep.OpenRecordset("SELECT MSysObjects.Name FROM MsysObjects WHERE ([Name] = '" & tblRep.Name & "') AND ((MSysObjects.Type)=4 or (MSysObjects.Type)=6)")

If rec1.EOF Then
  XF = 0
Else
  XF = 1
End If

    ' Ignore system tables and CMDB tables.
    If InStr(1, tblRep.Name, "MSys", vbTextCompare) = 0 And _
        InStr(1, tblRep.Name, "CMDB", vbTextCompare) = 0 And _
        XF = 0 Then

        '***** Table definition
        ' Create a table definition with the same name.
        Set tblNew = dbNew.CreateTableDef(tblRep.Name)

        ' Set properties.
        tblNew.ValidationRule = tblRep.ValidationRule
        tblNew.ValidationText = tblRep.ValidationText

        ' Loop through the collection of fields in the table.
        For Each fldRep In tblRep.Fields

            ' Ignore replication-related fields:
            ' Gen_XXX, s_ColLineage, s_Generation, s_GUID, s_Lineage
            If InStr(1, fldRep.Name, "s_", vbTextCompare) = 0 And _
                InStr(1, fldRep.Name, "Gen_", vbTextCompare) = 0 Then

                '***** Field definition
                Set fldNew = tblNew.CreateField(fldRep.Name, fldRep.Type, _
                    fldRep.Size)

                ' Set properties.
                On Error Resume Next
                fldNew.Attributes = fldRep.Attributes
                fldNew.AllowZeroLength = fldRep.AllowZeroLength
                fldNew.DefaultValue = fldRep.DefaultValue
                fldNew.Required = fldRep.Required
                fldNew.Size = fldRep.Size

                ' Append the field.
                tblNew.Fields.Append fldNew
                'On Error GoTo Err_NewShell
            End If
        Next fldRep

        '***** Index definition

        ' Loop through the collection of indexes.
        For Each idxRep In tblRep.Indexes

            ' Ignore replication-related indexes:
            ' s_Generation, s_GUID
            If InStr(1, idxRep.Name, "s_", vbTextCompare) = 0 Then

                ' Ignore indices set as part of Relation Objects
                If Not idxRep.Foreign Then

                    ' Create an index with the same name.
                    Set idxNew = tblNew.CreateIndex(idxRep.Name)

                    ' Set properties.
                    idxNew.Clustered = idxRep.Clustered
                    idxNew.IgnoreNulls = idxRep.IgnoreNulls
                    idxNew.Primary = idxRep.Primary
                    idxNew.Required = idxRep.Required
                    idxNew.Unique = idxRep.Unique

                    ' Loop through the collection of index fields.
                    For Each fldRep In idxRep.Fields
                        ' Create an index field with the same name.
                        Set fldNew = idxNew.CreateField(fldRep.Name)
                        ' Set properties.
                        fldNew.Attributes = fldRep.Attributes
                        ' Append the index field.
                        idxNew.Fields.Append fldNew
                    Next fldRep

                    ' Append the index to the table.
                    tblNew.Indexes.Append idxNew
                End If
            End If
        Next idxRep

        ' Append the table.
        dbNew.TableDefs.Append tblNew
    End If
Next tblRep

In a similar fashion, you would re-create all of the relationships, macros, queries and modules. Then, just use similar code to copy them over:

' Loop through the list of table definitions.
For Each tblRep In dbRep.TableDefs
Set rec1 = dbRep.OpenRecordset("SELECT MSysObjects.Name FROM MsysObjects WHERE ([Name] = '" & tblRep.Name & "') AND ((MSysObjects.Type)=4 or (MSysObjects.Type)=6)")

If rec1.EOF Then
  XF = 0
Else
  XF = 1
End If

    ' Ignore system tables and CMDB tables.
    If InStr(1, tblRep.Name, "MSys", vbTextCompare) = 0 And _
        InStr(1, tblRep.Name, "CMDB", vbTextCompare) = 0 And _
        XF = 0 Then

        ' Open a recordset for the new table.
        Set rstNew = dbNew.OpenRecordset(tblRep.Name, dbOpenTable)
        ' Open a recordset for the old table.
        Set rstRep = dbRep.OpenRecordset(tblRep.Name, dbOpenTable)

        ' Continue if there are records.
        If Not rstRep.BOF Then

            ' Move to the first record.
            rstRep.MoveFirst

            ' Loop through all the old table records.
            Do Until rstRep.EOF
                ' Add a record to the new table.
                rstNew.AddNew
                ' For each field in the new table, set the value
                ' to the value in the related field of the old table.
                For intC = 0 To rstNew.Fields.count - 1
                    rstNew.Fields(intC).Value = _
                        rstRep.Fields(rstNew.Fields(intC).Name).Value
                Next
                ' Update the new table.
                rstNew.Update
                ' Move to the next old table record.
                rstRep.MoveNext
            Loop ' rstRep
        End If

        ' Close the new recordset.
        rstNew.Close
        ' Close the old recordset.
        rstRep.Close
    End If
Next tblRep

score 0 · Answer 3 · answered Dec 20 '16 at 14:26

Create empty Access database and link tables from MDB file to new database. You should be able to perform queries and reports out of linked tables, or perhaps backup and move of data.

I would strongly suggest to backup all data first, before making any changes this way.

Extracting data from a Database that was built on access

3 Answers3