"Access has stopped working" due to vba routine taking too long (1 Viewer)

[HalloweenWeed]

Thank you for all your replies. I appreciate ALL your suggestions and comments, and do take them seriously.
Regarding the "never ending IIf()": After Access tries to create the table, and fails with the "Access has stopped working" error, when reopening the db I have a properly completed table with the correct values in all fields, but incomplete records. The IIf() is working properly. However, when I delete all the records in the table, and compact and repair the db afterward (or vice-versa), the db has grown permanently by over 7MB - I no longer trust that it remains stable, so I restore it from backup. All the records that were shown in the logfile were written properly.

@arnelgp this is a new database file that only I have access to, no one else can open it. All the data comes from Sharepoint, and the table data linked to the Excel file is static at runtime, only I can access that Excel file too. But I don't really understand how Sharepoint tables are presented directly to Access when a query is run. What's more what I did read is that those "complex data", "type2/object" data type fields actually are linked to tables hidden in Access, I have looked for them (hidden tables turned on) and cannot find them. It may be that they only exist at query run time, and are not saved as they are linked. I also read that the "Long Text" fields are actually "pages" of memory. This would only apply to the items from the recordset "tblLngTxt" at the bottom of "MakeIR_tbl()". Already I have plans to remove all but 3 of those (the 3 using Log Text fields), but it involves changing the Excel linked table Query .iqy, so I haven't gotten around to that yet. This Sharepoint table is used by many users, multiple times daily, so it is not only possible that their locking is affecting it, but likely that a user will be editing a record at the time of query. This is a large institution, and there are literally hundreds of users of that table, and over a thousand records made per year (so multiple per day). I am under the impression that Sharepoint is now used as a tool for this because it handles the multi-user aspect of this better than Access can, I do not know the implementation methods involved, and am not allowed access to "Sharepoint Designer" - the administration and creation tool, nor do I have Admin privileges to the design of the Sharepoint table.

The problem with Sharepoint is, the data is not represented in a way that the Manager of the Sharepoint database (not to be confused with Administrator) can easily find entry errors for correction. First, we can only see 30 records at a time, and cannot jump to any given record without using a filter to do so, meaning if scrolling through the list, you can only go 30 at a time, and must scroll all the way to the bottom to get the "next" set of records, meaning if you stop during the process (likely), that you have to use a lot of time just to get back to where you were (this also times out when idle, requiring a refresh of the website to continue). If a filter is used, the only record(s) seen is what meets that single criterion. Then there is a lot of scrolling both ways involved to see the raw data as a table, with lots of blank space. The user can select which fields to look at, by creating a "view," but then still to jump to a particular record without entering a specific filter (a sningle number or date), it takes time to scroll and then wait for the next 30 to load. My job is to streamline that process, and also produce statistics, in multiple ways, each with variable date ranges.


I have tried using the DB.OpenRecordset(tblDocName, dbOpenSnapshot, dbReadOnly) method for the linked Sharepoint table, but it does not work (throws an error), tried it with IMEX=1 (read only), but that also doesn't work either. I can only use IMEX=2 and DB.OpenRecordset(tblDocName, dbOpenDynaset) I have not tried other values in the LockEdit parameter with dbOpenDynaset (other than dbInconsistent).

I'm going to try making a MakeTable query out of the TblSQL string now. When I first started making this a month ago I couldn't do this because I was originally using a date range from a form, and it wouldn't work when the Access Query was called by vba. Now that I have eliminated that, for a couple of reasons, I can try it. I can try it a couple of ways, with and without the logic processing, I can do the logic processing changes in vba, and I'll find out which is faster. I'll let you know how it goes.

 

[HalloweenWeed]

Does it always reach the same place in the code when it hangs?
See you add message boxes after each section of code to help identify the location

Its possible you are running out of available connections. You can monitor this using my utility Available Connections - Mendip Data Systems
Another possibility is that you have hit the limit of MaxLocksPerFile or (less likely) MaxBufferSize. See this thread for possible solutions: MS Access Disk Buffer Size | Access World Forums (access-programmers.co.uk)

