Zenith63

Hi guys,

I created a purchase order/sales order system in MSAccess for our company over a number of years. We had always been manually copying the information from this system into a seperate accounts package to actually generate invoices but recently moved to an online accounts package that has an API available to create invoices etc. It would save us literally a week of work every month if we could have the MSAccess create the invoices automatically in the accounts package.

I've contacted the accounts package vendor (Visor Software) and have some info back on how the API works but am really struggling on how to get the MSAccess database actually talking to the API. I handle getting the data into the correct formats and all that, but where I'm struggling is how I actually go about calling the API from within my VBA module. I studied programming in college and have a fair bit of VBA experience, but it's all in my spare time so any of the more complicated stuff I'm unsure of and don't really have the time to learn in any great detail either unfortunately, so you'll forgive me if some of my terms and questions below make no sense .

Basically VisorSoftware have told me their API is XML web service based and that I should write a proxy class between my local language (VBA) and the web service - I understand the theory of what they're saying here but have no idea how to implement it and wonder if it's even possible in VBA. They pointed me to this to get me started - http://msdn.microsoft.com/en-us/libr...ffice.10).aspx - but that has been phased out of Office 2010.

They then suggested maybe I would be better off just manually creating the XML and submitting API requests this way, which seems like sound logic but I suspect it will be hugely error prone and very time-consuming to code for.

They also suggested we coudl create a .net DLL using wsdl.exe, not sure if this would help us at all?

See here for a list of API commands available https://www.visorsoftware.com/visor/...ation_1_1.asmx

Has anybody done something similar to this in MSAccess before and can point me in the right direction?


Any help would be much appreciated and to be fair VisorSoftware have been very helpful so if there are any questions I can certainly put them to them and come back to you guys with answers!

Thanks!
Philip

Zenith63

Does anybody have any thoughts on this? Even the smallest bit of info would be very useful and much appreciated!

hDC_0

Well, I don't know what some of the other forum experts have to advise, but here's what I would say..

Your situation is very complicated and has a variety of options,
so maybe that was why no one cared to hazard a guess as to which way to go is most "correct".

Here's the thing with VBA - its basically old school.
(I realize I risk the "wrath" of all the hardcore VBA people that pay attention to this forum,
but I'm not alone is believing the "death of VBA" idea/perception is out there).
Right now one of the things that Microsoft is doing --it's in the process of turning its Windows desktop-based Office package into
online Office Web Apps --basically an online service (SaaS) that runs in the clouds (Azure).

That's may be part of the reason the MSDN functionality you linked to
has, as you say, "been phased out of Office 2010."
This actually sounds like a reasonably possible suggestion (but I won't say its easy depending on the vendor implementation).

You say that you:
This is the real issue and might also have contributed to forum members not knowing how to advise you.

Maybe the question back to you is:
You are going to have to put in some real time and effort,
so what area do you want to put that time and effort into:
1.) Learning about calling XML Web Services thru Access VBA?
(which may end up involving using .Net Interop,
now or at some point in the future..)
2.) Learning about how to work with (and code for) .Net Web Services natively?
3.) Starting fresh and develop a totally whole new client/server application
based on the one of of the many types of web applications that can be developed in VisualStudio.Net
(which could lead to a whole bunch of options..including ASP.Net, WPF and Silverlight).
I know this "start fresh" option seems like a lot of work, but if you decide to go the fully modern .Net route
it maybe easy than something you are going to have re-write again in the
future (if, for instance, your accounts package vendor, Visor Software
ends up merging with another company or going out of business).

Here are some "things" (pieces of technology?) that are unlikely to go away in the future:
1.) Data stored in csv, xml, or SQL formats
2.) Web services - they maybe already replaced/supplemented by fancier new technology
..and AJAX is probably already in much heavier use -- at this point.
The use of web services, like SOAP, kind of "peaked" in the
"let's get a web client front end for mainframe data" period, late 1990s and early 2000s.
3.) The cloud (and cloud type data storage, including json/json-p)
I noticed one of the primary services Visor Software offers
(and I'm not sure it's one you subscript to, but..)
--is something called "AccountsIQ", which is listed as one of the
Top 25 European Cloud Computing Rising Stars
The thing with clouds services - you pay and pay and pay.
The other thing - who "owns" the data..and can you get the data out
is you decided to stop subscribing (and/or move to another cloud service)?

As a "for instance", this page shows how you import data into AccountIQ (that
was exported from somewhere else), but doesn't say how you could
offline backup the software's data (through, say, an OBDC connection, for example).


But anyway I really didn't want you to think that no one cares..just
know that your question was possibly asked somewhat awkwardly
(in a too vender-specific sorta way).

For instance,
Saying "I need to transition to one of two options
and here's my skill level for both options,
but what do you think s the better long term option"
--that I think could have been one more succinct way of asking your question.

The other way to ask the questions is (possibly):
"I'm thinking that moving ahead with VBA is the not the best way
to 'futureproof' things and, if I decide not to go with Visor Software,
what are my options available to set up something similar under .Net?".
(because if you do decide to continue your relationship with Visor Software,
you accounts package vendor, then they are the probably
the best people to advise you of what they are willing to help you with..)