kmolmstead
New member
- Local time
- Today, 05:09
- 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.
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.