Solved MS Access Query of remote Database pulling partial data

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
I am working on a database application and am running into an issue that seems odd and maybe someone can shed some light on the subject and help me move forward with a solution. I am working on a offline solution copying and pasting code will not happen, and as such there may be slight errors in how I typed everything, so please bear that in mind. Also I may not be able to create a accde format which most assuredly protects the linked tables, which is why I am fleshing out this option. The DOD has disabled all trusted locations. I want to populate a Test Feedback form with their test results and then destroy the data. So making tables and exporting and importing them seems like a waste, if I can get this to work correctly. Because then I can create a recordset and assign it to the recordset of a form and gets destroyed when finished, no temp tables anywhere that will lead to bloated DBs.

Using: Microsoft Access 2016

Setup:
Front end MS Access DB
Backend MS Access DB Encrypted, with password only provide in code via Front End. This has two tables I do not want exposed by connecting to the front end. I know how to link the table, and how to set as dbHiddenObject, and how to prevent shift key usage, that is not the issue. Linking the tables works; I already know this, but actively want to avoid doing that.

I have a query that when run from the Backend works just fine. I am not going to post that query, Its SQL statement is six pages deep in word document in landscape mode. What you need to understand the query works fine in the back end and works fine in the front end when the two required tables are linked to the front end, when run as native Access Queries,. However, will not run as an SQL string for db.execute statement because of []()"':; in the field data, which cannot be removed. I want to save "Cleaning" the data as an absolute last resort. The query works, this should work, but doesn't.

Now here is where the problem arises. I am attempting to call the query from the front end, when not connecting the tables to the front end. Here is the code I have so far.
Some other location in code
Global lc_db as DAO.Database
lc_db = dbengine.connect (database connection string)

Then in my sub I have a line that calls the remote query to set it to a recordset
check state of lc_db, is nothing reconnect (paraphrasing here of course)
str_qry_name = "qry_TestFeedback"
set rstTestFeedBack = lc_db.openRecordset (str_qry_Name, dbOpenSnapShot)

