API in access database (1 Viewer)

Hi Guys

Just wondering if we can use APIs using access database? What is it purpose?

Can anyone please give me some examples that will explain some benefits of APIs.

Many Thanks

you use API's to communicate with other systems - typically web based.

So if you wanted to find the geo coordinates of a postcode, you could use the google maps API to send the post code and receive back the geo coordinates.

Some API's are intended for 'home use' i.e. are free but perhaps have limited volumes, but if you want to use commercially (e.g. to get and maintain geo coordinates of your customers) there is usually a license fee to be paid.

For examples - almost limitless and what is of value to one person means nothing to someone else. but some basic functions

communicating with google apps
communicating with web databases (azure/salesforce/your website db etc)
sending/receiving sms text messages

basic vba code structure is the same - plenty of examples about

Thanks CJ_London, I haven't used API before so just wondering is it easy to use ApI.

My whole application(Frontend and Backend) is built in access . It records all the employees activities like Reviews,Meetings,absence,call audits,action plans etc...so do I need to use a different frontend and backend in order to use API?

Thanks

Just to add to CJ London's comments & answer your latest questions:

1. Like anything else, APIs aren't easy to use at first but after a while they get easier. As CJ said, there are plenty of examples online.
Download & try some out
2. There's no point using them unless there's a good reason
3. You only need to use the API in the module where you use it - the frontend
4.. You will need to declare the API differently for 32-bit or 64-bit Access.
For example:

backend is just data so no impact at all

front end should not be a problem but you would only use them if you require the functionality. As you have described your db, I don't see a requirement

Just google 'vba api example' or similar for loads of hits of how to write the code. Many are for excel, but the principle is exactly the same for access.

It's not difficult - you will find instructions for specific api's on line, typically by the resource owner (google, salesforce, amazon etc) and depending on requirements you may need to register in order to gain access to the data and you may need multiple licences for different users - in which case you would need a bit of a setup to store against a user login.

@Colin, think we are talking two different API's - but both valid. Since I'm working on one now I was thinking web. Didn't think about the windows API's So good point

@CJ
I did wonder when you talked about payment...
I've never paid for any I use & I do have quite a few

Anyway, the points we both made should be relevant to the OP

@Aman
For info, I use APIs for many things.

I've just checked one large db of mine & it uses loads of API calls with uses that include:
- fast copying of multiple files e.g. during version updates
- limiting keyboard input
- handling SMS & voice messages (Twilio)
- checking for Internet connections
- grabbing selected text from web pages
- working with active directory
- using / clearing the clipboard
- show / hide the application window & taskbar
- working with ribbons
- calculating disk free space before making backups
- controlling mouse wheel scrolling
- reading / writing to the registry
- running programs remotely
- resizing forms automatically depending on screen size & resolution
- working with MS terminal services
- file dialogs

and more ...

Can I stress that I'm not trying to show off here....

Every API call that I use has been downloaded from the interweb where I had a need for additional functionality that the API provided.

I don't always understand what they mean but if I can use them, that's fine

I've never paid for any I use & I do have quite a few

Click to expand...

one of my clients is a healthcare company with over 100k clients and 10k nursing staff. They use google maps to determine planned staff travel times between clients which is done on a daily basis. travel times between postcodes are logged so they don't have to be calculated again but still a very high usage.

You are not paying for the API - you are paying for the commercial quantities of data

Aman:

An "Applications Programming Interface" or API is a fancy way of saying that you can use Access VBA code to ask another program than Access to do something for you without having to go through the mouse and keyboard to use the GUI or "Graphic User Interface" to do it. Here's the question you must ask yourself: Why do I want to talk to or use the program on the other side of that API?

If, for example, you wanted to create a text file, VBA can do that for you directly. But if you wanted it to be in WORD format, you would create the text of your file and then use the WORD API to convert it. If you wanted to make your file in EXCEL format, you would use a query (perhaps) to export to a spreadsheet and then use the EXCEL API to address formatting issues. And so on...

If you have a specific function that requires talking to another program than Access then you use APIs. That includes other Office programs and it also includes Windows itself, which has an extensive set of API calls.

But here is the question you need to answer first for yourself and then for us so we can know the best way to help you: What are you trying to do that needs help from another program than Access? Because your problem description sounds pretty much like something Access can do all by itself.

CJ_London said:

