Search results

Sorry, I am self-taught, don't know all the naming conventions, I just try my best to follow the examples I have seen. Hmm, interesting, I was planning on going back in the future to redo this subroutine to use a recordset entirely in memory rather than the hard table. If I get to this I will...

It is a vba recordset from SQL (a "query" for all practical purposes), not a table. Therefore whatever was set in OrderBy in the table (nothing) is absolutely meaningless, correct? If so, please read the previous posts (code) more closely - specifically the tblCnf OpenRecordset line.

MajP thank you for your input. I watched the results stepping though the code in the debugger. The problem was tblCnf was not sorted (as specified in the ORDER BY statement), so it was doing the wrong records first. I even used MsgBox in the 'immediate' window to read the SQL string (SQLTmp) to...

Here is the code (new version) that now replaces the previous code that I couldn't make work: SQLNumTbl = "SELECT * FROM TmpNumVal_tbl" 'procedure for creating a lookup table for MR# to name ProcNmbr = 8 'eighth procedure about to start ProcRcrdNo = 0 ProcDescAncmt =...

By the way, I am now using a maketable query to produce the properly sorted tblCnf, and have it working properly. I have actually moved on to the next subroutine. Sorry theDBguy, but I must keep moving on, no time for that.

tblTmp is just a 'scratchpad' of [MR_No]'s that are already done, it is used only to skip the subsequent entries after they have already been processed. As it infers, it is just a temporary table, and is erased on the next procedural use. You can see this in my code snippet: 'Skip over MR#s that...

You seem to misunderstand, nothing was done between creating the SOL-sorted recordset and reading it, yet it was not sorted. I did exactly as you said: "ORDER BY clause in the SQL statement of the query." (recordset). It did not work - simple as that. It just kept starting at the first record in...

That's not feasible, data is sensitive Protected Personal Info, source is a linked table with 70 fields and main table made with that source has 122 fields, and these have 4670 records (grows by about 2000 per year). And the present subroutine runs subsequently after 10 subroutines and makes a...

No, they're type Single actually. I have assigned a confidence level with previous subroutines, and am sorting by the numeric result of that. Once sorted, my "master record" would be the one sorted to the top, so-to-speak, so I record that as master, record the work on the other matching...

Quick Q: Why is my vba recordset not sorted? I am doing some pretty advanced data validation and error-correction using multiple vba subroutines (let's not get into that as it will take MUCH too long to explain). My subroutine works most efficiently if the recordset data is sorted according to...

Just to be clear: 1. I currently have no queries that reference more than one source, and so certainly no Join queries. I tried that (JOIN query) months ago, didn't like the results, also had problems with "access has stopped working," and since then have rebuilt my database from scratch...

It is my suspicion that the memory is being used up by the transfer and storage of the "long text" fields, as many of them have about 1000 characters per record, multiplied by over 4000 records, and is expected to gain almost 2000 records per year.

No, there are no "Lookup" type fields, UNLESS: if you are talking about "calculated" fields, yes, the direct Sharepoint-linked table "ShrptIR_tbl" has "calculated" fields, but I don't use them. These, and the "Type2/Object" fields, are the sole reason that I am using an Excel spreadsheet table...

1. About 12MB. Most of the data is taken from an Excel download of the Sharepoint table, and that file is 1.2MB (the "long text" is truncated in Excel). So the "Long text" is done in a separate direct table link from Access. 2. See "Long text" above, the "Long text" is transferred and stored in...

Update (EDITED, final): I put my report database on hold due to pending OIT inquiry. I've been having more trouble with this and the problems seem to point to a lack of system resources. But previously to doing so, I was grappling with 'out of memory' and "Error 3035 System resources exceeded."...

I've finally troubleshot and simplified my append table code (actually to make a table), and using the And/Or operators has reduced the run time from about 4 seconds back to the previous 2 seconds. Thank you for the suggestion. I had got into the (bad) habit of doing the previous due to the...

Update: good news, the Maketable query runs in less than 2 seconds! That is the way to go, now I just have to figure out how to fire that and the subsequent vba post-processing.

Well zero skews statistics, where nulls are ignored. I have no problem dealing with nulls. Sometimes the record has nothing of value to a particular statistic, so Null is appropriate in those numeric fields. Nulls also indicate to me, in certain fields, that the post-processing failed. Even...

@Isaac yes, I learned that the hard way. First I get this "UserInfo" linked table with the one that I want, but I can't look at it or use it. One other problem I had was the same one you are talking about: I got a number in my reports instead of a name, despite seeing a name when I looked...

Thank you @The_Doc_Man for your highly appreciated advice. I learned SQL from using Access design mode queries and looking at the resulting SQL, and then googling a few things like join types for instance. I was merely copying the Access-generated syntax, which always included the source...