When I run the recordset set statement it executes fine, no errors. What does fail is when I start trying to use the data from the recordset. There are 54 fields returned and for simplicity's sake I am referring them by their index number. When I test the values using the immediate window this is what happens:
?rsTestFeedBack.fields(#).value
# = 0 - 3 No issues returns the proper value (CACID,TCompletedDate,TScore,TPass)
# = 4 - 53 generates a runtime error "3078": Unknown. (Q1-Q50)

I know it is pulling the fields, just not populating the concatenated fields with the data, because when I use the recordset to count the fields it returns 54.
Remember, if you open this exact query in either DB it will pull the appropriate data without any errors at all, and displayed correctly for all 54 fields.
The Q1-Q50 are many fields concatenated, with most native to the originating table, but two are from a foreign table, in the same DB using DLookup Commands. and the concatenated fields are very large with embedded spacing. (Small snippet include below) The query itself is not the issue.

The issue is why doesn't the entire complete recordset get populated when the Query is run? I have a feeling it is because of either:
1. The size of the data returned is too large, but that should not matter Right? If it pulls the data when opened from the navigation pane in either DB, then why won't it populate when called from one to the other.
2. The DLookup Commmands run on the wrong db or fails in some way? Meaning the query is in lc_db, but when called from the front end, it looks in the front end instead of the backend where their tables are located. Most likely the cause in some weird discreet way, because the first 4 fields populate just fine, its the fields with the DLookups that fail. But if this is the case, why didn't it fail when it was set? It should have populated all fields when the recordset was set like the query populates the data when run from either location.

Please when replying with suggestions please do not suggest using SQL backend or some other thing like that. If I could I would, but I can't so don't bother suggesting that. Please provide solutions to the problem at hand, with what I am asking for a solution for improper data coming in from a query that works natively in either db local or remote, but does not function when called by the local db from the remote db using MS Access only. I work in a DOD space and my options are very limited.

Thank you for your attention and consideration in this matter.

Reference information for die hard "need to see everything" people:
Database will globally distributed in an even far more complex situation then needs to be addressed here. Local db is the front end on their PC; lc_db is the encrypted backend on their local server. gl_db is the encypted global version that houses all statistics data in a globally accessible location.
CACID is a text field and a primary key for many tables. This is tied to DoD CAC ID Card Numbers. This number uniquely identifies a person can be alpha/numeric.
Test_Data - houses the test taken and results in Encrypted DB by CACID
Test - houses all possible test questions with feedback and supporting regulation references in Encrypted DB
qry_TestFeedback in both DBs (Right now anyways)
Here is a small piece of the query in question. If you want to understand the full scope duplicate [Q1Test] through AS Q1 and replace the "Q1" with numbers Q2-Q50 for each piece and add them in place of the ...AS Q50 before FROM to make the entire statement.
SELECT Test_Date.CACID, Test_Data.TCreatedDate, Test_Data.TScore, Test_Data.TPass, [Q1Text] & chr(13) & chr10 & chr(13) & chr10 &Correct Answer - " & [Q1Ans] & chr(13) & chr10 & chr(13) & chr10 & "Selected Answer - " & [Q1AnsSel] & chr(13) & chr10 & chr(13) & chr10 & "Answer - " & iif([Q1AnsMiss]=0,"Correct","False") & chr(13) & chr10 & chr(13) & chr10 & "Feedback"& chr(13) & chr10 & chr(13) & chr10 & Dlookup("[Test]![Feedback]", "[Test]", "[QuestionNum]="&int(Left([Q1BuildCode],3))) & chr(13) & chr10 & chr(13) & chr10 & "Regulation Reference" & chr(13) & chr10 & chr(13) & chr10 & Dlookup("[Test]![RegRef]", "[Test]", "[QuestionNum]="&int(Left([Q1BuildCode],3))) AS Q1,... AS Q50 FROM Test_Data WHERE (((Test_Data.CACID)-'1234567890'));

The BuildCode is formatted like this ###@*%^ that ties the data back to the Test table
### is question number' 1-108
@ is Question setup 1-7
* is actual Question asked 1-3
% is answer configuration 1-4
^ is the actual answer A-D
The test is 50 questions randomly selected from the 108, then randomly select Setups, then randomly selects actual questions and then randomly selects a question configuration with the correct answer. The process contains over 9000 possible question combinations that could be asked.
So build code 037324D would look completely different from 037511A when the generated question is viewed even though its on the same topic.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 13:23
Joined
Jan 20, 2009
Messages
12,268
You are probably not going to like the answers we give you. I suspect your back end data structure might not be ideal. Perhaps you could explain your underlying data structure so we can get a better picture. You seem to have a lot of stuff in just two tables.

Either way I think you would have to agree that six pages of text is not a practical query. I'm surprised Access hasn't already choked on it.
 

Ranman256

Well-known member
Local time
Yesterday, 23:23
Joined
Apr 9, 2015
Messages
3,805
Attach the tables. You can hide the tables and prevent users from seeing them
It's not a big deal.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:23
Joined
Oct 29, 2018
Messages
14,544
Hi. I suspect reason #2 may be correct. So, instead of using DLookup(), maybe try joining the two tables in your query.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:23
Joined
Feb 28, 2001
Messages
19,384
From the appearance of that sample query, I would say you are trying to generate a formatted report using Query Datasheet view. If there is any way to divide and conquer on this, do so. You said you could use a form. Which means you could also build a report with all of the formatting built in. Get rid of all those embedded vbCrLf and other formatting constants in the query, which will shorten it tremendously.

It might also reveal something useful by getting rid of the clutter, which makes that query incredibly hard to read. You would do well to simplify your life by splitting up the data retrieval and data presentation steps separate. You have DLookups involved, so this query could never be used as an updateable query.

The other alternative is even if you have to use a form because you have to capture answers, don't bind all of the visible fields to the query. Instead, have some invisible text boxes to receive the raw data (so the form is still bound) and then, during the Form_Current event, sweep through those fields that need formatting that is essentially constant and "fill in the blanks" on unbound text boxes that happen to correspond to the invisible bound fields. Take the formatting out of the query and I'll bet it won't be six pages long.

Having worked with the DoD for 28 1/2 years including over 10 years at Navy Enterprise Data Center - New Orleans, I understand that sometimes things get a little crazy. However, the DoD CANNOT get rid of the concept of Trusted Locations long-term. Too much within Windows and too much within Network setup depend on it. E.g. on-line training, deployment notices for IAVAs (vulnerability alerts), authorized patch repeaters, etc. You just KNOW that Windows 10 will not allow you to forever suppress updates and those updates will HAVE to come from a trusted location.
 

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
Hi. I suspect reason #2 may be correct. So, instead of using DLookup(), maybe try joining the two tables in your query.
Joining the two tables may not work very well. The Test_Data table stores a structured reference that loosely refers back to the Test Table via the BuildCode when the test was created; there is no field that directly relates to the other like key fields. That why I add the last part about the BuildCode and how it is formatted. the first three digits of the BuildCode is the Test Question reference number in the Test table it applies to. But you have me thinking now. I could build a separate query that performs the extraction of the test template code, and then convert that into an integer and then that could be tied back to the Test Table. But that would require 50 more queries, it would be the same as including the lookups in the actual query itself. Actually That was the first attempt, before I ended up with this.

Thank you for the food for thought,
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:23
Joined
Oct 29, 2018
Messages
14,544
Joining the two tables may not work very well. The Test_Data table stores a structured reference that loosely refers back to the Test Table via the BuildCode when the test was created; there is now fields that directly related to the other like key fields. The why is add the last part about the BuildCode and how it is formatted. the first three digits of the BuildCode is the Test Question reference number in the Test table it applies to. But you have me thinking now. I could build a separate query that performs the extraction of the test template code, and then convert that into an integer and then that could be tied back to the Test Table.

Thank you for the food for thought,
Let us know how it goes. Good luck!
 

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
You are probably not going to like the answers we give you. I suspect your back end data structure might not be ideal. Perhaps you could explain your underlying data structure so we can get a better picture. You seem to have a lot of stuff in just two tables.

Either way I think you would have to agree that six pages of text is not a practical query. I'm surprised Access hasn't already choked on it.
The query loads in about a second when run on either location. No real delay in performance.
the Test table houses the complete list of available test questions within their "Templates" Each Row is a new Template
Has these fields
ID (Autonumber)(PrimaryKey) (this is not related to anything)
QuestionNumber (Number) (This is used like a PrimaryKey, but manually created)
MISCAP_ID (Number) (Foreign Key to MISCAP Table - Identifies Subject Area of the Question)
P1V1 (ShortText)(QuestionSetup #1 )
P1V2 (ShortText)(QuestionSetup #2)
P1V3 (ShortText)(QuestionSetup #3)
P1V4 (ShortText)(QuestionSetup #4
P1V5 (ShortText)(QuestionSetup #5)
P1V6 (ShortText)(QuestionSetup #6)
P1V7 (ShortText)(QuestionSetup #7)
P2V1 (ShortText)(Question #1)
P2V2 (ShortText)(Question #2)
P2V3 (ShortText)(Question #3)
AnsV1 (LongText) (Answer configuration #1)
AnsV1Ca (ShortText)(Correct Answer for Configuration #1)
AnsV2 (LongText) (Answer configuration #2)
AnsV2Ca (ShortText)(Correct Answer for Configuration #2)
AnsV3 (LongText) (Answer configuration #3)
AnsV3Ca (ShortText)(Correct Answer for Configuration #3)
AnsV4 (LongText) (Answer configuration #4
AnsV4ca (ShortText)(Correct Answer for Configuration #4)
Feedback
RegRef

AT runtime when generating a test it:
Randomly Selects a test question# based on the MISCAP_ID for the area of the question,
Randomly selects P1V# (1-7)
Randomly selects P2V# (1-3)
Answer Configuration AnsV# (1-4)
Correct Answer based on above seelection (AnsV#Ca)) (A-D)
Concatenates everything except correct answer into preformatted Field in the Test_Data an SQL update statement updates the Test_Date Table where the CACID = the CACID of the person the test is being generated for with these fields. Q#BuildCode, Q#Text, Q#Ans

The Test_Data table has these fields: This table holds the test before they take it and afterwards with their results. Destroyed after 6 months via code
CACID (ShortText) (PrimaryKey) Identifies the person taking the test. Any table dealing with a user has this as the Primary Key
TPass (Y/N)
TCreatedDate (Date)
TComplDate (Date)
TScore (Number)
Q1BuildCode (ShortText) Describes how each test question was built at runtime. (First 3 = Test.QuestionNumber)
Q1Text (LongText) The actual formatted test question being asked.
Q1Ans (ShortText) The Correct Answer to this question as displayed in the BuildCode (This could be derived Right(BuildCode,1))
Q1AnsSel (ShortText) the answer someone selected during a test
Q1AnsMIss (Y/N) (This Could also be derived in a more complex calculation Right(BuildCode,1) = Q#AnsSel)
...
Q50AnsMiss (Y/N)
Q#s repeat until Q50 finishes 5 fields times 50 questions =250 fields with the 5 above. The maximum field number is reached.
To me it seemed easier to store the data than run all the calculations required to generate the data, each time I need it. And it fit, with no room to spare. its so tight I cannot even alter the properties of any of the fields, even reducing the field size, it fails on save.

These are the relevant table structures. The two tables are only linked via the first three digits of the Q#BuildCode for each test question back to the Test Table. So for each test there are 50 loose links back to the Test Table, but no direct relationships between the two tables and really no way to link them.
 
Last edited:

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
From the appearance of that sample query, I would say you are trying to generate a formatted report using Query Datasheet view. If there is any way to divide and conquer on this, do so. You said you could use a form. Which means you could also build a report with all of the formatting built in. Get rid of all those embedded vbCrLf and other formatting constants in the query, which will shorten it tremendously.

It might also reveal something useful by getting rid of the clutter, which makes that query incredibly hard to read. You would do well to simplify your life by splitting up the data retrieval and data presentation steps separate. You have DLookups involved, so this query could never be used as an updateable query.

The other alternative is even if you have to use a form because you have to capture answers, don't bind all of the visible fields to the query. Instead, have some invisible text boxes to receive the raw data (so the form is still bound) and then, during the Form_Current event, sweep through those fields that need formatting that is essentially constant and "fill in the blanks" on unbound text boxes that happen to correspond to the invisible bound fields. Take the formatting out of the query and I'll bet it won't be six pages long.

Having worked with the DoD for 28 1/2 years including over 10 years at Navy Enterprise Data Center - New Orleans, I understand that sometimes things get a little crazy. However, the DoD CANNOT get rid of the concept of Trusted Locations long-term. Too much within Windows and too much within Network setup depend on it. E.g. on-line training, deployment notices for IAVAs (vulnerability alerts), authorized patch repeaters, etc. You just KNOW that Windows 10 will not allow you to forever suppress updates and those updates will HAVE to come from a trusted location.

No this is not an updateable dataset. I am building the test feedback display. So it just displays static results. The form is not really necessary, but wouldn't creating the report data require the exact same data set? The vbCrLfs are not the issue, it only adds to the clutter. The problem appears tp be the DLookups, as I suspeccted.

Each Page of the Form/Report/view would be a Test Question. I guess I could use a function I retooled from someone else's work I Believe Allen Browne and his ELookup Function. I modified that to run on a remote Database. Basically I added a Optional dbObj that takes a DAO.Database object. Then uses that to query the remote database like the DLookup function does for the CurrentDB and gets the required information. I Could reformat the the Feedback to display only the information from the Test_Data table, then use the ELookup function to look up the required data on the Form's OnCurrent event from the Test Table, Hold On, its only trickery that makes it look like the users is moving from one record to the next, the test Feedback table is flat. But that also works to this advantage because the form is rebuilt each time they press one of my arrow buttons. So I could populate an unbound field with each button press.

I Talked to my Network tech and we looked at the Trusted Locations on the Air Force Network Standard Desktop configuration. The Trusted Center had Zero trusted locations and the trusted location area was locked down, even for him. Even the default MS Office location for Wizards is Disabled, We Get Zero Wizards for anything. So he said it was probably a no go. For the application I am developing it has to fit within that environment without changes in that environment, I.e. no trusted locations. Without being able to deploy an accde formatted file, anyone can break into the file by remotely re-enabling the shift key, Hit F11 to open the code window, and then start probing the TableDefs and extract all the table names, then set all table Attributes = 0, exposing all tables to view, then open the linked test table a view every answer to every question. The actual VBA code is Password Protected so they cannot break in to that. But they still can execute some commands, they could even do all of it remotely. But that is only when the table is linked, when not linked it resides in the encrypted DB protected to the maximum extant possible.

Thank you for the fresh perspective.
 
Last edited:

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
Hi. I suspect reason #2 may be correct. So, instead of using DLookup(), maybe try joining the two tables in your query.

Please look at my reply to Galaxiom regarding your response about joining the tables, which is not really possible.

Thank you for your reply.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:23
Joined
Oct 29, 2018
Messages
14,544
Please look at my reply to Galaxiom regarding your response about joining the tables, which is not really possible.

Thank you for your reply.
Sorry, I may not agree, but I'll take a look again to see if I missed anything. Essentially, by joining, you may be thinking the joined fields have to be equal. If so, I suggest you look up non-equi joins. If you are already aware of them, then I guess I did miss something and will let you know when I realize what that is. Cheers!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:23
Joined
Oct 29, 2018
Messages
14,544
The two tables are only linked via the first three digits of the Q#BuildCode for each test question back to the Test Table. So for each test there are 50 loose links back to the Test Table, but no direct relationships between the two tables and really no way to link them.
Okay, I finished reviewing your earlier post and quoted the above statements. The bolded parts are, in my humble opinion, contrary to each other. Looks to me like you can link the two tables using a non-equi join. Please let me know if I misinterpreted your situation.
 

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
Thank you all for your responses so far. Seeing that most people agree that the DLookups are the issue and linking the tables in some way might be the way to go. But directly linking the tables is impossible. Each row in Test_Data has 50 links to the Test Table via the first three digits of the BuildCode. So matter how you cut it there has to be 50 separate queries back to the Test table, one for each BuildCode, and actually was the first design I created. But then realized the queries were doing he same thing as what a DLolokup could perform, but I'd have 50 or 100 less objects to deal with. So I decide on one very complex object instead of potentially hundreds of less complex objects. I mean it works natively in each DB. The only problem is having it cross over one DB to another, and that is where it fails.

I like The_Doc_Man's idea of using unbound boxes at runtime. But again is calling this data forth from a remote DB. The data to populate the box comes only from the encrypted db, which I don't want the table linked in the front end. This is the easiest solution and solves all the problems, I agree. In fact that is the existing process, before I started this process change.
Okay, I finished reviewing your earlier post and quoted the above statements. The bolded parts are, in my humble opinion, contrary to each other. Looks to me like you can link the two tables using a non-equi join. Please let me know if I misinterpreted your situation.
There are 50 BuildCodes for each Record in the Test_Data table. So you would have to have 50 joins to the Test_Data to the Test table, one for each question 1-50, to get the desired results.
I Showed structure in a earlier reply. Let me fill in some of the data so you can see what I am talking about
Test Table
ID, Q#, MISCAP_ID, P1V1, P1v2, P1V3, P1V4, P1v5, P1V6, P1V7, P2V1, P2v2, P2v3, Ans1, Ans1Ca - Ans4, Ans4Ca, Feedback, RegRef
2, _1 , ____15_____ , Text1,Text2, Text3, Text4, Text5, Text6, Text7, 1Txt, 2Txt, 3Txt, Txt1, Txt1A, -- Txt4, Txt4A, _____FBText, _RRText
3, 2 , ____ 6______ , Text1,Text2, Text3, Text4, Text5, Text6, Text7, 1Txt, 2Txt, 3Txt, Txt1, Txt1A, -- Txt4, Txt4A, _____FBText, _RRText
nnn, nnn, ___ n ___ , Text1,Text2, Text3, Text4, Text5, Text6, Text7, 1Txt, 2Txt, 3Txt, Txt1, Txt1A, -- Txt4, Txt4A, _____FBText, _RRText
110, 108 , ___9____, Text1,Text2, Text3, Text4, Text5, Text6, Text7, 1Txt, 2Txt, 3Txt, Txt1, Txt1A, -- Txt4, Txt4A, _____FBText, _RRText

Buildcode would concatenate these parts into one field for the example shown below Q1BuildCode is 002323D
For Q# 002 - Q1Text = Text3 & " " & 1Txt & vbcrlf & vbCrLf & Txt3
BuildCode for Q2Text would be lets say Q2BuildCode is 047234A
For Q# 047 - Q2Text = Text2 & " " & 3Txt & vbcrlf & vbCrLf & Txt4
This process repeats until 50 Questions are randomly built. Leading to 50 BuildCodes Stored in a flat table in one record for a user.

Test_Data (This is simplified for sake of space.) (one row equates to 1 50-Question Test)
CACID______, TCreatedDate, TComplDate, TScore, TPass, Q1BuildCode, Q1Text, Q2BuildCode, Q2Text ... Through ... Q50Text
1234567890, _Some Date__, Some Date_, _____0_, ___N_, ____002313D_, Q1Text, ___047234A_, Q2Text ;;; Through ... Q50Text

So would I not have to bind the tables together at each Q#BuildCode creating 50 Joins to pull in the data required.

So in a graphical sense here is what is being proposed by joining the tables
Test_Data (Simplified again)
Q1BuildCode, Q2BuildCode, Q3BuildCode, etc. etc. Q50BuildCode
___ |_________, ______ |______, ______|_______, _______, ______|________
Left(Q1BC,3), Left(Q2BC,3), Left(Q3BC,3) , _______, Left(Q50BC,3)
Join_________, Join_________, Join_________, _______, Join
Test_________, Test_________, Test _________, _______,Test
Q#=LeftCalc Q#= LeftCalc Q#= LeftCalc _______, Q#= LeftCalc

The build code is how the data relates back to the Test Table and there area 50 build codes in a test, i.e. in each Record of the Test_Data table. To join them correctly there would need to be 50 joins/links. Of course the BuildCode is a Text Field so the function Int() would encapsulate the Left() function so it would match the Q# Data Type.

Hope this paints a clearer picture of the data structures for everyone.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:23
Joined
Feb 28, 2001
Messages
19,384
From your first post: "I am attempting to call the query from the front end, when not connecting the tables to the front end."

A DLookup builds an implied query for which it requires the ability to look up information about the target domain's structure (in order to properly build the query.) The rule for DLookup says that you have DLookup( field, domain, criteria ) and that the domain must be either a table name or a stored-query name. You cannot put SQL in the domain field. (We've tried in a sequence of posts a couple of years ago. Miserable failure.)

The domain in this case is not connected, therefore not available, therefore you cannot find it, and that is strike one. You cannot (mechanically) validate the field name, so that is strike two. And you cannot (mechanically) validate the fields used in the criteria clause, and that is strike three. You said that the queries work correctly if either the BE is opened directly with a copy of the query - but of course the domain is local so no problem with the mechanical steps of validation and construction. You also said it would work if you made local FE copies. Again, same logic, same result. Without that connection, I think DLookup will always have to fail.
 

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
Solved my problem of getting the data from the backend without actually linking the tables . I implemented a version of The_Doc_Man's concept of unbound objects.
I created a global TestFdBk_CACID to store the targeted CACID for the feedback form to display. Sometimes it the user who took he test, or its some one in their leadership chain looking at the results of the test. So I need a way to Seed the CACID no matter where it was being called from.
I then copied my bound form and removed the data source and made the 6 fields unbound.
The code on the load event of the form looks like this:
cln = "0000": if gl_UseErrHandler then On Error GoTo Err_Ctrl: DoCmd.SetWarnings False
cln = "0001": modRef = "Form": procRef = "frm_Test_Feedback_Unbound": cdLnum = modRef & "|" & procRef & "|" & "Line"
cln = "0010": str_CACID = TestFdBk_CACID
cln = "0011": tstPass = ELookup(cdLnum & cln, "[TPass]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0012": dtTCompl = Elookup(cdLnum & cln, "[TComplDate]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0013": int_Score = ELookup(cdLnum & cln, "[TScore]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0014": QuesText =ELookup(cdLnum & cln, "[Q1Text]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0015": QuesAnswrd = ELookup(cdLnum & cln, "[Q1AnsSel]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0016": QuesMiss = ELookup(cdLnum & cln, "[Q1AnsMIss]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0017": QuesCorAns = Right(ELookup(cdLnum & cln, "[Q1BuildCode]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True),1)
cln = "0018": trgQuesNum = Int(Left(ELookup(cdLnum & cln, "[Q1BuildCode]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True),3))
cln = "0019": QuesFdBk = ELookup((cdLnum & cln, "[Feedback]", "[Test]", "[QuestionNum] = " & trgQuesNum & ", lc_db, True)
cln = "0020": QuesRR = ELookup((cdLnum & cln, "[RegRef]", "[Test]", "[QuestionNum] = " & trgQuesNum & ", lc_db, True)
cln = "0040": strSpacing = vbCrLf & vbCrLf
cln = "0041": str_QText = QuesText & strSpacing
cln = "0042": str_QText = str_QText & "Correct Answer - " & QuesCorAns & strSpacing
cln = "0043": str_Qtext = str_QTeext & "Selected Answer - " & QuesAnswrd & srSpacing
cln = "0044:"
______________If QuesMiss Then
cln = "0045": ____str_QText = str_QText & "Answered Incorrecctly" & strSpacing
cln = "0046":
______________Else
cln = "0047": ____str_QText = str_QText & "Answered Correctly" & strSpacing
cln = "0048": End IF
cln = "0049": str_QText = str_QText & "Feedback - " & QuesFdBk & strSpacing
cln = "0050": str_QText = str_Qtext & "Regulation Reference - " & QuesRR

Of course my mv buttons have similar code but slightly modified
For btn_GOTO_First essentially the same except str_CACID = me.CACID.Value Pulling the reference number
For btn_GOTO_Last the Q1 above are replace with Q50

For both btn_GOTO_Next and btn_GOTO_Previous
The "[Q1blh} had to change to "[Q" &qNum & "Blah]"

I guess I should explain a few things. I use a global error handler and I could never get the err.el to work properly. So I implemented a hard coded Error line reference numbering system. That is why on the ELookup Command they start with the calling code reference so when the ELookup code Breaks, I know which line of code called the function and trace the root of the problem. Without that the ELookup Code returns the cln reference in that function so no help with the offending line of code. I also use a constant to tell if I am development mode. The use has Zero access to the Navigation Pane or Any toolbars.

So it works great now no real lag between question numbers.

Thank you everyone for you time and consideration in this matter I truly appreciate it.
 

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
From your first post: "I am attempting to call the query from the front end, when not connecting the tables to the front end."

A DLookup builds an implied query for which it requires the ability to look up information about the target domain's structure (in order to properly build the query.) The rule for DLookup says that you have DLookup( field, domain, criteria ) and that the domain must be either a table name or a stored-query name. You cannot put SQL in the domain field. (We've tried in a sequence of posts a couple of years ago. Miserable failure.)

The domain in this case is not connected, therefore not available, therefore you cannot find it, and that is strike one. You cannot (mechanically) validate the field name, so that is strike two. And you cannot (mechanically) validate the fields used in the criteria clause, and that is strike three. You said that the queries work correctly if either the BE is opened directly with a copy of the query - but of course the domain is local so no problem with the mechanical steps of validation and construction. You also said it would work if you made local FE copies. Again, same logic, same result. Without that connection, I think DLookup will always have to fail.
I Had an idea that it was that.
But I took Your Idea of unbound fields and amped it up, Made everything unbound and looked up everything remotely via Code and then plugged it into the form fields.
I created a ELookup function from Allen Browne's ELookup implementation that does on a Remote database, what DLookup does for the CurrentDB.
I posted the ELookup Function I am using, if you or anyone would like to test/use it.

I just posted my end results.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:23
Joined
Oct 29, 2018
Messages
14,544
Solved my problem of getting the data from the backend without actually linking the tables . I implemented a version of The_Doc_Man's concept of unbound objects.
I created a global TestFdBk_CACID to store the targeted CACID for the feedback form to display. Sometimes it the user who took he test, or its some one in their leadership chain looking at the results of the test. So I need a way to Seed the CACID no matter where it was being called from.
I then copied my bound form and removed the data source and made the 6 fields unbound.
The code on the load event of the form looks like this:
cln = "0000": if gl_UseErrHandler then On Error GoTo Err_Ctrl: DoCmd.SetWarnings False
cln = "0001": modRef = "Form": procRef = "frm_Test_Feedback_Unbound": cdLnum = modRef & "|" & procRef & "|" & "Line"
cln = "0010": str_CACID = TestFdBk_CACID
cln = "0011": tstPass = ELookup(cdLnum & cln, "[TPass]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0012": dtTCompl = Elookup(cdLnum & cln, "[TComplDate]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0013": int_Score = ELookup(cdLnum & cln, "[TScore]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0014": QuesText =ELookup(cdLnum & cln, "[Q1Text]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0015": QuesAnswrd = ELookup(cdLnum & cln, "[Q1AnsSel]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0016": QuesMiss = ELookup(cdLnum & cln, "[Q1AnsMIss]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True)
cln = "0017": QuesCorAns = Right(ELookup(cdLnum & cln, "[Q1BuildCode]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True),1)
cln = "0018": trgQuesNum = Int(Left(ELookup(cdLnum & cln, "[Q1BuildCode]", "[Test_Data]", "[CACID] = '" & str_CACID & "'", lc_db, True),3))
cln = "0019": QuesFdBk = ELookup((cdLnum & cln, "[Feedback]", "[Test]", "[QuestionNum] = " & trgQuesNum & ", lc_db, True)
cln = "0020": QuesRR = ELookup((cdLnum & cln, "[RegRef]", "[Test]", "[QuestionNum] = " & trgQuesNum & ", lc_db, True)
cln = "0040": strSpacing = vbCrLf & vbCrLf
cln = "0041": str_QText = QuesText & strSpacing
cln = "0042": str_QText = str_QText & "Correct Answer - " & QuesCorAns & strSpacing
cln = "0043": str_Qtext = str_QTeext & "Selected Answer - " & QuesAnswrd & srSpacing
cln = "0044:"
______________If QuesMiss Then
cln = "0045": ____str_QText = str_QText & "Answered Incorrecctly" & strSpacing
cln = "0046":
______________Else
cln = "0047": ____str_QText = str_QText & "Answered Correctly" & strSpacing
cln = "0048": End IF
cln = "0049": str_QText = str_QText & "Feedback - " & QuesFdBk & strSpacing
cln = "0050": str_QText = str_Qtext & "Regulation Reference - " & QuesRR

Of course my mv buttons have similar code but slightly modified
For btn_GOTO_First essentially the same except str_CACID = me.CACID.Value Pulling the reference number
For btn_GOTO_Last the Q1 above are replace with Q50

For both btn_GOTO_Next and btn_GOTO_Previous
The "[Q1blh} had to change to "[Q" &qNum & "Blah]"

I guess I should explain a few things. I use a global error handler and I could never get the err.el to work properly. So I implemented a hard coded Error line reference numbering system. That is why on the ELookup Command they start with the calling code reference so when the ELookup code Breaks, I know which line of code called the function and trace the root of the problem. Without that the ELookup Code returns the cln reference in that function so no help with the offending line of code. I also use a constant to tell if I am development mode. The use has Zero access to the Navigation Pane or Any toolbars.

So it works great now no real lag between question numbers.

Thank you everyone for you time and consideration in this matter I truly appreciate it.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

kmolmstead

New member
Local time
Yesterday, 22:23
Joined
May 27, 2020
Messages
22
Here is the function that I am using in my final solution to use like DLookup Functionaly but in a External DB without existing table links to the referenced tables. Again not solely Mine. Please if you use please keep the Author Information. Also please remember I am hand jamming this in because my development system is offline with no way of getting online so there will be slight typos. Please bear that in mind.

Public Function ELookup( CallRef as String, Expr as String, Domain as String, Optional strSQLCriteria as string, Optional dbObj as DAO.Database, DbProv as Boolean, OrderClause as Variant) as Variant
'CallRef is only for my error handler you can kill that piece
'DbProv - is true if you provided dbObj, empty(False) if you did not. The problem with dbObj it is nothing and not Missing so you cannot identify if the dbObj does have an empty DB reference or the dbObj is not provided. The DbProv says you provided one to check for and use it.
'Original Author : Allen Browne, allen@allenbrowne.com
'Updated: December 2006, April 2010
'*Modified by: Kevin Olmstead
"*Modified Date: June 2020
'*Upgraded : Now includes ability to select a remote database object the use ELookup against.
'*Will accept same formatting as the DLookup("[Field]", "
", "[FirldCrit] = Criteria") or only text values
'*Now has added functionality of being able to search external DBs that have been declared via Set statements
'Examples:
'1. To Find the last value, include DESC in the OrderClause
' ELookup("[Surname] & [FirstName]", "[tblClient]", , False, "CliertID DESC")
'2. To find the lowest non-null value of a field use the criteria
' ELookup("ClientID", "tblClient", "Surnamere Is Not Null", , , "Surname")

Dim tgtdb As DAO.Database
Dim tgtrs As DAO.Recordset
Dim tgtrsMVF as DAO.Recordset
Dim strSQL as String
Dim strOut as String
dim lngLen as Long
Const strcSep = ", "

cln = "000001": varResult - Null
cln = "000002": strSQL = "SELECT Top 1 " & Expr & " FROM " & Domain
cln = "000003": If Not IsMissing(strSQLCriteria) then strSQL= strSQL & " WHERE " & strSQLCriteria
cln = "000004": If Not IsMIssing(OrderClause) Then strSQL = strSQL & " ORDER BY " & OrderClause

cln = "000100":
_________________If DbProv = False Then
cln = "000101:_____Set tgtdb = CurrentDb
cln = "000150":
_________________Else
cln = "000151":_____If dbObj Is Nothing then Call SetEnviro
cln = "000152":_____Set tgtdb = dbObj
cln = "000199": End IF

cln = "000200": Set tgtrs = tgtdb.OpenRecordset(srtSQL, dbForwardOnly)
cln = "000300":
_________________If tgtrs.RecordCount > o then
cln = "000300":
_____________________If VarType(tgtrs(0)) = vbObject Then
cln = "000302": _________Set tgtrsMVF = tgtrs(0)
Cln = "000303":_________Do While Not tgtrsMVF.EOF
cln = "000304":
______________________________If tgtsr(0).type = 101 Then
cln = "000305":__________________strOut = strOut & tgtrsMVF!FileName & strcSep
cln = "000396:
______________________________Else
cln = "000307":__________________strOut = strOut & tgtrsMVR![Value].Value & strcSep
cln = "000308":______________End IF
cln = "000309":______________tgtrsMVF.MoveNext
cln = "000310":_________Loop
cln = "000311":_________lngLen = Len(StrOut) - Len(strcSep)
cln = "000312
_________________________If lngLen > 0& Then VarResult = Left(StrOur, lngLen)
cln = "000313":
_________________________Else
Cln = "000314":____________VarResult = tgtrs(0)
Cln = "000315":________End IF
cln = "000319": End IF
cln = "00330": tgtrs.close
cln = "00399": ELookup = varResult

Exit_ELookup:
______________Exit Function

Err_ELookup:
____erln = Erl
____ErrNum = Err.Number
____Select Case ErrNUm
____Case 3031 ' means the referenced dbObj is really nothing and needs to be reconnected by resetting the environment variables
_________Call SetEnviro
_________Set tgtrs = tgtdb.OpenRecordset(ssrtSQL, dbForwardOnly)
_____Case Else
__________ErrDesc = Err.Description
__________modRef = "Mod3": procRef = "ELookup"
__________errMsgStr = "Calling Reference is " & CallRef
__________cdLnum = modRef & "|" & procRef & "|Line" & cln
__________call gsubErrorHandler (ErrNum, ErrDescc, ctrlfnctnm, erln, , , errMsgStr, cdLnum)
__________Resume Exit_ELookup
____End Select
End Function

To use this you need a set statement like mine:
Global lc_db as DAO.Database
Set lc_db = DBEngine.OpenDatabase(Name:=UNC_AppPath, Options:=False, ReadOnly:=False, Connect:="; PWD=" & lc_dbPassword)

Then the SQL statements can be run against the lc_db object exactly the same as the CurrentDB Object.
I use this concept quite abit in my project Set SQLStatement, then lc_db.execute SQLStatement Bam! updated/inserted/deleted etc. remotely.

Happy Coding Everyone and Thanks again.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:23
Joined
Feb 28, 2001
Messages
19,384
Beware of one thing: If the form is completely unbound, it will not have an OnCurrent event. So, though the OnCurrent event is the ideal place for some of this code, it doesn't exist if nothing is bound.

I'm glad that you found something useful in my post. The most important result, though, is that you feel you made progress and that is why we are all here. Good luck in that USAF environment. USN had its moments, too. Actually, until my brother-in-law retired, he was USAF, first as military and later as civil service. So I know a little about his environment, too.
 

Users who are viewing this thread

Top Bottom