MDB to JSON converter [closed]

Question

I got a Microsoft Access MDB file from a client. It has about 10 tables, I do only need one of them. For further usage I have to convert that table into a JSON file. Is there any way to achieve this (freeware or online tool highly appreciated)?

I'm on OS X 10.11 and windows is no option.

Rew

github.com/VBA-tools/VBA-JSON (Win & Mac) – Transcript

Sadly, also excel is not an option – Rew

It is a VBA library and works with every Office program. – Transcript

Answer 1

Yes there is a solution you can just take the mdb access database file to some one who has windows and use this program on the the file to extract or convert MS Access .mdb file to json file you can find the proram in this link and how to use it in this clip on youtube

https://www.youtube.com/watch?v=6adHmSfWs-o the program http://konbrand.com/DownLoads/

Jephum

Much too late, but that was sadly the only working solution. Now I always have to ask someone using windows... – Rew

Answer 2

For anyone else that needs an online Access database converter (mdb, accdb), I have built an online tool that converts almost all versions of Access databases to JSON, MySQL dump file or SQLite.

Here is the link: https://lytrax.io/blog/tools/access-converter

Any feedback, bug reports or suggestions are highly appreciated.

Gaullist

Excellent website. Software development at it's best. One simple goal, simple to use, and instant results. Great job !! – Howell

Answer 3

Here's a Microsoft Access database module function to export a table or query (passed as a string) and create a JSON date time stamped text file within the same folder as the Access database:

Function toJSON(PassTblQry)
' EXPORT JSON FILE FROM TABLE OR QUERY
Dim mydb As Database, rs As Recordset
Dim VarField(255), VarFieldType(255)
Dim fld As DAO.Field, VarDat As String
Set db = CurrentDb
fn = CurrentProject.Path & "\" & PassTblQry & " " & Format(Now(), "YYYY-MM-DD HHMM") & ".json" ' define export current folder query date/time
Open fn For Output As #1    ' output to text file
Recs = DCount("*", PassTblQry) ' record count
Set rs = db.OpenRecordset("Select * from [" & PassTblQry & "]")
Nonulls = True ' set NoNulls = true to remove all null values within output ELSE set to false
fieldcount = 0
' Save field count, fieldnames, and type into array
For Each fld In rs.Fields
    fieldcount = fieldcount + 1
    VarField(fieldcount) = fld.Name
    'Debug.Print VarField(fieldcount)
    VarFieldType(fieldcount) = "TEXT"
    Select Case fld.Type
        Case 4, 5, 6, 7 ' fieldtype 4=long, 5=Currency, 6=Single, 7-Double
            VarFieldType(fieldcount) = "NUMBER"
    End Select
Next
Set fld = Nothing
Print #1, "[" ' start JSON dataset
' build JSON dataset from table/query data passed
Do While Not rs.EOF
    Print #1, "{"  ' START JSON record
    ' build JSON record from table/query record using fieldname and type arrays
    For looper = 1 To fieldcount
        VarFT = VarFieldType(looper)
        If VarFT = "NUMBER" Then QuoteID = ""     ' No quote for numbers
        QuoteID = Chr(34) ' double quote for text
        If IsNull(rs(VarField(looper)).Value) Then  ' deal with null values
            VarDat = "Null": QuoteID = ""   ' no quote for nulls
            If Nonulls = True Then VarDat = "": QuoteID = Chr(34)                       ' null text to empty quotes
            If Nonulls = True And VarFT = "NUMBER" Then VarDat = "0": QuoteID = ""      ' null number to zero without quotes
            Else
            VarDat = Trim(rs(VarField(looper)).Value)
        End If
        VarDat = Replace(VarDat, Chr(34), "'") ' replace double quote with single quote
        VarDat = Replace(VarDat, Chr(8), "")   ' remove backspace
        VarDat = Replace(VarDat, Chr(10), "")  ' remove line feed
        VarDat = Replace(VarDat, Chr(12), "")  ' remove form feed
        VarDat = Replace(VarDat, Chr(13), "")  ' remove carriage return
        VarDat = Replace(VarDat, Chr(9), "   ")  ' replace tab with spaces
        jsonRow = Chr(34) & VarField(looper) & Chr(34) & ":" & QuoteID & VarDat & QuoteID
        If looper < fieldcount Then jsonRow = jsonRow & "," ' add comma if not last field
        Print #1, Chr(9) & jsonRow
    Next looper
    Print #1, "}";  ' END JSON record
rs.MoveNext
If Not rs.EOF Then
    Print #1, "," ' add comma if not last record
    Else
    Print #1, ""
End If
Loop
Print #1, "]"  ' close JSON dataset
Close #1

End Function

Epigenesis

Answer 4

It is quiet late but after read your answer, I finally used nodeJS to extract data from access and format it to Json as follow:

{export.ts}

import * as adodb  from "node-adodb";
import * as fs from "fs";

// write to a new file named 2pac.txt

const connection = adodb.open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;');



async function toJSON(filename:string,sql:string) {
  try {
    const data = await connection.query(sql);
    const dataString=JSON.stringify(data, null, 2);

    fs.writeFile('data/' + filename, dataString, (err) => {  
      if (err) throw err;
      console.log(filename + ' saved!');
  });
  } catch (error) {
    console.log(filename + ' error !');
    console.error(error);
  }
}

Ogive