Solved Stub method is giving me issues (1 Viewer)

I am with Pat, this is such an overly engineered approach to do a trivial task in Access. If you did it using Access and not working around it.
If you want to go this route and you are experienced in .net then you are much better off building it in .net as the front end and using either your access db as the back end or migrating to sql server. You have more direct control if you go with a .net FE.
Since it runs on access it will run on a free version of SQL. Either go that route or stop what you are doing and do it like everyone else would do in Access. But this whole moving to a dictionary is silly, IMO.
 
and I did consult with the AI about some things like using the .Tag line to pass metadata and that the PK isn't assigned to a stub until the .Update event and that I needed to use DAO to make access play nice,
That information is incorrect. Very much of the info regarding how Access works is simply made up based on the AI understanding of how other apps do the same thing.

When the BE is Jet/ACE the autonumber PK is assigned immediately as soon as you dirty the record (type one single character in any control). You would see this on any form where the PK is visible. When the BE is SQL Server or other RDBMS, the PK is not assigned by Access and so is not generated until you try to save the record at which time Access sends the append query to the server. The server then returns the identity column which will show in the PK control if it is visible. In the case of Jet/ACE, the autonumber is burned and leaves a gap if you don't actually end up saving the record. This disturbs people because they have a hard time understanding the purpose of the autonumber and don't like seeing gaps.

Only your AI thinks that you need DAO to make Access "behave". No actual Access developer would share that opinion. Part of your dislike for Access is the misinformation you are getting from whatever AI you are using. @Uncle Gizmo is our most AI conversant expert and he is working with several. Perhaps he will chime in and suggest which of the AI's he uses has the best actual knowledge of Access and VBA.

The best feature of Access is bound forms. That's what saves you tons of work once you understand how you should work with them. You are banging your head on a wall as you fight with Access. Either stop using Access and recode the app using a platform you are familiar with as MajP suggested or learn how to use Access so you can stop billing your client for so much unnecessary work. You can still use the badly designed ACE BE if you don't know how to fix that.

If you want to protect the client data, run some update queries to scramble it. We prefer that anyway. Someone has posted a tool I believe but I can't remember who. It might have been @MajP.
 
Last edited:
Not me, but here.
 
Thanks for your insight guys, my boss decided to have someone he knows come work with me that knows access so I will probably be scraping the whole stub thing after all.
 
If you look at Access for what it is - a Rapid Application Development tool and keep an open mind, you will be amazed by how productive you can be with this smart tool and how capable it truly is. The trick is to accept the "Access way" and stop trying to control the things Access feels that it should control.

Given your programming background, I really think that viewing those videos and playing with the logging database will give you a huge insight into how Access wants to work. You're probably also experienced enough to understand that if you created a complicated and unmaintainable procedure, it is probably easier to just log what you learned from the exercise and start again from scratch. Remember - KISS. Don't write code you don't need to write.

Good luck.
 
@Uncle Gizmo is our most AI conversant expert and he is working with several. Perhaps he will chime in and suggest which of the AI's he uses has the best actual knowledge of Access and VBA.

Well, I wouldn’t go that far!!!

I find working with AI as exhilarating as when I first conquered MS Access and became conversant with its fantastic abilities. I get the same thrill now, working with large language models. The learning curve is much the same—you learn by making mistakes—and I’ve learnt a lot!!!

The biggest mistake you can make with a large language model is believing what it tells you. My first educational experience came about when I answered a question on the Access World Forum about MS Access VBA. The LLM had supplied me with Microsoft Excel VBA! That’s when I realized that large language models don’t really have a clue. The solution is down to you—it’s how you ask the question.

I specify in my question something along these lines as a prompt:

DRAFT
You are an expert in Microsoft Access VBA. You must be aware that there are different flavours of VBA. The VBA for Microsoft Office differs across programs: for instance, you cannot use some parts of Excel VBA in Access and vice versa, the same applies to Word and other Microsoft programs. When you return a code solution, ensure that it comes from a Microsoft Access-based source. Some large language models can search the internet, and if you are using one of those, instruct them to check against the Microsoft examples on the Microsoft website.

As you can see, that’s a terrible prompt—I just wrote it off the cuff for this post. But here’s a tip for you:

You’re using a large language model to help you, so feed your DRAFT prompt attempt back to the large language model, and it will give you a polished prompt.

