connect to an outside mongodb from MS Access-CodePudding

There is a similar thread and I like one of the answers there, the one using shell. But it seems to connect to a running instance of mongo.

In my case, there's no running instance, the Mongo db is somewhere else and I can't figure out how to connect to it using this script. I guess i would need a way to add a connection string to an outside MongoDB using an approach similar to the one below.

How to connect Mongodb from Excel

This is the answer

The Shell Approach Pretty much anything that interfaces with the Command Line can be accessed with Shell.

Here's a bare-bones example that connects to a running MongoDB instance and prints a query to the Immediate Window. You'll need to add a reference to the Windows Script Host Object Model.

Private Sub Test()

    Dim wsh As New WshShell
    Dim proc As WshExec
    Dim line As String
    
    Set proc = wsh.Exec("mongo")
    
    With proc
        .StdIn.WriteLine "use test"
        .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
        .StdIn.WriteLine "quit()"
        
        Do While .Status = WshRunning
            line = .StdOut.ReadLine
            If line = "Type ""it"" for more" Then
                .StdIn.WriteLine "it"
            ElseIf line Like "{*" Then
                Debug.Print line
            End If
            DoEvents
        Loop
    End With
End Sub

Just printing the raw JSON strings isn't very exciting or useful, however. You could write your own JSON parser but, for this example, we will use VBA-JSON by Tim Hall (you can find it on GitHub).

At the time of writing, there is one issue with VBA-JSON that has to be tackled when using it to parse strings returned from MongoDB. Any values that contain parentheses, e.g. "_id": ObjectId("..."), will throw an error. A quick and dirty fix for this is to use RegEx to clean the string for the parser. You will need to reference the Microsoft VBScript Regular Expressions 5.5 library for the following function to work.

Private Function CleanString(str As String) As String

    Dim temp As String
    Dim rx As New RegExp
    
    With rx
        .IgnoreCase = True
        .Global = True
        
        .Pattern = "[a-z]*\(" ' Left
        temp = .Replace(str, "")
        .Pattern = "\)" ' Right
        temp = .Replace(temp, "")
    End With
    
    CleanString = temp
End Function

We can then parse the JSON returned from MongoDB and add each object to a Collection. Accessing the values becomes quite simple.

Private Sub Mongo()

    Dim wsh As New WshShell
    Dim proc As WshExec
    Dim line As String
    Dim response As New Collection
    Dim json As Object
    
    Set proc = wsh.Exec("mongo")
    
    With proc
        .StdIn.WriteLine "use test"
        .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
        .StdIn.WriteLine "quit()"
        
        Do While .Status = WshRunning
            line = .StdOut.ReadLine
            If line = "Type ""it"" for more" Then
                .StdIn.WriteLine "it"
            ElseIf line Like "{*" Then
                response.Add ParseJson(CleanString(line))
            End If
            DoEvents
        Loop
    End With
    
    For Each json In response
        Debug.Print json("name"), json("address")("street")
    Next
End Sub

... Which will produce the following output from the MongoDB Example Dataset.

Nectar Coffee Shop          Madison Avenue
Viand Cafe                  Madison Avenue
Don Filippo Restaurant      Lexington Avenue
Lusardi'S Restaurant        Second Avenue
Due                         Third Avenue
Lenox Hill Grill/Pizza      Lexington Avenue
Quatorze Bistro             East   79 Street
Luke'S Bar & Grill          Third Avenue
Starbucks Coffee            Lexington Avenue
New York Jr. League         East   80 Street
Doc Watsons                 2 Avenue
Serafina Fabulous Pizza     Madison Avenue
Canyon Road Grill           1 Avenue
Sushi Of Gari East          78 Street

Gotchas

ReadLine and WriteLine are blocking functions. The window opened by Exec can't be hidden. A workaround for both of the above would be to use a two-layer approach, where VBA calls a hidden script using wsh.Run, which then runs the Exec (as well as any other code that interacts with the proc). The downside to this approach is that StdIn (and to an extent StdOut) has to be written to a file.

CodePudding user response：

To connect to an external MongoDB, simply adjust the Windows Shell call to point to external address. Per MongoDB docs, mongo by itself defaults to localhost at port 27017. For a remote host, adjust these defaults.

Using connection string:

Set proc = wsh.Exec("mongo ""mongodb://username:password@host:port/database""")

Using args:

Set proc = wsh.Exec("mongo --host <server_or_ip_address>" _
                  & "      --port <port_number>" _
                  & "      --username <username>" _
                  & "      --password <password>")

Above would require having the mongo shell installed on client machine even with no database set up locally. Also, the server machine hosting the MongoDB must allow external connections. Read docs for setup and instructions.