How to connect to a web-API enabled database via Access VBA-CodePudding

On Windows 2007, I'm trying to use the following Access VBA subroutine I found on the internet to connect an Access (2016 64 bit) database to another web-API enabled Access (2016 64 bit) database to extract some data elements:

Sub DownloadFile0()

Dim Stm As ADODB.Stream
Dim Rec As New ADODB.Record
Dim Conn As New ADODB.Connection
Dim Url As String, Str As String

Url = "http://www.elesteshary.com/sosdata.accdb/sdr/doll_per_sdr" ' URL, database, record, and field names

Conn.Provider = "ExOLEDB.DataSource"
Conn.Open Url
Rec.Open Url, Conn
Set Stm = Rec.Fields(adDefaultStream).Value
Str = Stm.ReadText
...

Conn.Close
Rec.Close
Stm.Close
Set Conn = Nothing
Set Rec = Nothing
Set Stm = Nothing

End Sub

When run, I received "Provider not found". Advise what provider may I use instead of "ExOLEDB" and how can I get it? When I bypass "Conn.Provider" statement I received "Data source name too long". Is this because the provider is missing?. Please correct the code if something is wrong. If all are set, will "Str" contain the value of "doll_per_sdr"? "sdr" is a one record table. What if I need to query a field from a specific record of a multi-record table. Finally, can this subroutine extract data from a database other than that of Access?

Note: It is not allowed to download the "sosdata.accdb".

Best regards.

Said El Noshokaty, PhD, Professor of Information Systems

CodePudding user response：

You can't do this. The code, you've found, requires a specific URL syntax and a web server knowing how to resolve it to be able to know:

the database to open (sosdata.accdb)
the table (sdr) to use in the database
the index (doll_pr_sdr) of the record to retrieve from the table
return that record

There is no such thing as an out-of-the-box web-API enabled Access (2016 64 bit) capable of this. Such an API would be built using a web server that publishes an API service that (internally) interacts with the Access database.

Addendum:

As you can download the file, you can use DAO for this.

Set References to include these:

Download and insert module Internet.bas from my project VBA.PictureUrl
Create a new module and copy-paste this code:

Option Compare Database
Option Explicit

Public Function DownloadSdrValue() As Currency

    Const Url       As String = "http://www.elesteshary.com/sosdata.accdb"
    
    Dim Database    As DAO.Database
    Dim Records     As DAO.Recordset

    Dim FileName    As String
    Dim Value       As Currency
    
    FileName = DownloadCacheFile(Url)
    Set Database = DBEngine(0).OpenDatabase(FileName)
    
    Set Records = Database.OpenRecordset("Select * From sdr")
    If Records.RecordCount > 0 Then
        Records.MoveFirst
        Value = Records.Fields("doll_per_sdr").Value
    End If
    Records.Close
    Database.Close
    
    Debug.Print "Value:", Value
    
    DownloadSdrValue = Value
    
End Function

Compile and save the modules
Retrieve the value like this:

    DollarPerSdr = DownloadSdrValue

Result: 1.57