Moving away from Access - what gives?

[peskywinnets]

So I've used Access extensively in my one man business- it's a wonderful tool.

I've recently discovered using APIS, which is helping me be quite efficient with repetitive tasks. However in this vein, I'm seeing that most APIS I want to use are going the way of REST/json ....which there seems to be little (no?) embracing of within VBA, so that got me pondering ....how does it work in a world outside of Access?

What I mean by this is presently I'm using MS Access as a one stop shop for an API, I code it up in VBA & the data is pulled in from a remote server & placed nicely into a table....everything done within access - simple!

Whenever I hear of SQL ...hmm, well that that's just a database isn't it?, when I see the libraries offered for most APIS, it's things like java, ruby, C,....they are programming languages.

So my question is what 'pulls all this together' like I say, in Access everything is done in Access, but wrt SQL...what does everything get done in?!!

Forgive the naive line of questioning, I'm not really a programmer at all & just struggling to get to grips with this & currently the penny is beginning to drop that I may have to start leaning other stuff to be able to embrace all those APIS that use json.

 

[Minty]

SQL is just a database - Access can be just a front end. So you may find it easier to get the data into SQL and use Access as your front end to SQL Server / MySql etc.- it's what a lot of people do.

C# .Net , Java etc are all ways of creating front ends to your database. You can connect them to an access back end as well. They are generally much more web-centric, and not anything like as quick to develop as a an Access front end.

 

[The_Doc_Man]

I have not actually tried this but a friend of mine at work DID. He said that for him, the biggest annoyance is that if you want to have event management, you have to "roll your own" event dispatcher because Windows event management is not NEARLY so convenient as Access event management. You don't get called on a "Form_Current" event. You have to have code that "declares" such an event for yourself, for example. That MIGHT have been the particular environment he was using, of course. I don't recall whether he was using VB6 or C# at the time.

 

[peskywinnets]

SQL is just a database - Access can be just a front end. So you may find it easier to get the data into SQL and use Access as your front end to SQL Server / MySql etc.- it's what a lot of people do.

C# .Net , Java etc are all ways of creating front ends to your database. You can connect them to an access back end as well. They are generally much more web-centric, and not anything like as quick to develop as a an Access front end.

thanks for that (& the new terms I can google!), so I guess most API developers are using an SQL back end & a front end based on C# .Net , Java etc - but my understanding is that C# .Net , Java are programming languages (with their own IDE), so when we speak of 'front end' (which I may be misinterpreting as a user interface) what would they be using? ...for example if they are coding in C#, for a 'front end' to SQL...what interface are they actually using?

My problem with sticking with Access (as a front end to mySQL), is that with Access I have to use VBA...and VBA doesn't really seem to embrace json, so I'm sort of having my hand forced to use some other front end.

 

[jdraw]

Pesky,

Just a couple of thoughts for putting a few of these into context. SQL is a database language. You can think of it as what Access and other database systems use to manage the database. When you create an Access query, using the wizard, you are working with a graphical user interface(GUI). Behind the scene (and when you click SQL view) Access shows/uses this SQL to manipulate data in the database.

SQL Server is a database product (RDBMS) offered by Microsoft.

MySQL is another database product offered by Oracle. MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius' daughter, and "SQL", the abbreviation for Structured Query Language.

JSON is a language for representing and transferring data. For the most part it has replaced XML, but there are numerous references on the web highlight benefits/issues/usage/growth... I agree that there is not much available in vba(Access) for interacting with JSON. I did find a class module for working with JSON in vba, but found it had been removed and could not find a replacement.

Here is a link to REST and places it in context with respect to HTTP.

I agree with the comments from Minty and Doc. But I'd like to know more about your business processes such that the forum can offer you some focused comments that apply to you and your set up. I do recall responding to some of your posts where some workarounds and "creative code" were used to help get some specific answers.

If you have a viable business, and you are comfortable with Access, and are not a server based environment, you might just need to refine your current tools.

As for the vba/JSON, one of the forum participants UncleGizmo (Tony Hine) is requesting ideas for class modules that he is willing to undertake, or at least lead/participate. This might be a good candidate for a new, accessible class module.

You can check youtube for videos by Steve Bishop on JSON API with vba. He has videos #34, #35, 36 and 37 starting here.

It might be useful/helpful to see if others have a desire/need for this.

@Tony -- I hope I haven't volunteered you too quickly.

 

[gemma-the-husky]

the thing is, access comes with nice easy (they call it rich) set of facilities to manage your data. you can pull in code libraries such as API to do some things that access doesn't offer.