Yes I did read that reply before I posted though I think you may have edited it again since then.
I've suggested 3 things that may be responsible for your issue and that may well allow you to prevent it.
MaxLocksPerFile can also be increased for the current session using VBA

However there is a limit to the number of times I will suggest you follow up those links which you seem to have dismissed without investigation.

@isladogs First, as I said I have a locked-down computer to work with, I cannot install software such as this on it, it is forbidden.
As for the MaxLocksPerFile idea, like I said I do appreciate all the ideas submitted but that said I feel this is likely not the issue, and don't feel it is worth the effort of going down that fork at this time, sorry. I have the option to 'pick and choose my battles' and if this is the case I would rather pick a different battle. My project is not mandatory, I am just trying to help a coworker reduce their hourly workload (and this is part of my job description).

 

[HalloweenWeed]

Hey, one more question regarding the MakeTable Query:
Right now I use a form to show the user that the vba query is running, and in order to do so it starts the vba after a 1/2 second timer (then turns of the timer, zero duration). Said form dies not have any active controls, only some hidden inactive ones to hold a couple process variables (I might have even moved those off, I can't remember offhand). I used the Timer because if I fired the vba from the load event, the form would not show during execution. I tried the activate event, that didn't work. The form is not bound, never has been. Since it doesn't have a record, the current event will not fire. I even tried the Gotfocus event and that didn't work.

Now since I am going to try to fire the MakeTable query, I am faced with another quandary: how does it fire, and how do I make the vba to post-process those results fire given that the MakeTable query will take much longer to execute as the years pass? If I set a timer for the vba, then eventually the vba will fire before the MakeTable Query finishes. My idea is to bind said form to the MakeTable query, then use an event to fire the vba when the MakeTable query finishes, but exactly how would I do that?

 

[The_Doc_Man]

I tried to do some things to analyze your query. It was written with some inefficient styles. It was also not well-indented and thus incredibly hard to read, but I decided it needed help. I've been having the "Oops" problem where I cannot post and I'm pretty sure it is because that query was a bit much for it. I will therefore make my comments and SEPARATELY try to post the deconvoluted query.

First, you have several cases where you test for a variable type to be >1 and < 9 - but you could collapse that into "BETWEEN 2 AND 8" and thus simplify the expression. Here's an example.

IIf(VarType([ExcelIR_Stats].[Safety Chair Out (min)])>1,
    IIf(VarType([ExcelIR_Stats].[Safety Chair Out (min)])<9,
        CLng([ExcelIR_Stats].[Safety Chair Out (min)]),
        Null),
    Null) AS SRC_End,

Using the BETWEEN ... AND operator, this might simplify to

IIF( VarType( [Safety Chair Out (min)] BETWEEN 2 AND 8, CLng( [Safety Chair Out (min)] ), Null )

Between…And operator

Office VBA reference topic

docs.microsoft.com

Second, you have several cases where a little bit of Boolean logic would get rid of the IIFs. You have more than one thing that resembles

IIf([SRC_DrtnValid],
    True,
    IIf([STC_DrtnValid],
        True,
        False)) AS MechDrtnValid,

But in order to be able to do this at all, [SRC_DrtnValid] and [STC_DrtnValid] must themselves be TRUE/FALSE in nature. Therefore, your statement would be ... [SRC_DrtnValid] OR [STC_DrtnValid] AS MechDrtnValid ...

Third, and it's a REAL biggie in terms of simplification: You have a single source for all fields. I.e. your FROM clause names one source. Therefore, you do not need to qualify ANY of the fields with the ExcelIR_Stats. qualifier. EVERYTHING comes from there or is a constant. There is no other source. You can get rid of EVERY qualifier prefix on your fields and thus do considerably less typing as well as you can read that a whole helluva lot easier.

Fourth, you often have this construct or something similar:

IIf((ExcelIR_Stats.[Restraints: Non-Mechanical] Like " & Chr(34) & "*Hands On Hold*" Chr(34) ),
    IIf([HoH_DrtnValid],
        True,
        False),
    False) AS HoH_Status,

You can simplify this to HoH_DtrnValid AND ( [Restraints: Non-Mechanical] LIKE "*Hands On Hold* )

This works because you have a T/F expectation as your result, the HOH_DrtnValid appears to be T/F, and your comparison expression will return a T/F result after doing the comparison. So simple logic will help here.

Besides those four specifics, I have a comment about declaring null values. Generally, Access has a headache with nulls. If you were to declare something as having a 0 value rather a null value, you can test for that easily enough. Or, if 0 has meaning there, pick something that cannot have meaning, like an impossibly huge negative number. Nulls lead to functions returning "Invalid use of null" errors, so you are inviting headaches down the line. Consider it "food for thought" if you will.

 

[The_Doc_Man]

In several attempts, I have repeatedly gotten the "unable to post" error, which means there is something going on here. I have one more trick to try before I give up on posting the deconvolution.

 

[The_Doc_Man]

Let's try attaching the deconvoluted file...

Looks like it made it. This is how to analyze the logic of the SQL and is therefore how I managed to extract those logic comments.

 

[Jon]

Aware of the issue. Need to fix a security plugin on the WordPress portion of the site that is affecting all subdirectories.

 

[HalloweenWeed]

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 object name with the fieldname. GTK. When I do things like that on my own, and then I get an unstable db, makes me wonder if that was the cause, so I didn't do that, I will from now on. As far as the indentation is concerned, TY, I will strive to fix that in the future.

Not creating excuses here, just explanation - and rant - in case you might be interested: when I first created this SQL I was using the BETWEEN...AND syntax for a date range, and it wasn't working. I had concluded incorrectly that it wouldn't work with vba, just like you can't call an Access Query directly from vba when parameters such as these are required (comment: seems like a stupid oversight by Microsoft IMO). After weeks of adjusting, troubleshooting, researching, and refining I finally discovered that the problem was actually that "complex data", "type2/object" data type for the dates, not the syntax (seen it in the watch window in VB Editor). In doing so, I binned the idea of using a date range instead of messing with the headache of "Recordset2" properties and yet another recordset to open - after I looked at the fields and values a bit. Because I still would have to get query results for the entire table (a Recordset), then cross-reference them with the "Recordset2" recordset properties in order to filter out dates, the filtering became moot. What a PITA. Not impressed with Sharepoint. Since then I started using the Excel table as the main source, and limiting the date range is now again possible because Excel converts these values to proper dates.


And I wasn't aware I could use And/Or in the SQL logic in Access (I am aware that Access is not compatible with all SQL language). Also GTK, thank you.

The good news is I am learning a lot here, and becoming more valuable as an Access database manager (although my job desc is much lower than that). Also: I had made some extra fields in order to simplify the logic ("Valid" boolean variables) to make them easier to read and troubleshoot. Using And/Or would simplify them greatly and allow me to remove two unnecessary boolean process variables, I will do so. Thank you again.

 

[Isaac]

In my current job I'm forced to use Sharepoint with Access quite a bit. One thing I've learned (which is really nothing more than yet another application of the general principle which I constantly repeat "keep data types simple"): The more complex Sharepoint column types really do not play well with Access. The mere presence of a People column type can make an entire linked Sharepoint record non-updateable, depending on the mood Access and Sharepoint are in that day.

 

[HalloweenWeed]

@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 directly at the tables in Access, and thought that table was an index of the names - and it wasn't! I contacted the Sharepoint Admin asking for privileges to access the table, and she didn't even know what I was talking about. She said there is no "UserInfo" table. Subsequent questions revealed that it was actually a number linked to an email server, and that's where the names were coming from! Wierd. So as she strongly suggested, I have to use Excel to translate the values. Nice going, Microsoft! I don't even think she knows as much about programming as I do, none of the data entered into the Sharepoint tables use any sort of validation, except to make sure that dates are really dates, and there is a few dropdown menu choice text fields (combo boxes). There is fields that should be numeric-only and the user can enter alphanumeric characters and symbols, and sometimes do (they are only saved as text). What a PITA.

Can you imagine, some people put "@1843", 643p, 6:43p.m., or 18:43 as a time! In the "MR" field, instead of putting in just the number, they put like "MR00004372" or "N/A". For god sake, leave it blank if there is no number!

 

[HalloweenWeed]

Generally, Access has a headache with nulls. If you were to declare something as having a 0 value rather a null value, you can test for that easily enough. Or, if 0 has meaning there, pick something that cannot have meaning, like an impossibly huge negative number. Nulls lead to functions returning "Invalid use of null" errors, so you are inviting headaches down the line. Consider it "food for thought" if you will.

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 in all my custom global functions I code the inputs as "Variant", and include code in the function to handle Nulls and non-initialized values and also take error values (VarType 10) as well.

 

[HalloweenWeed]

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.

 

[HalloweenWeed]

Second, you have several cases where a little bit of Boolean logic would get rid of the IIFs. You have more than one thing that resembles

IIf([SRC_DrtnValid],
    True,
    IIf([STC_DrtnValid],
        True,
        False)) AS MechDrtnValid,

But in order to be able to do this at all, [SRC_DrtnValid] and [STC_DrtnValid] must themselves be TRUE/FALSE in nature. Therefore, your statement would be ... [SRC_DrtnValid] OR [STC_DrtnValid] AS MechDrtnValid ...

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 necessity of checking for Null before comparison of values.

 

[The_Doc_Man]

Well zero skews statistics, where nulls are ignored.

Just an observation: If taking statistics via a query, WHERE clauses can ignore nulls too. And if you use some OTHER indicator of "not valid number" then you can test for that in a WHERE clause just as easily. If you want cases to be ignored when doing statistics, don't leave exclusion to chance. Take the bad records out of contention at the WHERE-clause level and don't worry about using an obscure feature of something that happens to be ignored by a domain aggregate or SQL aggregate.

 

[HalloweenWeed]

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." Our OIT only supplied me with a (2010) PC with 4GB RAM and 32-bit Office, despite my pleas for more. I tried inserting both the

        DoEvents
        DBEngine.Idle dbRefreshCache

codes in an area of various loops to prevent system usage being monopolized by Access, but it made no difference. I looked at my system resources when I got the "out of memory" error and it was up near 90% memory usage and commit charge, with the "hard faults" at least 50% green (areas of time seconds long all green), "Available: 359MB." MSAccess.exe was taking up 1,364,568KB on the commit, 1,200,816 "Working Set". Screenshots provided.

But before I got the pure "out of memory" error, I was getting "Error 3035 System resources exceeded" on the following subform subroutine, (attached in file 210423 ProvSelectSubform vba.txt) on the .Update command line (SOLVED*, see near the bottom):

Private Sub Choose_But_Click()
'On Error GoTo Err_Handler

Dim Response As Integer
Dim tblDocName As String
Dim tblPNA As DAO.Recordset
Dim DB As DAO.Database

    Set DB = CurrentDb

    tblDocName = "ProvNamesAlias_tbl"
    Set tblPNA = DB.OpenRecordset(tblDocName, dbOpenDynaset, dbAppendOnly)

    With tblPNA

        .AddNew
        !ShrptAlias = Me.Parent![OD_Name]
        !ProvName = Me![ProvName]
        .Update
        .Close

    End With

    Set tblPNA = Nothing
    Forms![FinishIRTbl_form]!FlgPP_NaN = False
    Set DB = Nothing
'Run the FinishIR_tbl() function (from the parent form) until finished
    Me.Parent!TmpSuccess = Me.Parent.FinishIR_tbl()
    Forms![FinishIRTbl_form]!FlgIR_tbl_Success = Me.Parent!TmpSuccess

ExitSub:
    Exit Sub

Err_Handler:
    If Err.Number = 3420 Then                   'if recordset is closed just exit the subroutine
        Err.Clear
        Resume ExitSub
    End If
    MsgBox ("Error #" & Err.Number & ": " & Err.Description)
    Resume ExitSub

End Sub

and also in the near-identical algorithm in the parent form ("ProvSelectForm") subroutine "NaN_But_Click()."

This seems very odd to me, as this recordset is a small recordset with only about 200 records and two columns of short text. I went back through all the vba that ran prior and reworked the closing of recordsets and resetting of pointers to ensure I wasn't causing a memory leak. I tried changing to dbOpenTable type recordset and still the same result. I also get the same result in a very similar subroutine: "Create_But_Click()" in the form "ProvSelectForm" (also attached).


Attached is all the vba code that runs in the database up to this point. They run in this order:
Database startup unbound form is "StartForm," which has no controls whatsoever, it starts running vba after a split-second timer wait (500 I think). This deletes all records in the main "IR_tbl" (but not the definition), and then calls the append query "ApndTbl_IR_tbl_q" (also attached), which fills in all the records with all but 4 fields (one short text and 3 long text fields).

When that is done running, it calls up the unbound form "FinishIRTbl_form" which has only 2 visible controls (both disabled) that show progress in a x of y format. It has several invisible controls for process status variables. This is where the first attempt at filling in all the other 4 fields occurs. When (if) the process hits an issue that requires human input to continue, it exits (after sending some process variables) to the next form:
"ProvSelectForm" (otherwise it exits to the MainMenu, and I haven't gotten that far yet). This is an unbound form but the subform is bound to the query "AllProvNamsDstnct_q", which is a direct field-for-field reference to "ProvNamesAlias_tbl". This form is where the user can select from a few choices to determine what the process should do with the text input, and do the same for the next record that also encounters the same text.

The subform is only used if the user selects the "choose" button on the record of choice.

 

[The_Doc_Man]

When you show a 1.2 Gb "working set" that is probably Access loading up memory with structures it thinks it needs. My questions to you are:

1. How large is the DB file when it has been compacted and repaired but then immediately closed?
2. Do any of your routines attempt to create in-memory structures, arrays, or some sort of Windows interface structures?
3. Do you repeatedly open some external application (like an Excel Application Object)? Word or Outlook or PowerPoint would also do it.
4. Do you dynamically open a lot of recordsets without first closing them?
5. Is any part of your code recursive?

 

[HalloweenWeed]

When you show a 1.2 Gb "working set" that is probably Access loading up memory with structures it thinks it needs. My questions to you are:

1. How large is the DB file when it has been compacted and repaired but then immediately closed?
2. Do any of your routines attempt to create in-memory structures, arrays, or some sort of Windows interface structures?
3. Do you repeatedly open some external application (like an Excel Application Object)? Word or Outlook or PowerPoint would also do it.
4. Do you dynamically open a lot of recordsets without first closing them?
5. Is any part of your code recursive?

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 Access as pages of memory from what I have read. Officially, it is just "long text" fields in each (of over 4000 records).
3. No.
4. I open only 4 recordsets at once, close them and destroy the pointers before allowing another subroutine to open them again.
5. I don't think so, but see the way that "ProvSelectForm" is opened from the "FinishIR_tbl()" function in "FinishIR_tbl_form", near the bottom, the following lines:

                    stDocName = "ProvSelectForm"
                    If CurrentProject.AllForms(stDocName).IsLoaded Then
                        Forms![ProvSelectForm]![ID] = !ID
                        Forms![ProvSelectForm]![OD_Name] = RmvPunctExcComma(!OD_Inpt)
                        Forms![ProvSelectForm]![ProvSelectSubform].Requery
                        Forms![ProvSelectForm].SetFocus
                    Else
                        DoCmd.OpenForm stDocName
                    End If

The first part of the "If" statement shoudn't ever execute, "ProvSelectForm" should not be open when this function is executed - it's only here just in case I forgot to close it while troubleshooting. Only the Else portion should execute. I kept the code that follows that simple and quick to avoid possible latent execution problems. Once this form opens, it should execute a function that is nearly the same (the variable addressing is different, and it starts from the ID set instead of the beginning). So no, but you may want to take a look to see for yourself.

The other possible recursive portion is in the function of the same name in "ProvSelectForm", at the same point, but if it does execute recursive (I doubt it) it doesn't have much left to do in the previous iteration - just closing the function (and the return Boolean value). This is that code:

'requery this form's subform to update any choices added in previous iteration
                    ID = !ID                                    'redundant, but just to make sure ID is same
                    IR_NbrTxt = !IR_Title
                    OD_Name = RmvPunctExcComma(!OD_Inpt)
                    tblIR.Close                 'close the IR table before requerying this form
                    Set tblIR = Nothing
                    Set DB = Nothing

                    Me.[ProvSelectSubform].Requery
                    DBEngine.Idle dbRefreshCache
                    GoTo ExitSub                            'allow user entry and re-entry into this subroutine
                    'this subroutine will need to be restarted and re-ran after the form until finishing otherwise

It hands back control to the user to take care of another text value that the algorithm cannot resolve. It actually does work, I have proven it. The 'out of memory' error however, happens before execution ever gets to "ProvSelectForm". I think that the function above would completely execute to 'End Function' before any user input, so I don't think it would execute recursive.

 

[Isaac]

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 directly at the tables in Access, and thought that table was an index of the names - and it wasn't! I contacted the Sharepoint Admin asking for privileges to access the table, and she didn't even know what I was talking about. She said there is no "UserInfo" table. Subsequent questions revealed that it was actually a number linked to an email server, and that's where the names were coming from! Wierd.

The Userinfo table is automatically linked in Access, and for me at least, it is accessible to view - I've never tried to do anything other than View/read/query it (and wouldn't recommend doing so).

My best conclusion so far is that the Userinfo table will contain a record for each person with any type of permissions at the Site level.

And it exists inside Access for the purpose of supporting the heinous lookup-type table columns related to People fields. If your List has no People fields, you probably won't see the Userinfo table auto linked when linking the sharepoint List.

And I agree, Sharepoint admins have generally never heard of this and will not know what you are talking about.

I haven't read this whole thread again, nor do I plan to this time, but remember to close and set to nothing DAO recordsets which may be opening in any kind of loop (or other excessive-usage-producing scheme).

 

[The_Doc_Man]

Something mentioned in passing suddenly tripped with me. Do you have tables that use lookup fields? I'm not talking about queries that do an implied lookup via a JOIN, but rather an actual field in a table that is of type LOOKUP? If so, how many such fields do you have and how many possible values does each one have?

Rather than confuse the issue, I will wait for your answer before commenting further.

For a 12 MB database to expand to 1.2 GB in physical memory, I know of very few mechanisms that would do it - but if you have lookup fields in a table, then my post #36 with the five questions left out a significant option.

 

[HalloweenWeed]

Something mentioned in passing suddenly tripped with me. Do you have tables that use lookup fields? I'm not talking about queries that do an implied lookup via a JOIN, but rather an actual field in a table that is of type LOOKUP? If so, how many such fields do you have and how many possible values does each one have?

Rather than confuse the issue, I will wait for your answer before commenting further.

For a 12 MB database to expand to 1.2 GB in physical memory, I know of very few mechanisms that would do it - but if you have lookup fields in a table, then my post #36 with the five questions left out a significant option.

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 as the source, which is linked to the Sharepoint table. Excel translates these to usable values for Access, into type Double and Date values (I then convert the type Double into Long Int in the Access append query, as they are all Integers by nature without decimal points). Also, the same linked table contains what you may be referring to as a Lookup: there is a name that is actually a number for lookup in the Exchange server database, shown as a name when you look at it in table view, but when you attempt to use it in a report it only shows a number - unless you access it as a "Recordset2" type (like the dates). Again, I don't use this field in this linked table but instead use the translated version from the Excel table (the Excel download translates it to a short text for me).

OK I just now checked, and the "long text" fields show up as VarType 8 (vbString) when queried to the "ShrptIR_tbl", which is directly linked to Sharepoint. But despite this, they contain more than 256 characters in many, if not most, records.


I believe the linked table "ExcelIR_stats" only contains types Boolean, Double, short text, and Date values; and I only use about half of the fields. I have confirmed this with manual queries containing VarType, on the fields that I actually use (there are also Nulls).

I answered all of your 5 questions in my post #37.