Error5 on specific record, "strange" instr behavior? (1 Viewer)

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
Code:
id = Mid(mainrow, InStr(mainrow, "_id") + 6, InStr(mainrow, Chr(34) & "," & Chr(34) & "_rev") - (InStr(mainrow, "_id") + 6))
test = Mid(mainrow, InStr(mainrow, "titel") + 8, InStr(mainrow, Chr(34) & "," & Chr(34) & "categorie") - (InStr(mainrow, "titel") + 8))

short intro: I have a json and am pulling data from it. I had some On Error code before on almost each line though my DB kept corrupting so I went in

for this specific file the error occurs after 280+ lines. Every line is handled by these 2 lines (and several others). The first does not error, the second does BUT the test variable DOES hold the information I want. checking the InStr's they return 0.

the issue occurs on different files too, all the same format, other records

I fear resuming or acting on error is what eventually corrupts the DB, though truth be told I just started Access and don't know much about it

If anybody can indicate a possible flaw in the lines, or advise how to proceed on this that would be quiete some help
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
In passing, you only need an On Error Goto xxxx once per module unless you wanted to change handlers midway during the code. The implied setting (of a trap handler target label) is permanent for the lifetime of the subroutine call and becomes obsolete only when reaching an Exit Sub or End Sub.

Your title says "Error 5" - but we need clarification. Is that Access error 5 or Windows error 5?

Offhand, I would say that we don't have enough info for this one. Obviously you are trying to parse out some stuff, but this description doesn't give us enough to make a clean diagnosis.

The obvious question is, can you see the input line that causes the error? Compare it to previous lines and see what makes it anomalous? Because this appears to be a data-centric error, the error code makes no particular sense. Both Windows error 0x00000005 and Access error 5 are about things that don't relate to string parsing.

One last suggestion to help you with parsing. If the strings don't contain embedded commas, only commas as delimiters, you might have better luck with the SPLIT function, q.v.

https://www.techonthenet.com/access/functions/string/split.php
 

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
Re: Error5 invalid proc. call or arg. on specific record, "strange" instr behavior?

Error 5 invalid procedure call or argument.

I checked the line but it's okay. I use the data references I put up to get the data out and they're the exact same on every line. I split the original file on '}' since there are quite some commas in the data.
This makes it I get all my data from the same column, in rare cases also of a second column.