Now if you desert access altogether, then you have to replace a good deal of those access features with code, which will cost you a lot more than the cost of access.

Of course ultimately it lets you package things as .exe files, and maybe lets you appear more of a developer to the world at large, who maybe consider access to be a bit of a toy, rather than a serious development platform.

 

[peskywinnets]

Thanks for the input (jdraw - very useful, but unless there's a way for VBA to handle JSON, I fear it's going to be a slog staying with Access).

As I say, I'm happy with access, now starting to be able to use it well for most of my repetitive business tasks, but my heart sinks when I look at all the cutting shopping cart website offerings & their APIs.....all I'm seeing is JSON.

Shopify, Bigcommerce,Woocommerce ...etc. - they all use JSON (whereas Ebay & Amazon have XML based APIS, which is how I've managed to get them working with Access) ...they all have libraries for java c#, ruby ...I never see as a library VBA!!! (which speaks volumes about how many are wanting it!)

I'm still struggling to grasp with what I'd be learning (& in what interface) if moving away from Access. Access provides me with a single erhm 'environment', which pulls together a nice front end gui, a database & a VBA compiler ...all under one hood so to speak - what are people using when they code up in something other than Access - in other words if I wanted to start doing the equivalent of what I've been doing in Access in say mySQL ...what would I be downloading/firing up to give me a similar 'environment' to that of MS Access?

 

[jdraw]

I think Dave (gemma the husky) was saying that the ease of use and rapid development features of Access can always act as a front end to other databases. Other programming languages may require a new learning which may not be trivial.
Do you do all maintenance on your application including database?
Are you using MySQL as your back end now? Or are you just thinking of that as a free RDBMS that you could use?
I'm not sure there is anything out there that can give you the "environment you have currently with Access" for free or even with considerable costs.

I'd like to find out who is using Access vba and JSON and get a good example of usage. I have seen articles (via Google search) but they seem directed to a more technical and familiar audience and don't necessarily come with a representative example. I'm looking for something that we all can read and "understand" (at least generally). Perhaps a video that can be played, stopped, reversed as necessary.

From 30,000 ft, you send a request to a website that offers to provide to you -providing you have proper username/password/account - a response text in some format (JSON, XML, some proprietary style...). The response is formatted according to some standard. It is your code (whether personally developed, obtained from 3rd party, or some service) that transfers and parses the response data into a format/context suitable for your needs. The simplest analogy may be you send a request in XXX, the website evaluates/validates your credentials, reviews your request, gathers the response and sends it to your computer/site in "ancient Greek", and you have a routine that can translate "ancient Greek" into English. Again from 30,000 ft, that's sort of what is happening. You may also have a choice of "ancient Greek" or "pig Latin" (that's kinda the difference between JSON and XML from 30,000 ft). Some sites offer different formats -- I know I used one that offered CSV(weather related as I recall).

Meanwhile you can do some research. Ask some basic questions of what you want to know. Google, youtube.... I wouldn't jump without knowing the depth of the water......

 

[peskywinnets]

From 30,000 ft, you send a request to a website that offers to provide to you -providing you have proper username/password/account - a response text in some format (JSON, XML, some proprietary style...). The response is formatted according to some standard. It is your code (whether personally developed, obtained from 3rd party, or some service) that transfers and parses the response data into a format/context suitable for your needs. The simplest analogy may be you send a request in XXX, the website evaluates/validates your credentials, reviews your request, gathers the response and sends it to your computer/site in "ancient Greek", and you have a routine that can translate "ancient Greek" into English. Again from 30,000 ft, that's sort of what is happening. You may also have a choice of "ancient Greek" or "pig Latin" (that's kinda the difference between JSON and XML from 30,000 ft). Some sites offer different formats -- I know I used one that offered CSV(weather related as I recall).

You've pretty much summed it up, json (& I'm no expert!), is just another way of 'wrapping data' for transferring in a standardised manner...the problem is 'unwrapping it' when it arrives into VBA ...sure everything is doable, but unless there's a decent parser (like there is for XML in VBA), things will likely get ugly & unwieldly real quick!

 

[static]

You can write whole websites in nothing but notepad. They are (with the exception of media files like images, videos, sound files) text.
XML is just a text file. JSON is text. HTML is text.
If you know the format of the data, you can write it all yourself, no need for API's.

As for GUI's. Visual Studio allows you connect to data sources just like Access does. You have similar controls as Access and even a grid control for displaying data as a table. There would be a learning curve, but maybe not as steep as you think.

 