The following is a rough draft of a prompt I want to give to a large language model to help me get an expert answer to a Microsoft Access question. Please could you spell-check and punctuate it, and adjust the wording to suit. <<<

Prompt Returned:
You are an expert in Microsoft Access VBA. Only provide code and solutions that are valid specifically for Microsoft Access, not Excel, Word, or other Office applications. If code differs between Office programs, always choose the Access version. When possible, cross-check your answer with official Microsoft documentation to ensure accuracy.

The large language model, ever helpful, also suggested a shorter version:

System instruction (strict):

“Respond only with solutions valid for Microsoft Access VBA; reject or ignore any code that applies to Excel, Word, or other VBA environments.”

Now, the next thing you should bear in mind is that if it can’t find an answer in the official documentation, it will resort to forums, GitHub, and other internet resources. These resources allow people to discuss their issues and suggest solutions—but who’s suggesting the solution? Is it coming from a top MS Access developer? You can’t know for sure. Unless you have a pretty good idea about MS Access, you could wind up using a really crappy idea.

This is why you have to use large language models frequently—constantly—to suss them out. And even then, I still get caught out!!! I’ve wasted hours, sometimes a whole day, chasing my tail through something I thought was a good solution provided by the model, only to discover it wasn’t.

However, the more you use a large language model, the more experience you’ll have in spotting when it’s hallucinating or “swinging the lead.” Sometimes I think it even takes the piss… and I’m not joking around. Some of the forums where it gets this knowledge are manned by some very unsavoury people, and the large language models pick up on this. I know this for a fact, because I had a good chat with a model to the point where I practically broke its conditioning and got it to answer sensibly. I blogged about it somewhere else…
 
but who’s suggesting the solution? Is it coming from a top MS Access developer?
I should add that this forum Access World Forums (AWF) is manned (and ladyed) by the top MS Access developers --- I know that because many of them recently joined from Utter Access - which according to Microsoft is/was the preeminent resource for MS Access help & advice ( where's the tongue-in cheek icon?) --- Another thing you should bear in mind - with this forum - every question you ask and every answer given is examined by two or three MS Access experts --- You could not get better advice for free anywhere ---- not even from a large language model...
 
I always start my question to ChatGPT as ' Access VBA to '
Another prompt I've heard about, but not yet had a chance to try is along the lines of.

"You've been hired as the senior developer on an Access VBA project. Use your most advanced VBA skills to solve the following problems/address the following requirements/create the following solution."
 
And yet technically, that is not correct. As of a version of Access some years ago, after Ac2003 but before Ac2016, ALL Office members that use VBA use the same VBA library, so Access VBA IS Excel VBA IS Word VBA etc. The differences are the behavior and nature of the objects being referenced. Heck, I've even seen some terminal emulator software that allowed use of VBA to handle special emulation issues. I never tried to do it, but I verified that it was there and was using the VBA library.
 
And yet technically, that is not correct. As of a version of Access some years ago, after Ac2003 but before Ac2016, ALL Office members that use VBA use the same VBA library, so Access VBA IS Excel VBA IS Word VBA etc. The differences are the behavior and nature of the objects being referenced. Heck, I've even seen some terminal emulator software that allowed use of VBA to handle special emulation issues. I never tried to do it, but I verified that it was there and was using the VBA library.
Technically correct, but beside the point.

As noted, "The differences are the behavior and nature of the objects being referenced. "

The point is to provide sufficient context to the prompt so that answers will should be pertinent to the task at hand. In this case, the context is an Access project, not a Word project and not an Excel project. The objects being referenced are Access objects, not Word objects and not Excel objects. Providing that context as the lead-in to the request is relevant and potentially crucial to getting useful responses.

AI can appear to be quite bright but still not fully grasp context. Prompt engineering requires you to provide guidance on what you do and do not want to include, or as someone has said, you need too assume the role of adult in the room.
 
Technically correct, but beside the point.

The point was that previous posts didn't make the distinction that you did. Microsoft VBA is the same, syntactically, for all Office members. There is one and only one reference document for VBA. And MAN, do they ever have to split hairs when dealing with object references.
 
One of the gotchas is that Access Forms are different from Form objects used in other Office products.
 

Users who are viewing this thread

Back
Top Bottom