The only errors I had in mind to log were the lines which had too much data for excel or access to be capped in a single field (didn't rly find a clear solution for this except for going for a more frequent delimiter that is ofc, though before Im doing that I want to get this error out.) I currently have 4 onErrors for this, each putting a specific variable for me to know what missing info it errored on. (though I have not yet figured out how ill be able to get the missing data straight from the original json, might be a diff language ill be needing i guess)

I am aware the error is not related to the action, hence I turn here
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:33
Joined
Oct 17, 2012
Messages
3,276
Out of curiosity, do ANY of the affected data lines include a first occurrence of "_id" within 5 characters of the end of the string or "titel" within 7?

Your invalid procedure call or argument could result from the start or length arguments extending beyond the length of the string.
 

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
no
","categorie":"ISDN comes right after the string needed. the string is easily contained and did not invoke issues on a lot of different rows

the thing is that the issue occurs for most of the items on the record, the specific indication of error here is just saying this is the first item that errors, so it's not the specified string but the row itself, though very strangely the id does not error, nor do some other data parts.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:33
Joined
Feb 28, 2001
Messages
26,999
I might suggest as a debugging tool to break apart the line slightly differently.

Code:
IDPos = InStr( mainrow, "_id" )
RevPos = Instr( mainrow, Chr(34) & "," & Chr(34) & " rev" )
ID = Mid( mainrow, IDPos + 6, RevPos - (IDPos + 6) )

TTLPos = InStr( mainrow, "titel" )
CatPos = InStr( mainrow, Chr(34) & "," & Chr(34) & "categorie" )
Test = Mid( mainrow, TTLPos+8, CatPos - ( TTLPos + 8 ) )

Then, with the line analysis elements broken apart like that, you can single-step this in debug mode and see exactly what is barfing on that critical line. Let's be honest - those functions with the nested InStr functions become hard to read very quickly. Breaking it apart to see the intermediate values will help you understand what is going on.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:33
Joined
Jan 23, 2006
Messages
15,364
versn,

I think it would be helpful if you could show us a sample of mainrow (or your json file).
If possible it might be helpful if we saw your code in context.
I realize you do not have 10 posts, but you can post before 10 post criteria, as long as it is a zip file.

There may be many reasons for Error 5

I found this via Google, but it may not be applicable to your situation.
This behavior occurs when the length argument for the statement is negative.

Another google result that corrected 1 poster's error 5
Responder thought it was corruption and suggested the op should create a new clean database. Then import all the objects into it. Then add all of your references back in. Compact and repair and compile.
 
Last edited:

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
I suspect the json itself has severed some corruption
I changed some small things according to what the_doc_man said for but 2 data captures, though I ran it on a different file. I was called away from my computer and the db actually ran for at least 15h (while there is NO way it should take that long)

you might notice a whole lot of replaces on the data caps but as said, this is actually the first time I make something like this, I figured out I had to change the data format (UTF8 now) in order not to get these weird characters

In the spirit of this being my first thing to make, I know some things can be made far more efficient, ive seen some things myself I could, ild appreciate would you only hint to those, want to get there myself

the sample json file is a part of the 2nd file on which I had errors (normally it holds 12k+ lines which I decimated), the line to error is 573 (if I took the right file, dont have too much time today again). Note that 573 and 574 are the same lines as 564 and 563 (I can be off here), but in a different language. since there are several jsons this is pulled in by a linked table (named 'Go'), the other tables to export data too are fully in the database.

I do have an additional question:
If I do not open the csv in Excel, the opening in the DB takes like a full 15 minutes, otherwise it goes immediatly. Is this due to the way I open it in VBA (a very basic, i think, set and open adodb.recordset) or might it have to do something with some json lines being actually corrupted?

Thanks for any effort made already!

edit: even without data the value assigned to the parameter says 'null'. The verified line does not hold that value on the references that error. references are always in the same sequence and always apart, no counter goes up to the next reference or all the way back. When debugging on the error the code actually shows the variable containing the wanted string

i remade the db several times, and even decompiled and recompiled etc, issue repeated
 

Attachments

  • dbVersn.zip
    598.5 KB · Views: 40
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:33
Joined
Jan 23, 2006
Messages
15,364
versn,

In simple English what are you doing in this code?
We need some info to start to debug/run.
 
Last edited:

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
im pulling data into a table to get it nice and orderly, the data itself will serve as product information to be used on a site, after recompiling/comparing/binding/parsing/etc. (im up for a long road of frustration as it looks now)

Im basically making a row of data and then putting it in the collectNL and collectFR

I have a file for each letter of the alfabet, and will aim to get all the data assembled in to 1 table for NL (dutch) and 1 table for FR (french), though I figured loading DBs at such size while the input files take so long already, i'ld better just bind all the collects in the end with a query

main table structure for the final of this db is the collectNL
the 'Errorsoccured' tells me which records did not entirely fit the field (when finished ill pull it out since ill be splitting on "," instead of "}" and then iterating over all fields if necessary)
 

JHB

Have been here a while
Local time
Today, 18:33
Joined
Jun 17, 2012
Messages
7,732
Try the attached database maybe it is what you try to do.
Open form "Form 2" click the button, result is stored in table "collectNLJHB".
 

Attachments

  • dbVersn.zip
    365.4 KB · Views: 32

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
impressively short. It's not all how the data should come but im prolly diving into it tomorrow to get the hang of this one. Big thanks already; 52 seconds and no corruption, no errors and seemingly no data loss. Thats my current result times 5 ++
 

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
so im starting from JHB's version though I was wondering the next, I'll be splitting on comma in this version meaning the position of the references is variable and not defined.

So I put the fields in an array
Code:
        Do
            count and check all fields in input record (beta)
            mainrow(counter) = beta.Fields.Item(counter)
            counter = counter + 1
        Loop Until IsNull(beta.Fields.Item(counter)) = True

and after having verified the possible existence of the key in the output db, having thrown away the language I don't need and starting the main cycle like

Code:
For x = 0 To UBound(mainrow())

can I verify the start and ending reference of the part I need, likely to be in seperate fields, check AND maybe even determining without running the counter over every single field (or mainrow here)?

f.e.
Code:
For counter = 0 To UBound(mainrow())
                    id = Mid(mainrow(), InStr(mainrow(), "_id"), InStr(mainrow(), "foto-src") - InStr(mainrow(), "_id"))

expecting VBA to, being a logical and sequential organism, take all fields and thereby the length, or just presence, in account, is it possible it will not corrupt if I don't count it out myself, and give me the entire string between reference points?

The thing is, contra corruption I wanted to pull out my values in the sequence they are in the record since I would think that making VBA purposely run over multiple fields multiple times might organise corruption, instead of letting it roam free

so, if VBA is in field A and I dont tell it where the end reference is, will it safely scan'em'all and return to Field A afterwards WITH the data of other fields too? I might want to make another Main Loop condition otherwise
 

JHB

Have been here a while
Local time
Today, 18:33
Joined
Jun 17, 2012
Messages
7,732
Sorry I got lost reading your post.
Could you instead explain, (no code but plain text), what you're trying to proceed with, using some input data and what output you want to get?
 

versn

New member
Local time
Today, 18:33
Joined
Mar 24, 2017
Messages
8
on a delimited text file, searching a row for a specific string, having the delimitations in an array, will mainrow() suffice as a location indication in which start and end reference point given, if both points are in a different 'field' or 'array element'.

at this point it will just shorten my code since I typed it out dictating vba what to do in these cases

f.e. comma being the delimitation
Code:
titel = Mid(mainrow(x), InStr(mainrow(x), "titel" & Chr(34) & ":" & Chr(34)) + 8)
Do Until InStr(mainrow(x), "categorie" & Chr(34) & ":" & Chr(34)) > 0
   x = x + 1
   titel = titel & "," & mainrow(x)
Loop
titel = Mid(titel, 1, Len(titel) - 1)

EDIT: but nvm the bollocks, just found how to import a textfile, meaning no more delimitations to take in account, shorter code, faster processing (i hope) and no dataloss

drn
 
Last edited:

Users who are viewing this thread

Top Bottom