[liddlem]

...what would I be downloading/firing up to give me a similar 'environment' to that of MS Access?

Hi Peskywinnets : While I haven't got my head around 'Visual Studio' yet, I think that it is the product that you are looking for.

 

[Lightwave]

I almost guarantee that there is still likely to be a place for MS Access in your tool set. Even if it is mainly as a scripting / coding environment.

Recently I've noticed more businesses coming back to MS Access. Why? There is a real business continuity issue with not having a rock solid library of functions - error trapping is brilliant and there is far less backward compatibility issues. Collected technologies tend to break pretty easy and suddenly become unsupported. Yes you can do some amazing things with it but the people who are doing that are often the ones writing the framework and work on small highly complicated areas that have widescale relevance. Most of us don't have the luxury to be that focused.

Of course it is really worhtwhile to have something other than Access but be prepared for some horrendous configuration issues which can really kill productivity.

Here's some references to some vba tools that may help with JSON


https://github.com/VBA-tools/VBA-Web

https://github.com/VBA-tools/VBA-JSON

 

[jdraw]

I agree with Lightwave - there is still likely to be a place for MS Access in your tool set. Since you are familiar with the Access environment, it will be a "comfort zone". Access does many things for you/us, more than we appreciate.

It seems the VBA-Web, mentioned by Lightwave, has more documentation and is more relevant to your general issue -web request and response.

There may be interest from some on the forum to do some experimenting with VBA-Web and Access.
My quick look at the link shows it was set up for Excel, but that it should be usable in Access.

VBA-Web: Connect VBA, Excel, Access, and Office for Windows and Mac to web services and the web

 

[The_Doc_Man]

Lightwave's comments are spot-on regarding something that I did for part of my career. I was considered a "software engineer" and engineering in that context is "learning how to make things work together." Right on the fringes of system designer, but that depended on the labor structure of the employer...

Anyway, the advantage of Access over so many of the other methods out there is that Access already has solved the problem of integrating things, even if the solution is not the prettiest in the world. Component Object Model methods can be clunky but they work! Businesses try all sorts of things but if it becomes too difficult to keep it working, they gravitate to "tried and true" solutions.

 

[Lightwave]

So my question is what 'pulls all this together' like I say, in Access everything is done in Access

and there's the rub

If your not in Access - quite probably you do - quite often by hand writing code that ties a database engine to a web client to a web server or alternatively some kind of client to a database

 

[peskywinnets]

Don't get me wrong guys...I'm not diss'ing Access...in fact I'd go so far to say it's been the one single revelation in running my one man business...I remember I had a 'penny dropped' aha moment when I realised I needed my own database to help me run my business - I fired up MS access for the first time & thinking WTF - What now? However, little by little I've built up a reasonable amount of access 'chops' (albeit in a kludger style that would make most on here wince!) ....frankly, if wasn't for access eliminating time intensive/repetitive tasks, I'd have probably packed up & found something else to do for income! So there's lots of love for Access here.

No, the only reason I'm mulling having to start looking at other languages/databases is because of the lack of json support in VBA....it seems (wrt APIs) that json is the future & VBA isn't keeping up.

I'm really a hesitant/reluctant learner - I learn stuff only if I have to, so if there's any way I can avoid having to pull all this newly required knowledge wrt other coding languages, user interfaces & databases together, then you can be sure I'll stick with access! (I guess I'm just going to have to explore my own way of handling json formatted data first)

I'm just a little surprised that there's no 'goto' package if you have to work with a certain language & SQL that would provide an equivalent 'well integrated' interface to Access (that said, liddlem Tabled Visual Studio, so if push comes to shove, that'll be my first line of enquiry)

 

[isladogs]

I posted a link to the MS Access user voice forum a couple of days ago.
Improved support for JSON has already been proposed

See this post which contains the link:
https://www.access-programmers.co.uk/forums/showpost.php?p=1540469&postcount=2

You may want to add your vote to the idea

 

[isladogs]

By chance, I just found what appears to be a useful link for this topic whilst searching for something else

http://ramblings.mcpher.com/Home/excelquirks/json

Although its concerned with the interaction between Excel & JSON, I expect much of it can be applied to Access

HTH

 

[jdraw]

Pesky,

I started another thread to see what's going on re Access vba and json.

Can you provide a sample json response that represents something from your business activity, and what you need from that response?
You can mock up/replace some names and/or codes --but replace them with similar number of (alphas or numeric).

I think there is interest in finding out more, and it can only help you with your decision making.