API Data Retrieval Issue (time lag?)

[sxschech]

I'm pretty sure the issue is related to an intermittently slow internet connection.

I've put together some code to make api calls to SurveyMonkey that returns data in JSON format and gets saved into a variable which then is passed to a function that parses out the data.

I started encountering parsing problems when downloading a large data list because the row markers I was using to guide the parsing routine weren't there. I flooded my code with doevents and added a routine which checks the first row for the marker, this seemed to solve the problem, but alas, after running the code several times to be sure that the problem was sorted out, I started missing data again (randomly-sometimes all data came through properly sometimes not). Since now the first row check succeeds, it means that there must be messed up/missing data further down the list, but not sure how to identify it since at this stage the data aren't structured in a standard access table format.

Is there code that can tell me if the api call fully completed or missed data, or are there other suggestions on how I can test the list to see if it is complete besides manually checking every data list I get?

 

[sxschech]

After careful review, turns out the problem was not with the API, rather with the vba code to parse json. So at this point, am unable to proceed since without a viable json parser can't work with the data. I tried various parsers and encountered errors, not sure which references were needed or not understanding how to invoke and retrieve the data, which is why the parser I was using seemed so great as it worked without much modification. Too bad can't rely on it to pull out all the data. Most of the other parser code I saw used python, c, java, etc and I am unfamiliar with those languages so can't take advantage of it.

 

[strongm]

Have you used Microsoft's JSON parser?

e.g. my code in thread705-1737583

 

[sxschech]

Hi Strongman,

Thank you for pointing me to your post. I probably saw that at some point during my search and as mentioned "not sure which references were needed" to avoid errors. I tried again and get error 429 ActiveX component can't create object and highlights:
[pre]With CreateObject("ScriptControl")[/pre]

I added script and activex references:

I am on windows7, access 2013.

 

[strongm]

You should not need to add any references at all. The code is self-contained as it stands. However ... if you've installed 64bit Access,rather than 32bit, then I'm afraid you can't use any 32bit ActiveX controls - which is what the Script Control is.

There are some hacks that can get around this, but they are messy.

 

[sxschech]

Thanks Strongm, looks like I'll need to keep looking since I'm on 64 bit.

 

[strongm]

Hmm - actually, it is more esoteric than being a hack, involving launching a hidden HTA