Solved MS Access Query of remote Database pulling partial data

[kmolmstead]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Thanks.
Also Left a Function you might be interested in. It allows remote DLookup Capabilities, without connecting the tables to the Front End. It does require a Set dbObjVariable = DBEngine.OpenDatabase statement though, but after that works great.

 

[kmolmstead]

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.

Agreed. I killed all of the OnCurrent code because like you said nothing is changing. Did you checkout the ELookup Function I Left for everyone. Its basically remote DLookup Capability, without the need to connect tables. You posted earlier that you had a failed attempt at making DLookup function via remote DBs. This might solve that problem.

Let me know what you think.
Thanks again.

 

[kmolmstead]

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.

Also, I am exploring the concept of a virtual recordset. Using the concept from ELookop where a remote recordset is opened, specifically
tgtdb.OpenRecordset(strSQL, dbForwardOnly).
Well could not this be used to create a remote recordset and then bind a local form to that recordset, virtually?

Ah Man! it works amazingly well. And no annoying Security Warnings Either when "virtually" pulling the data as opposed to linking the table. Most of my forms use a non-updatable recordset. Now I don't have to destroy the linked table either because it was not created.

 

[Deleted member 147267]

If the form is completely unbound, it will not have an OnCurrent event.

Actually it does. It's just that I doubt you can get it to fire more than once (upon form opening). But it will fire then.

 

[The_Doc_Man]

True, Micron - you get one shot and then nevermore unless it is bound. But all it takes is having one bound field and you get the event every time. It might even be bound to an invisible field. What matters is that when you navigate and do saves and such, you can get OnCurrent to fire and that will give you the driving force for code to re-load everything, which is something I inferred would be desirable here.

 

[kmolmstead]

Actually it does. It's just that I doubt you can get it to fire more than once (upon form opening). But it will fire then.

Thanks for the spotlight on this.

 

[poliadisa]

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,

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