• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to connect odata in Ms Access

trprasad78

trprasad78

Member
In Excel we have option to connect odata, But i not see same option in Ms access.

Please suggest.

Screen shot of Excel attached.
upload_2017-10-4_22-37-23.png
 
There is nothing that's built-in to my knowledge. And likely will not be, since you should not link tables to web service. Rather you should consume the properties and methods of the web service in the code and import data into tables.

You will need to write custom code. If you use .NET, it has good support for OData. However, you can still write VBA code to consume OData.

Read through articles posted by Marcelo Lopez Ruiz. Though it's bit hard to find all the relevant articles. Below is good starting point.
https://blogs.msdn.microsoft.com/ma...nsuming-odata-with-office-vba-part-iv-access/
 
Chihiro said:
There is nothing that's built-in to my knowledge. And likely will not be, since you should not link tables to web service. Rather you should consume the properties and methods of the web service in the code and import data into tables.

You will need to write custom code. If you use .NET, it has good support for OData. However, you can still write VBA code to consume OData.

Read through articles posted by Marcelo Lopez Ruiz. Though it's bit hard to find all the relevant articles. Below is good starting point.
https://blogs.msdn.microsoft.com/ma...nsuming-odata-with-office-vba-part-iv-access/
Click to expand...
Thank you for intimidate respond @Chihiro let me go through
 
As this is a site for Excel you may be very limited in help for Access if you had tried an Access help forum you would have been helped in doing this as it is there in the ribbon
 
@bobhc

Are you sure? There's no out of the box connector for OData that ships with MS Access. It requires 3rd party connector.
 
upload_2017-10-5_17-13-48.png


You can use the CData ODBC Driver for OData 2015 to import data into an Access table and to create a linked table.

Import a Table into Access
To import a table into Access, follow the steps below:
  1. Click External Data -> ODBC Database from the ribbon. This opens the ODBC Database wizard. Select the option to import the source data into a new table in the current database.
  2. Next, select the CData OData data source from the Machine Data Source tab.
  3. Select tables to import into Access.
Importing data into Access is a onetime import; this means that any changes made to the Access table will not be reflected back to the main data source and vice versa. Use linked tables if you want the changes to propagate back to the data source.
Link a table in Access
To link a table in Access to a table in OData, follow the steps below:
  1. Click External Data -> ODBC Data sources from the ribbon.
  2. Select the option to link to the data source by creating a linked table.
  3. On the next page of the wizard, select the CData OData data source in the Machine Data Source tab.
Once you select the correct data source, you will be presented with a list of tables. Here you can select a table that you want as a linked table in Access. The linked table will always have fresh data and any changes made to it will be reflected back to the underlying table in OData.
Limitations in Access
There are a few limitations with using the driver from Microsoft Access in linked mode.

Inserting a new row may result in Access showing a new row with a value of #deleted for all of its fields. This is because Access is unable to select the recently inserted row correctly. The row was created, which a refresh will verify. Optionally, you can also set the MaximumColumnSize to less than 255; this will allow Access to select the inserted row.
 
Hah, I thought you meant ribbon tool much like Excel with native OData connection to see *.svc data structure.

Yep, I'm aware of that commercial connector. It's a good one at $300/yr.
 
What i did was i had link odata with excel file and excel link with Ms Access.
only think is every time i need to open excel to refresh the excel data.

I though of write a macro from access for refresh all the excel file.
once i click the button it has to open excel file one by one and save the file then close to open the next. it has to do until the last file of that folder.
 
bobhc said:
View attachment 46142

Link a table in Access
To link a table in Access to a table in OData, follow the steps below:
  1. Click External Data -> ODBC Data sources from the ribbon.
  2. Select the option to link to the data source by creating a linked table.
  3. On the next page of the wizard, select the CData OData data source in the Machine Data Source tab.
.
Click to expand...
Only problem in "select the CData OData data source in the Machine Data Source tab." data source not showing.
 
You have to install the 3rd party connector. You can get trial, but will eventually need to purchase it as noted in my post above.
 
You must log in or register to reply here.
Back
Top