one of my clients is a healthcare company with over 100k clients and 10k nursing staff. They use google maps to determine planned staff travel times between clients which is done on a daily basis. travel times between postcodes are logged so they don't have to be calculated again but still a very high usage.

You are not paying for the API - you are paying for the commercial quantities of data

Click to expand...

GOTCHA...
BTW thx for your modification to the closing all VBE windows post.
As I use that library for other purposes I hadn't tried using late binding for that.

Thanks Guys for all the information.

As I discussed earlier my whole application is built in Access, I got these questions from my Boss:

Q. What is the feasibility of getting Access products into an XML format, do you have ASCII text capability?

Q. Does the above result in complete rewrite or is conversion possible?

Q. What other options are there for platforming the systems (i.e. SQL Server) and what are the pros / cons

Guys can please provide answers to the above questions as I am not quite sure? I might not have right skills for the task but I am a quick learner and will put a lot of effort into this to learn new technology .

Well those are completely different questions to the original post.
They are also very wide ranging

If you search this site, you should find answers for each of those questions.

Suggest you read them & do other Google searches first.
Then come back with more specific questions

questions back

1. what do you mean by getting Access products into an XML format? You can export queries as XML and import from XML. If you want a multi table XML (e.g. invoice header/invoice rows) it can be done with code

2. ditto what do you mean by 'ASCII text capability'

3. depends on what your code is like now. Subject to question above, if it is well written then instead of outputting to .xls/x, you would output to .xml

4. Access is two things - a backend database and a client based front end application. Access does not need to use an Access back end, it could be sql server/express/azure, mySQL/Oracle and many others. There are some SQL language differences and there are benefits in using things like stored procedures. Conversion of most queries is generally pretty straight forward if you have followed proper naming conventions (no spaces or non alphanumeric characters, names not starting with a number).

Hi Guys

1. To elaborate is it possible to migrate access frontend to XML ? I have heard its possible to migrate access backend to SQL server .
2. What would be the best option for a web based frontend? If we need to migrate Access frontend to web based , will a complete rewrite be required or is conversion possible?
3. Can we have a web based frontend and Access backend so we don't have to convert the backend ?

To elaborate is it possible to migrate access frontend to XML

Click to expand...

No - XML is a data file structure, not an application. Think your boss is having you on. Or chucking terms around without having a clue what they mean.

Can we have a web based frontend and Access backend so we don't have to convert the backend ?

Click to expand...

think it is time your learned to google -the answer is yes. Cost (in terms of time to develop) is typically around 10 times more than developing an access front end and will almost certainly have less rich functionality. Do not use the access web app - it is being deprecated next year and being wiped from history (any remaining web apps will cease to work). Use something like Visual Studio or Eclipse. But what is the point? the access backend will be client based, so see little benefit is using a browser based front end.

You would be better to ask your boss for wish list of how he/she sees the future.

Good luck with your project but you will get a bigger range of answers if you just put your question into google/bing

I'm going to offer a couple of general comments and then step away because I have no specific experience with web conversions.

1. In general, it is relatively easy most of the time to migrate the BE file to an external SQL engine. Access itself has the ability to convert from a passive Access BE to a more active SQL-based FE. However, I know of no free utility - and specifically one is not built in to Access - to convert the FE to something else. Is it possible? YES. Is it easy? NO. It is a full-on redevelopment project.

Another group at my last work site did a web migration from another type of DB setup that had split FE/BE, though it wasn't in Access. Even with all of the original design documentation available and a team of folks working the web pages diligently as their primary assignment, it took them a full 18 months to finish the FE and they were still working on behind-the-scenes processes that had to become stored batch-like procedures because the original FE wasn't going to be there any more.

2. You can keep form designs as examples of what people are accustomed to seeing if you wanted to retain that look-and-feel. Any procedures you created would provide a GUIDELINE for the operation. However, front-end migration WILL be a near-total rewrite.

3. Access's back-end is passive because the BE file is treated like a shared document. The utility that actually works with the document is MSACCESS.EXE on the end user's terminal. If you switch to a web-based FE, you WILL need to convert the BE to something that will actively respond to data requests. E.g. SQL server or an ORACLE BE or any of several others that adhere to ANSI SQL. EDIT: I see that CJ is aware of a way to keep an Access BE with a non-Access FE. Like I said, I have limited experience with this. But CJ's point is well taken. Updating the FE will leave you with a less-than-perfectly compatible BE.

Thanks guys. I will dig into it more to find out the possible options.