Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ General Chat _ Visual Studio 2019

Posted by: Raas Oct 7 2019, 05:32 PM

I'm not sure where to ask this question, so here it is anyway:
I have just installed Visual Studio 2019 and attempt to connect to an Access .accdb database (2019). I get an error of datatype not recognized. If I attempt to connect to one of my archived .mdb databases, it will do so no problem. Seems funny that MS wouldn't even recognize it's own database.

If anyone has an answer/suggestion I'm willing to listen. Otherwise can someone give me a URL to a real support site for Visual Studio. The VS forum is a total waste of time. I've posted there, as have several others with the same problem, and we just get ignored. A live chat to someone would help greatly.
Thanks

Posted by: GroverParkGeorge Oct 7 2019, 05:44 PM

Just to be clear, how did you go about making the connection.

I have VS 2017 installed. I can connect to accdbs using the Microsoft Access Database File (OLE DB) provider (Microsoft.ACE.OLEDB.12.0). Is that what you are using?

Posted by: GroverParkGeorge Oct 7 2019, 05:46 PM

I forgot to ask if you were able to connect correctly in a prior version of VS.

Posted by: Raas Oct 7 2019, 06:03 PM

Didn't try with a prior version. My only connection previously was to MS SQL Server and Oracle.
I used the only database connectivity that will show in VS 2019 in the connection wizard. It's in the Choose Data Source dialog box. I can only select one source for Access. The only Data source available is OLE DB.




Posted by: GroverParkGeorge Oct 7 2019, 06:11 PM

I will ask someone. However, I wonder if you can get to this dialog in VS 2019.



Note that the only option offered prior to creating the connection simply refers to "Access", but once you have it, you can choose "Advanced" and select either JET or ACE. JET does work with mdbs, ACE with accdbs. It may be the default is to JET?

Posted by: Raas Oct 7 2019, 06:16 PM

I try that but the only Source > Provider that is accessible is Microsoft Jet.OLEDB.4.0.

Posted by: Raas Oct 7 2019, 06:19 PM

Hmm. I just went past the Advanced and now there are lots of Jet OLEDB connections. Are you possibly suggesting I use ACE, because I can't find anything but Jet.

Posted by: Raas Oct 7 2019, 06:22 PM

I wonder if your friend would know if it's something I missed in the installation. If so, I would uninstall and re-install, or at least maybe find where to install just the ACE provider. Also, is it possible that VS 2019, being only 32 bit, is not compatible with Access 2019 64 bit? I hope not.

Posted by: GroverParkGeorge Oct 7 2019, 06:22 PM

That would be a problem, yes.

I really hope MS didn't do this. If there is no ACE provider in VS 2019, we have a major problem, IMO. I'm waiting on a response

Posted by: GroverParkGeorge Oct 7 2019, 06:29 PM

I asked the Access MVP group. One of them should have experience.

But I wonder if the ACE provider is installed on your machine. Do you have Office installed?

Posted by: Raas Oct 7 2019, 06:32 PM

Yes. I have the permanent version, not subscription 365. I have the full Office 2019 installed.

Posted by: AlbertKallal Oct 7 2019, 09:02 PM

VS does not install the ACE provider (and it never did)

You should see the choice for ACE,and you HAVE to use ACE if you going to connect to a accDB file. (JET is limited to using mdb files).

The ACE provider "should" exist if you have any recent version of Access installed. So, you could install say the free runtime for 2013 or even 2016, and once done, then the dialog George screen capped should give you the ACE option.

As a general note, you could (should) also set + force your project to x86 since the version of ACE installed is going to be ONLY x32.

I seen some posts that suggest installing the CTR (click to run) versions of Access don't always install the references to ACE correctly.

If you have any recent version of Access (say 2010 to 2016) installed, then you could exit VS, and from the control panel (programs and features) right click on the office/access install and do a repair.

So having ACE, or missing ACE is not the fault of VS but that of you having a working (and more important registered) copy of ACE installed.

Regards,
Albert D. Kallal


Posted by: Raas Oct 7 2019, 09:55 PM

As mentioned in my posts above. I have the absolute latest version of Office and Access 2019 installed. Access runs fine, so I guess I have ACE installed somewhere. Don't know where, or how to get it available to Visual Studio.

If I create an Access project in 32 bit just to be able to use in VS later, then I can't run them in Access 64 bit. I get a runtime error that says the project was created in 32 bit and I can't run them in 64 bit.

So, since it seems that I have ACE loaded somewhere in order to run Access .accdb databases, could one of you point me to where I need to go in order to make it available to my VS?

Appreciate it very much!

Posted by: Raas Oct 7 2019, 09:57 PM

QUOTE
If you have any recent version of Access (say 2010 to 2016) installed


I only have Access 2019.

Posted by: Phil_cattivocarattere Oct 7 2019, 11:34 PM

QUOTE (Reena)
if it's something I missed in the installation
I think you need Microsoft Access Database Engine (not runtime)
Sorry but now I cannot find you any link to download it, please try by yourself or wait for another user, or read this: https://www.UtterAccess.com/forum/index.php?showtopic=2053565&view=findpost&p=2717741

Posted by: GroverParkGeorge Oct 8 2019, 08:46 AM

Thanks to Albert for clarifying.

I had forgotten that there is a problem with the way Office is now deployed. Based on feedback from the MS Access team:

Click to Run installations--such as yours--do not currently enable Ace to be used by non-Office applications, so you need to install an MSI setup (Office 2013/2016 MSI, or the 2016 Ace Redist would do).
I seem to recall that installing the https://www.microsoft.com/en-us/download/details.aspx?id=50040also does this, hence the recommendation to install a runtime. Try that.

The reason this works with mdb files even when it won’t work for accdb files is that Windows ships a version of Jet (what become Ace) that can open mdb files, but it can’t open accdb files.

Posted by: Raas Oct 8 2019, 10:01 AM

I guess I, also, don't understand. What makes my installation of Office 2019 a "click to run" installation? It's a full version, paid for, just a permanent installation, not a cloud based subscription version. Just for further clarification, I don't use Access runtime either.
Thanks for this help. If I don't have ACE installed from my Office 2019 suite, then I'll work on finding it, I guess.


Posted by: GroverParkGeorge Oct 8 2019, 10:19 AM

Click to run is the technology now used to install the applications.

It is not the "purchase" mode, i.e. subscription vs permanent license.

Posted by: Raas Oct 8 2019, 10:59 AM

Thanks for that clarification.

I have located and installed the Microsoft Access ACE engine. I downloaded the 2016 version, but it installs the ACE.12, so that should be ok. I can now go into VS and through the Advanced button I can see two providers, the JET, and the ACE. I select the ACE and get a message: The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine.
I have researched quite a bit on the internet about this issue and have tried a few of the suggestions that others have said worked, but I still cannot. I installed the 64 bit version of ACE, as that's what my .accdb databases are created as.

Is it possible that I have to remove this provider and download and install the 32 bit version instead? I'm a little nervous about doing that with 64 bit databases.

Posted by: Raas Oct 10 2019, 01:18 PM

I'm posting my current findings to see if that can get MS to do something. I understand that many in the MS MVP community might be able to pool their collective energy and put the word to MS that there needs to be a solution to this dilemma.

Below are two snippets from an official Microsoft inquiry, posted by MS MVPs. There have been hundreds of "I have the same problem" reports on this issue, but my request just got "triaged" and then blocked since "not enough interest, too severe a problem to take care of".

----------------
Microsoft.ACE.OLEDB.12.0 is the latest version. What is the error you are getting when attempting to open the database? Below is a link to connection string examples:

https://www.connectionstrings.com/access/ (My note: This link is so old that it only gives providers for Access 2007, Access 2013, and possibly 2016, and for mdb files, But not 2019).

Also, keep in mind that there are both 32-bit and 64-bit versions of the provider, so you need to make sure that your app is running 32-bit for the 32-bit version or 64-bit for the 64-bit version.

...~~~~ Microsoft MVP (Visual Basic)

(My Note: All that tells me is that 32 bit won't work with 64 bit. The only "flavor" of Visual Studio is 32 bit. There isn't a 64 bit version. So I'm supposed to uninstall all of my 64 bit Office Suite, then install the 32 bit versions? I already know that 32 bit versions won't run 64 bit applications.)
-------------------
Hi Tom,

I keep getting the message:

Windows Installer and Click-to-Run editions of Office programs don't get along for this version, so you can only have one type installed at a time. Please try installing the Click-to-run edition of Office instead, or uninstall your other Click-to-Run based Office programs and try this installation again"

------------------------------------------

If the ACE provider isn't compatible with VS, VB, or Access, then MS needs to do something other than just sell the product and then ignore that they don't talk to each other properly.

PLEASE, MS, PLEASE help us with a GOOD solution.

If anyone can help, please. I've spent hours researching. I've downloaded and installed the 64 bit version of ACE. Doesn't work, as explained above. So, I uninstalled that one and downloaded and installed the 32 bit version. Still nothing. So, I went to another computer I have available and did a complete clean install of Windows 10, did all the updates (that takes time), Installed my Office 2019 and Visual Studio 2019. Then I tried the connection wizard through VS and got my original message of unknown type. So, I then installed the 32 bit version of ACE and got the "not registered" message. I then installed the ACE 64 bit (after uninstalling the 32), and got the "not registered" message again. I have absolutely nothing else on this "new" computer, except for a web browser. All firewalls are down, and no anti-virus software is installed, let alone running.

I even, finally, created a small Access database from the new installation. Same message.

I know it means nothing to most, but without this working, I won't be either, come January.




Posted by: Raas Oct 10 2019, 01:32 PM

I decided to post the exact complete message I get when trying to install the 32 bit ACE database engine 2016 provider in my "new" installation. I think it shows that MS has made a gigantic error by not having a 64 bit version of VS.

---------------
Heading: Microsoft Access database engine 2016 (English) Setup

Message:
You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2016, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016:
Office 16 Click-to-Run Extensibility Component.

Then another message will come up: Installation ended prematurely because of an error.

------------

Well, I don't have Office 16 on my computer. I can't run 32 bit and 64 bit MS products concurrently.


Posted by: Phil_cattivocarattere Oct 10 2019, 03:13 PM

QUOTE (Raas)
Message:
You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2016, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016:
Office 16 Click-to-Run Extensibility Component.

Did you read the link I suggested in my #15? You will find how I solved it.


Posted by: Raas Oct 10 2019, 05:18 PM

I did, and tried that. Same error.
All I get when trying to install is a blank command prompt screen for about 2 to 3 seconds, and then revert to the original screen.

I'll go to another computer when I get home from work and try again with that one.

Thanks for trying again!

Posted by: AlbertKallal Oct 11 2019, 03:17 AM

Ok, let’s clear this up.

As noted, the 2019 CTR simply does not install + expose the ACE data engine for other external programs. While not important, the “idea” here is MS is starting to compartmentalise their programs. (they are becoming app-v packages) The idea is that install say Access 2019 will not affect other programs and “play nice”. In effect Access (and most of office) is marching towards the day when you don’t even have to install the program – you just run an .exe. In effect, this has been a dream of the access community for 20+ years.

So, CTR technology is not 100% “there” yet in terms of stand alone running of office programs. (and it not that the technology is lacking, but they are “moving” in this direction).

So MS installed stubs to “talk” to these CTR versions of office.

Those stubs are “outside” of the CTR system. So 2010, 2013, 2016 all installed this “stubs” to help “regular” folks use ACE and Access via “COM” interaces. But we are fast moving away from that model.

The future means you will NOT install software anymore!

So, what this means?

We WILL see the day when we don’t actually have to install Access anymore!!! (it will be a simple .exe file and a single file at that!!!). And when this occurs, then you be able to run multiple editions of Access, and all without having to install them!!!

So for 2016, and 2013 (CTR) they “did” register a set of dll’s to talk “inside” of the virtual applation. However, this is a messy affair, and was ONLY a stop gap approach on Micrsoofts part.

And wonders of wonders? It looks like for 2019, they don’t do this anymore (they avoid this mess).

Welcom to the future!

As for the bit size issue? Well, we had x32 and x64 verisons of Access since 2010. Again, you can’t mix the SAME version.

Now, I could make a “long” post here I am so famous for, and explain HOW 2010, 2013 and 2016 “CTR” actually ALSO allowed external programs (like VB6, vb.net) to use the ACE engine.

With 2019, it quite clear that not the case (but I been expecting this for 10 years!).

So, in your case?

Install the ACE re-dist package from here:

https://www.microsoft.com/en-in/download/details.aspx?id=13255


And make sure you install the x64 version!!!

And, this means you BETTER force your .net project to x64 bits!

You see, for 30+ years, “JET” shipped with windows and for that 30 years, it been x32 bits. Yet you want to run x64 bits!

So, installing the re-dist for ACE is your best bet.

And do keep in mind that you have to force your .net project to x64 bits.

And I suppose, if you have x32 2010 (runtime or otherwise) installed, then un-isntall it, and go with the ACE re-dist.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: Raas Oct 11 2019, 10:20 AM

Thank you, Albert!

That sheds a lot of light on why VS 2019 and Office 2019 don't talk to each other nicely. At least I know I'm not completely crazy now.

It does leave one unanswered question though: You said to make sure I FORCED my applications/projects to be 64 bit. I do all of my Access databases as 64 bit. I don't know of any way to force a VS project to 64 bit unless the connection to the 64 bit database provider does so. What am I possibly missing, or am I even missing anything now?

Thanks again. Your lengthy explanations are truly appreciated.


Posted by: AlbertKallal Oct 11 2019, 01:33 PM

Forcing the bit size of your .net projects?

Well, if you ever built say a “COM” object to extend Access (use + call vb.net code from Access), you will fast realize that such code you call + use + consume HAS to be the same bit size as Access you are running.

So, you can’t for example in Access x32 use:

Set AutoCad = CreateObject("AutoCad.UI")

The above will fail because you using Access x32, but AutoCad is x64 bits. Now of course since the “dawn” of the personal computer age, we had to deal with the change from 8 bits to 16 bits, to 16 bits to x32 bits to x64 bits.

Now it is possible you are somewhat new to computers in general or say not aware we had an x64 and x32 bit versions of Access for 10 years now.

But when it comes to using the say the common dialogs “tree view” control, or the Access ActiveX calendar control? Well for 10 years now, we seen truckloads of questions + answers on UA.

And of course your API calls to windows often have to be changed or upgraded to with x64 bits.

And the answer is since no x64 bit tree view or x64 ActiveX calendar drop in exists, then if you jump to x64 access, you can’t use that tree view control anymore. And the same goes for any other add-ins such as PDF viewers etc.

Since we “all know” and realize that JET is x32 bits, then obviously if we going to use JET from VB6, FoxPro, vb.net, c++ (and insert EVERY developer tool on the planet here!!), then of course that development tool MUST and WILL have to match the bit size of the program in question (JET x32).

As noted, this basic concept goes all the way back to the first time one hit the power on button on an Apple II, or a brand new cool surface pro.

So, when creating a project, in “most” development platforms you tend to “just use” the product, and you quite much forced to deal with and be “stuck” with the bit size of that development platform. As noted, .net is really nice in this regards, since .net code can call + consume other .net code of any bit size, and it can deal with this issue because .net has what we call a “JIT”. (Just in time compiler). JITS are an ASTOUNDING technology change in our industry. The result is you can write in HTML + JavaScript for your Android phone. Google has spent so much R&D on their Android JIT that such code is not only platform neutral, but such scripting type of code actually runs as fast as raw c++ or even BETTER on your android phone! In other words, some [censored] scripting language (slow) now runs as fast as hand coded optimised c++ compiled to a native ARM CPU on your phone!

And we seeing web browsers do the same thing!! Now, I want to keep on writing about what this means for our industry (but the ramifications are so huge it not even funny – but we save this talk for another day!!!). Anyone reading this should be able to figure out what this means (and it going to change software development in such a huge way it is not even funny. If anyone wonders what this means – just ask, and I’ll explain.

Anyway, back to the issue at hand:
So, if you install Access 2010 x32, and then say Outlook 2013 x64 bits?

Well, Access then cannot “automate” or “consume” Outlook with:

Set MyOutLook = New OutLook.Application ‘ early bind
Dim MyOutLook as New Outlook.Applicaton

Dim MyOutLook as object
Set MyOutLook = CreateObject("OutLook.Application")

So in above, we would be asking an x32 bit program to interface and talk to and “create” an x32 bit “instance” of outlook, but “if” Outlook is x64 bits. That will NOT work!

This is really no different than for the past 10 years seeing stories on UA about how some company decided to use Access x64 bits. Not the end of the world, but if they were using say Tree View (ActiveX), or say the common “calendar” control (ActiveX) for Access, they would find that those external ActiveX add-ins do NOT work anymore.

Again, this is because there is not for example an x64 bit version of the Tree View control. You thus can’t use such ActiveX controls and add-ins with Access x64, because these add-ns and external controls were written as x32 bit programs. (And they are un-managed code (which means non .net, which in turn means such code can’t do that “trick” to run as x32 or x64).

So, you are simply NOT allowed to call + use + consume x64 bit software if you are running Access x32.

Now the SAME applies when using Visual Studio. However, Visual Studio allows you to “target” the bit size due to .net ability to change like those cool lizards that change color!

So, you can in Visual Studio choose x32 (it called x86) or you can choose x64 bits for the final output of your compiled project.

And you can also choose “any CPU”. This allows both x32 and x64 bit .net code to consume the SAME output/compiled .net application. How this works is VERY interesting, but suffice to say WHEN .net code calls other .net code libraries (such as any project you create in .net), you can thus “set” the bit size to “automatic” or what is called “any CPU”. And .net can do that cool flipping trick for you (that magic JIT is the key).

However, Access, (and office) is NOT what we called managed code (managed code = .net code).

So:
Managed code = .net code.

Non managed code = c++, or systems without a JIT. (Like office programs, VB6, FoxPro, Access VBA etc.)


So what about Access calling + using .net code?

Well, for Access to call + consume .net code, then you have to “register” the .net class or object (and DURING that setup and registering process you will ahead of time choose x32, or x64 bits to “register” that COM/ActiveX .net object. You then can use CreateObject () in Access VBA to call + use that .net code.
In other words, when unmanaged code (Access) called managed code (.net), then that “automatic” on the fly bit size switching that is part of .net CAN NOT occur. You must choose ahead of time!

So the “automatic” bit size flipping feature of .net can ONLY work with .net to .net calls.

So, for Access to call + use .net code, then you have to specific set up the Visual studio project as x32 or x64. (But more important actually register the .net project to run/work as x32 or x64 ahead of time).

What about the reverse?
(Which is YOUR case!)

.net calling + using “non managed” code.

Well, once again, the non managed code is going to be frozen/fixed as x32 or x64 (non managed code cannot flip bit size like .net can).

Keep in mind that this “bit size” flip in .net does NOT occurring during running. Either your .net starts as x32 (and will stay running as x32), or it will start as x64 bits, and stay during running as x64.

So, in .net, if we use JET, then we KNOW for the last 30 years of Access, JET and Access has always been x32 bits.

So not only MUST we run our .net project as x32, but if JET works then we can conclude by logical thinking that our .net project MUST be running as x32, else we could not use the JET database!! And we can then by logic conclude that our .net code MUST have started running as x32!!!

However, to use ACE? Well two versions exist. And to use x64 ACE, we BETTER force and start our .net application as x64 bits.

So, we have this dialog in VS:




Note the setting in visual studio for x86 or “any”.

But if we choose configuration manager, then we can set (force) the .net project to run and be as x64 bits. (Just use new config, and use x86 as a template choice).

The dialog when launched and you create “new” config, you get this:



So, now the .net project is set (forced) to always be x64 bits. We need to do this if we going to use ACE x64. (Because this will force .net to run as x64 bits from the start, and that means it can call access x64 (ACE).

So, in .net land, when I say to run and set your project (force it) as x64 bits, it means to use the configuration manager for .net and “choose” + “force” the issue in place of leaving this setting as to how the wind is blowing that day.

But all in all, kind in mind, around the corner, we not likely to deal in installing software in the future – it will become a thing of the past! JIT technology, and combined with app-v will result in you just “using” software – kind of how like the web works!


Regards
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: Raas Oct 12 2019, 10:04 AM

Seems to confirm that Visual Studio 2019 won't connect with Access 2019 64 bit since Visual Studio only comes as 32 bit.

Visual Basic through Visual Studio will give me programming features that VBA won't I need it, but I can see it won't happen. Of course, VBA gives me features that VB doesn't, but as you all know, I'm still very slowly and patiently learning VBA.

If Microsoft ever gives use the connection from VS to 2019 to Access 2019 64 bit, I'd love to do it and know how, but based on Albert's postings, that isn't going to happen.

Sounds like we are either going to have to be subscription based or nothing. So … nothing it will be. Time to check out what Apple and Linux have to offer. Probably not much.

Albert has been looking forward to this day for many years, but then he is a lot further advanced in knowledge than I am, so he can use the technology. I can't.

I'll just go into the University on Monday and tell them I can't teach VS 2019 in conjunction with connecting to Access 2019 64 bit. That seems to be the answer I'm getting.

Thanks to all, anyway. Thank you Albert for the lengthy discussions that led to this decision.

Posted by: Raas Oct 12 2019, 12:24 PM

Sorry for this, but I just can't give up like that.

I've followed Albert's latest post as best as I can. He goes to the configuration manager and gets a drop-down that has "Any CPU", "x86", and "Configuration Manager". I do not have that. Her is what I see instead:

"Any Cpu", and "Configuration Manager".

I go to Configuration Manger and I don't have the x64 platform to work with. All I get is "Any Cpu', and the drop-down is <new...> and <edit...>

Not sure how to get the x64 platform into my Visual Studio 2019. I have ACE 64 bit installed, and, of course, the default Jet 32 bit.

I know I must be missing something in Albert's post, but what I don't know.

--------------Now, how does Albert paste screen shots into his posts? I'd send some shots of what I have if I knew how. I use Snagit to capture my clipboard shots, but they won't paste. I've tried the MS Print Screen as well. -----------------



Posted by: GroverParkGeorge Oct 12 2019, 01:33 PM

Re: images


Posted by: GroverParkGeorge Oct 12 2019, 01:35 PM

Or you can use the upload function:


Posted by: AlbertKallal Oct 12 2019, 02:40 PM

A few things:

When I spoke of that “near” future, the context was not having to install programs on your computer, but just having to “run” them. That is a MASSIVE different context and issue than that of being able to use x64 ACE with VS – which of course is MUCH the whole reason for my post.

Of course you can!

I stated, hinted, implied, demonstrated to GREAT lengths that .net is not only able to work with x64 bit code, but has special features that makes this whole process VERY easy. And that includes ACE x64.

Why would I spend so much time explain things here and not just say this can’t work?

Just the logic of this post, and the length of this post SHOULD suggest that this setup can work! You should “assume” this can work based on just the length of the information even if you did not grasp what it means!

Anyway, no worries!

To summarize ALL of the above?

You have to get x64 ACE installed
You have to set and force your VS project to x64
(That is ALL you really need to know).


QUOTE
Not sure how to get the x64 platform into my Visual Studio 2019


Ok, I did hint and suggest how to do this.

QUOTE
Albert wrote:
Just use new config, and use x86 as a template choice).


Well, you actually not get “x86”, but you get “any CPU” (so my bad!!)

Anyway, from this dialog, choose <New>





You then get this



You can usually just hit “ok”

From above, make sure the first box is x64 (quite sure it defaults to that choice).

So, just hit “ok”.

The “copy” settings from “any cpu” is just that – it copies a existing setup/template ,and AFTER you copy that setup, you are allowed to edit that setup (but the x64 setting in the top combo is already selected for you)
.
After ok, you get this:




And now note that you now have “two” configurations!

Eg this:



(in above, I have 3 choices, since I during testing also added a x86 config – you likely only see 2). I create a “lot” of .net code that is called from access, so I often force my projects to x86.

Anyway, now that you have two choices “any CPU” and “x64”.

It should be now x64, but if the box in the main VS shows “any”, then you should be able to select and set x64 as a choice from the drop down.

I can’t remember if “setting” to x32, or x64 necessary “restricts” your possible choices when setting up a connection string (I think it does, but not sure – can’t remember).

However, once you do create the connection, and run, then it will fail if you choose any cpu, or x86.

But, if you choose and force the issue as x64, then you should be able to use ACE x64.

Good luck!

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com

Posted by: Raas Oct 12 2019, 05:01 PM

OK. I realize I don't totally understand. I did read all of Albert's posts and understood much of it. The last post was very helpful. However, here is the sequence I just went through: Attached Word Document.





 Steps.zip ( 161.46K ): 2
 

Posted by: AlbertKallal Oct 12 2019, 06:41 PM

As I stated, I don't think DURING configuration you are restricted to selecting ACE, but KEEP in mind that Visual Studio is a x32 bit program.

So, in theory based on what we all learned from my post (hopefully), then the Test connection button can't work. You have to actually run the code to test the connection.

Keep in mind that Visual Studio is NOT managed code (that's is all you need to know, the rest can be deduced by logic).

So, based on what we know?

You should be able to setup a connection, but testing such connections from a x32 bit program (say like Visual Studio) can't work.

It still possible that your ACE or some other issues exists, but based on theory (since I never attempted this), I have to assume that test connection can't work, but running some code as x64 should work.

Of course if you have both x32 and x64 Access installed, then test connection should be able to work for both x32 and x64.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: Raas Oct 12 2019, 07:32 PM

All of you: I apologize for wasting your time. Albert in particular has been extremely patient and kind in his posts and responses. I understand about 80% of what he posts. It's obvious that he has knowledge, expertise, and experience that far surpasses anything I have.
I would be happy to write the code Albert talked about, if I knew how. I don't. I don't know where to write the code. I don't know what code to write or even what the code is. I assume it's code to create a connection to the Access database. If I knew how to do that, I would have done so.

I have learned a lot from this frustrating process of getting nowhere. What I have learned most is that I don't know what I don't know, and I don't know how to learn it.

So, unless someone is willing to do a step-by-step posting of the code (and where it goes in VB) for creating a connection, then I can only assume that with VS 2019 and Access 2019 64 bit, it can't be done, and as GPG stated way back, he and I hope that MS didn't do that to us.

As Albert stated in his latest post,

QUOTE
It still possible that your ACE or some other issues exists, but based on theory (since I never attempted this), I have to assume that test connection can't work, but running some code as x64 should work.
I'm getting the impression that all of the "fixes" so far have pertained to the VS and Access that is running on your own computers, and so far no one is running VS 2019 and Access 2019 64 bit, but earlier versions of both, and as Albert pointed out in his first posts, VS 2019 has changed the game.

Again, you have all been kind to me through this process and I appreciate it, more than any of you could ever imagine.


Posted by: Raas Oct 13 2019, 07:11 PM

I guess that's it. I have researched 7 different VS texts and finally found an obscure reference to creating a connection in code. So I entered the following in the form load event. It doesn't throw an error, but it also doesn't actually make a connection.
Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;" & "Data Ssource = E:\DGR VB Solutions\64Bit\bin\Debug\LegalFees.accdb"Dim newPath As New OleDb.OleDbDataAdapter(strSQL, strPath)
Try to connect to a database and I get the same "not registered" error.
I guess that's it.


Posted by: AlbertKallal Oct 15 2019, 07:17 PM

Ok, it still not 100% sure/clear that you ever did get ACE installed, or in fact what version you installed?
However, try downloading the attached small little script file.

And run it!

When you click on accesstest.vbs it will run as x64 bits.

If it can’t create an instance of ACE, then ACE is not installed correctly.

And, if you want, you can try running the script as x32 bits.
(not required, but it is a good test!)

To run as x64, just click on the file from windows explore


To run as x32, you have to tap windows key, and type in:

%windir%\SysWOW64\cmd.exe

Now run the script
Cscript path to accesstest.vbs

Eg:

cscript c:\test3\accesstest.vbs

So, download the file. Un-zip, and run it. It will tell you if it can create an instance of ACE.

The code inside is simple:

CODE
if Instr(WScript.FullName,"SysWOW64") = 0 then
   msgbox "about to start script - this is running x64 bits -->" & WScript.FullName
else
   msgbox "about to start sript - this is running x32 bits -->" & WScript.FullName
End if
s = "C:\test3\test44.accdb"
on error resume next
Set acc = CreateObject("DAO.DBEngine.120")
if err.number = 0 then
   msgbox "create data engine 2007 (12) is good"
else
   msgbox "Ace 12 NOT FOUND!"
end if
msgbox "done CHECK"


And you could just cut + paste the above into notepad. And then rename the extension from .txt to .vbs and run that (that’s how I created this vbs script).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



 accesstest.zip ( 395bytes ): 3
 

Posted by: Raas Oct 24 2019, 08:07 PM

Took me a while. I've had to attend meaningless conventions and just got back to look at this latest post last night.
I ran the script. The messages I get are as follows:
"create data engine 2007 (12) is good"
Then after clicking OK:
"done CHECK".
So, I'm assuming I have ACE for 64 bit installed.

Posted by: Raas Oct 24 2019, 09:52 PM

I just noticed that I forgot to let you know that the script said it was running 64 bit.I wouldn't think it would make any difference, but FYI, I run ADO, not DAO.

Posted by: dmhzx Oct 25 2019, 02:20 AM

Off topic slightly:

I have also posted a question on that Forum, and have had some response . - All of it useless.


Posted by: AlbertKallal Oct 25 2019, 06:55 PM

Excellent.

All that script does is create a instance of the ACE (and DAO) object.

If the script could not work, then ADO is out of the question also!!!

So, we may not be 100% across this bridge, but it is VERY likely that if ACE x64 bits is working as that script suggests then we have a VERY good chance that we can now attempt to build a connection sting in the “settings” part of say a test application. So, all we know that ACE x64 is installed. It should means that ado should also work. So, we basic just determined if the lamp has electricity here and can now try a light bulb!


As noted, while in the project settings, you can use the connection string builder.

It should be able to build you a working connection,
BUT AS NOTED IN ABOVE POSTS YOU CANNOT USE THE TEST connection option in Visual Studio.

The above is due to VS being x32 bits while you develop software, but the compiled code can "build" and "compile" to x64 bits (which is YOUR case!!!!).

So the Editor and builder and connection string builder in VS is all x32 bits.


So, VS can’t test the connection, but it can build one for you.
You ONLY will be able to test if the connection works by running your code.

Don’t forget when building the connection string to change from JET to Ace.

Some code like this placed behind a button on a form would be a nice test:

CODE
        Dim rstData As New DataTable

        Using MyCon As New OleDb.OleDbConnection(My.Settings.AceTest)
            Dim daRead As New OleDb.OleDbDataAdapter("select * from tblHotels", MyCon)
            daRead.Fill(rstData)
            With rstData
                If rstData.Rows.Count = 0 Then
                    MsgBox("No data found")
                Else
                    MsgBox("Rows of data found = " & .Rows.Count & vbCrLf &
                       "First row of data: HotelName = " & .Rows(0).Item("HotelName") & vbCrLf &
                       "Pk ID (first row) = " & .Rows(0).Item("ID"))
                End If
            End With

        End Using


I will also point out that the connection builder for the above created this:
CODE
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test3\test44.accdb


So, your connection string should also look quite much the same (take a quick look at what the resulting connection looks like). would also test open the database if you have Access intalled,, look at a given table, and if all is well, then exit access and try your VS project.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: Raas Oct 28 2019, 07:05 PM

QUOTE
Using MyCon As New OleDb.OleDbConnection(My.Settings.AceTest)


When I reproduce the code given above, I get the error: "'AceTest' is not a member of MySettings". Error Code is BC30456


Posted by: Raas Oct 28 2019, 07:14 PM

I just went through and verified that I had entered all of Albert's code correctly. I did. The error message shows "MySettings", but the code was entered properly as my.settings. I tried taking the 'dot' out of the code, but get a different error that way, so I have returned it to the original my.settings


Posted by: AlbertKallal Oct 29 2019, 02:30 AM

Well, of course just like choosing variable names, it’s up to you to choose whatever you like for YOUR setting name.
I mean, for a name of something, I might use:

Me.FirstName

And you might use

Me.CustomerFirstName


One could just hard code the connection string, but I used the app settings and the connection string builder. I mean it just makes sense to put the connection string in “one” place as opposed to re-writing over and over in your code. And the bonus feature of using the app-settings is:
It is built into visual studio. (Always wish we had something like this in Access).

It has several kinds of “builders”.

So it has a connection string builder.

So it has a color builder. (so you could make some custom colors for your forms, and thus be able to “change” the color in one place, and all your forms would use that color).

So it has a font builder - all kinds of neat-o builder.

So, all kinds of “general” purpose settings can be placed into the application settings area.

So for example, you might want an application wide setting such as “Company” name that you display on all forms. Again, using the application settings is a good idea and tip (certainly not something you must do – but just a good idea).

However, if you read close my last post, I did give you the connection setting I used. And again, like a file name, table name etc? Well of course these things are really up to your choice. No more or less then whatever flavor of ice cream floats your boat. I can’t possible read your mind as to what names you choose for things.

To launch the VS “over all” application settings to allow you to make a connection setting (like AceTest, or AceZooZoo)

So after opening your project, then from the main menu, select project, and then usually near last option is properties.

This one:

And of course the “name” of your project will be whatever floats your boat (whatever name you used will appear – in MY CASE I just happened to use AceTest).

And then I choose the settings tab.

This one:



As you can see in above, I created two connection string settings.
One is called AceTest, and the other is AceZooZoo

I also added a string setting called CompanyName.

In fact, you find as a general rule, most settings really are just strings. There is NOTHING special about these values. Keep in mind that AFTER using the builder (see my comments later in this post), then when the builder closes, it just shoves the resulting string into the above text box. So the builder just "builds" you the setting, but you could just type in such settings without using the builder (but who has that kind of memory to remember the exact format).

So, in my code, I can now grab/use that setting in place of having to directly type in connection strings in code (yuk – if not double yuk!!!).

So, in my code, I could now use:

My.Settings.AceTest

Or

My.Settings.AceZooZoo

All the above does is grab the string value you have in the settings area above.

So, in code to display my company name setting, I could use:

CODE
MsgBox(My.Settings.CompanyName)


So, for example, my connection object line of code could be:

CODE
Using MyCon As New OleDb.OleDbConnection(My.Settings.AceTest)

Or perhaps we use the second one (since they ARE the same in this example)

Using MyCon As New OleDb.OleDbConnection(My.Settings.AceZooZoo)

Or perhaps we decide to suffer, and make for a long day, and use this:

Using MyCon As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;DataSource=C:\test3\test44.accdb”)


Note how messy that last example suggestion was.

So you can “manually” type in the connection string right in your code. But as above shows, it certainly a lot less effort (and less ugly) to use the application wide “settings” feature in VS, and it also gives you a connection string builder to boot.

To launch the “builder”, note the [...] button that appears when you click or move your cursor into the “Value” column.

This one:


That “builder” is really much the same feature you see in access to launch extra options. So to launch the “builder” (in this case a connection string builder), then click on that [...] thingy/button that appears (the hand is pointing at it in above). As noted, Access also has this idea in lots of places. so that builder button ONLY appears if you click into that text box.

Make sure you select the combo box(s) before you try to use the builder.

So, after you type in say AceZooZoo, and select connection (first combo box), and then select Scope combo (Application), then now you move into the Value area box. You can click on the [...] builder button. But you can ALSO just type in the string also without using the builder. I certainly recommend playing with these settings.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: Raas Oct 29 2019, 01:14 PM

Albert, you are a very patient man!

That last post cleared up a lot. I actually understood all of it, and it removed the latest error (as you knew it would).

However. It seems there's always a 'however' with this project: I now run the test and I get the same old error of "System.InvalidOperationException" 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

Yet, we have tested, and it's installed. It still shows up.

I'm curious. Which version of VS are you running that allows you to get no errors?

Also, I note that your system shows, next to 'Debug', that it's x86. Mine only shows "Any CPU". I'm running x64., but Any should mean ANY.

I have even created a project that has only one form, one button, and only code for that button. Of course, the code is that which you have so graciously supplied me in steps. There is nothing else in the project. I even named it AceTest. My Access database is 'LegalFees'. The table I use is 'tblLegalFee'. I'm only testing for two fields, as did you. They are Payee , and TransCount .

Posted by: AlbertKallal Oct 29 2019, 10:05 PM

No, ANY does not really mean "any" the way you are thinking here.

It means that the program that "launches" YOUR program can be ANY, but we don't want that.

ANY does NOT mean that everything you have can be ignored and that ANY can work with ANYthing.

As noted, I usually "force" the project to x86, or x64.

You can't (and don't want) to use "any", since then you NEVER be sure what bit version of the project is going to be running as.

So, in your case, you want to be sure that your project is set to x64 bits.

ANY does mean "any", but that often means for the most part (and especially when launching + using visual studio, ANY will tend to choose out of the blue x64 or x32 bits, and you do NOT want this.
In fact, because you are launching your program from VS, then ANY will tend to result in your application running as x32.

So, if you using x64 ACE, then you can't leave this choice to ANY, since you can't be sure what bit version your software will launch as. the "ANY" choice is great say if other .net programs might launch YOUR program. "any" means just about anything, and that's not what we want.

ANY does not mean you don't care and don't have to worry about the setting. ANY really means that your program is likly to launch as "ANY" version it pleases, and if it launches as x32, then your code will not work.

So, in your case, we really can't use "any" as a choice. You have to force the choice here. If Access/ACE was a .net program, then yes, ANY would be fine. But Access is 30+ years old, and it not a .net program, so it can't know or use "ANY". So, we KNOW that you using access x64, so we better make sure we force the bit size of your .net program to also and ALWAYS run as x64.

It is not 100% clear if you testing + trying this with your project set to "any", but you MUST set and force you project to x64 bits.

The series of previous screen shots shows and goes about how you can create a x64 bit config. And you MUST run your application as x64, as ANY will not work in all cases, and in fact when running from Visual studio, the choice of "any" will not work at all.

So "any" don't mean it will work with "anything", but means you don't care what bit size the programs runs as. As noted, in most cases you don't care, but in this case we have to force how your application runs, since if we don't, when it attempts to use ACE, if ace don't match, it will not work

Try your application as x64 bits - review the previous post here where the steps were outlined to force and setup your application to ALWAYS runs as x64 bits.

ANY cpu setting simply cannot be used here and will not work.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



Posted by: FrankRuperto Oct 29 2019, 10:23 PM

I always thought that if "Any CPU" is selected, the JustInTime compiler automatically detects and sets the bitness and the program execs, including x86 and x64 Access COM objects.

Posted by: AlbertKallal Oct 30 2019, 09:16 PM

You are much correct.

But the problem is ONCE the program starts "one way" as x32 or x64, then it remains as such!

and during the development process, Visual Studio (VS) is a x32 bit program. So if you debug, develop, and test with any, then you going to get the resulting program running as x32.

So, ANY most certainly can "run" as x32, or x64, but it cannot change it mind ONCE it starts.

So, if you start a .net program as x32, then it can use other .net programs, or even libraries you referenced (and if they are set to any), then your main program, and any thing else you use can "switch" and run as the same bit size. As noted the term "switch" here is a really bad choice of words on my part. .net does not "switch" bit size, but is able to START as a given bit size and continue to run that way.

the above is all fine and dandy if you just using and running .net code (what we call managed code)

However, we are using ACE x64, and thus we simply have to prevent .net from starting as x32, and using the default "any" during debugging will in fact cause .net to start as x32.

However, if you choose x64, then even during testing and launching and starting from VS, you will be sure that you get a x64 bit running process.

So the "big" issue and problem here is that choosing ANY during the development process will usually result in a x32 bit running instance of your application. Of course choosing "any" will allow it to be called from x32 or x64 bit programs, but the key concpet is HOW the process starts. Once the .net starts running one way, it remains that way.

Bottom line:
DURING the development process, if you want a x64 bit version of the application to be used, you can't use ANY. You have to force this setting.

As noted, for most people, it really often don't matter because that .net code will run either way. However, when you introduce non .net code systems (such as Access/JET/ACE), then you can't leave this choice to the wind.

If Access was calling + using the .net code, then you can most certainly use "ANY" for the .net code. However, if the code is a COM object, then you have to register the COM object as both x32 and as x64 (you need TWO regasm.exe commands. I have tested this concept, and it does work). So you can setup and create .net code that can be consumed by access x32, or access x64.

However, if you going to launch .net first (as we are in this case), then we MUST make sure it starts as x64, else attempting to use ACE x64 will fail.

So in theory and practice, you are correct - .net code with ANY can run and on the fly start as x32, or x64 - but once it started one way, it can't flip to the other.

From VS, during testing and debugging, it will start as x32 if we choose x32, and it will also tend to start as x32 if we choose ANY. So, we have to force the issue and choose x64 bits.

Now, I suppose one could force start the application as x64 EVEN if the setting was ANY, and the way you would do this is to ensure that you launch the windows x64 bit command prompt and then launch the .net program. if we launch the x32 command line prompt, and then start the same progam, then it will run as x32.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada






Posted by: Raas Nov 4 2019, 06:43 PM

Well …

I just spent four days going over all of these posts, digesting what I could. I broke them down into a step-by-step process, based on the posts by, mainly, Albert Kallal. I followed the instructions exactly, and I can't get an x86 template in the configuration manager. Without that, it would be impossible to create an x64 solution platform.

I've run the scripts, run everything presented. I can verify that ACE 64 bit is indeed, installed.

I even got desperate and went to Costco and bought a new computer. Installed Windows 10 pro, did all of the updates (that took time), then installed my Office 2019 (not subscription based), then installed Visual Studio 2019 and all of the components it allowed me to install. Then I started VS and a new project for a Windows Form. The first thing I did was to try to make a data connection (after doing all of the step-by-step instructions). I get the same error of "System.InvalidOperationException" 'The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine.'

That's where we started with this situation.

I believe that Microsoft has a real problem that they haven't even tried to address yet.

If I'm doing something wrong, please don't just "point" me in a direction anymore. I've had weeks of that. This is one time I'm asking for exact complete information. Can someone please just write out the step-by-step instructions that will take care of the situation? I would be so grateful!

I've learned so much from Albert's posts, but now I can go no further, I'm afraid, without the full in-order instructions.

Thank you all!

Posted by: FrankRuperto Nov 5 2019, 01:00 AM

QUOTE
I even got desperate and went to Costco and bought a new computer...


wow don't despair lol, albert please rescue

QUOTE
The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine.'


Visual Studio is only available as a 32-bit application. If you connect to Access from within Visual Studio, you must have a 32-bit version of ACE installed, it wont work with 64-bit ACE, period.
64-bit processes cannot load 32-bit DLLs, and vice versa. When a call is made to the ACE provider, the 32 bit process will attempt to locate a 32-bit DLL. If ACE is 64 bit, its DLLs are 64 bit, thus you get the above error.

My two cents


Posted by: Raas Nov 5 2019, 11:32 AM

I have followed Albert's information to the letter, at least I think I have. I downloaded the 64 bit version of ACE. I tried to "force" VS to use an x64 bit configuration, piggybacking off from the x86 configuration, but VS 2019 isn't showing me an x86 template as is shown in Albert's information. So, I'm lost and running out of time.
I have done a lot of research and finally found some information (just this morning), from Microsoft that says that Office 2019 is distributed ONLY as a click-to-run installation and that MSI installation options are only available on servers, which, of course, I don't have and couldn't afford anyway.
Since I can only have a "permanent" version of Office 2019 that is still just a click-to-run version, and since VS 2019 is installed as an MSI installation, I'm being led to the conclusion that, since MSI and C2R won't communicate with each other, that Microsoft has screwed up and needs to correct this problem. VS 2019 is not worth anything as it pertains to communicating with their own Office 2019.
If I'm wrong, I'd be glad to have someone say so and show me how to get around this dilemma.

Posted by: AlbertKallal Nov 5 2019, 07:39 PM

QUOTE
Visual Studio is only available as a 32-bit application. If you connect to Access from within Visual Studio, you must have a 32-bit version of ACE installed, it wont work with 64-bit ACE, period.


Well, sort of correct. The key conception is WITHIN VS. If you use the "test" connection during setting up of a connection to ACE WHILE in VS, then yes, it has to be x32 ACE. However, if I have only ACE x64, then during the setup, the test connection part during the setup of a connection will fail, but if I run (or debug) the application, it will run as x64 bits. So all you have to do is setup the connection, but SKIP hitting or using the test connection button. That you have/get during most connection setup dialogs and wizards in VS.

Again:
If you force the project to x64 bits and NOT use any, then you WILL get a x64 bit running instance of your application, and you can use ACE x64 as a result. I can double and triple check if you have to run as "release" as compared to debug mode - but this is not my understanding at this point in time.

Read my comments careful. if you use x86, or ANY - your .net program will start as x32 (because VS is x32)
However, if you force the issue and specify x64 bits in the config, then your application is launched as a x64 in-process application, and this will allow you to use ace x64.

I'm gong to double check the above. It is "possible" that you have to choose "release" as opposed to debug to force this issue, but this is not my understanding at this point in time.
R
Albert

Posted by: AlbertKallal Nov 5 2019, 11:35 PM

QUOTE
and I can't get an x86 template in the configuration manager.


Over and over like a broken record, we MUST set this project to x64 bits. This whole discussion has centered on this issue.

The set of screen shots went step by step for this process in a previous post here.

Look at those steps again.

You should see this for the project settings:




If you don’t see or have the above, then this will not work. It is as simple as that.

I have attached a sample vb.net project. Unzip it to some folder.

Then from VS, go file->open project, and browse to the folder where you un-zipped the attached zip file.

You will find a ".sln" file to open.

You can then double click on the button in the sample form to open up the vb code editor.

You have to modify the code behind the button.

So change c:\VS\test44BE.accb to a known database on your computer.

And change the “table1” to a KNOWN table in that Access database.

At that point you can hit f5 to run. It should display the number of rows in that given table.

As noted, VERY much of the posts here have centered on the fact that if you fail to set your project to x64 bits, it simply not going to work.

Try the sample project I have attached. Modify the code in the sample for a known database and a known table in that database. The sample should just work fine.

Edit:
The code behind the button is this:
CODE
        Dim strCon As String

        strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\VS\test44BE.accdb"

        Dim strSQL As String = "select * from table1"

        Dim MyCon As New OleDb.OleDbConnection(strCon)
        Dim DataRead As New OleDb.OleDbDataAdapter(strSQL, MyCon)

        Dim rstRecords As New DataTable

        DataRead.Fill(rstRecords)

        MsgBox("reocds in table = " & rstRecords.Rows.Count.ToString)


So in above, you need to change the database name/path in above (c:\VS\test44BE.accdb) to a known database on your computer.
and you need to also change the "table1" in above to a known table.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

 WindowsApp2.zip ( 231.57K ): 1
 

Posted by: dmhzx Nov 6 2019, 10:41 AM

A quick note to Albert:
Just to say how much I've learnt for all your posts here.
I'm currently attempting to get to grips with VB .Net after years of VBA experience, and you explanations of the 'bits' have been very interesting.

Thank you - You've been much more helpful than Microsoft's official .Net support, who after many attempts have finally admitted that the VB .Net folder browsing dialog does not work properly, but the C# one does. (Not expecting an answer on that new question, just using it as an example)

Posted by: AlbertKallal Nov 6 2019, 10:42 AM

QUOTE
I'm gong to double check the above. It is "possible" that you have to choose "release" as opposed to debug to force this issue, but this is not my understanding at this point in time.


I have checked this. So, no problem, both release and debug work, and if the project is set to x64 bits, then your project runs as x64. I spooled up a vm on my laptop. Installed VS and ACE x64 redist. And again, if I run as x32, or as ANY, it fails exactly as i predicted. And if I run and start as x64 bits then it works fine.

So, everything I stated, claimed, hinted, suggested, implied and flat out claimed in this thread is a 100% correct. It makes sense, since I have several .net projects in which I am using x64 bit versions of code. One being the .net Ghost script library. I am running and using asp.net + vb.net. The web server runs as x64 bits, and if my project is set to x32 bits, it fails.

So, all is well, all is EXACT as I stated here.

The SIMPLE bottom line (and what I stated and suggested here OVER AND OVER)

Set your project as ACE x64 bits to use ACE x64.

R
Albert

Posted by: AlbertKallal Nov 7 2019, 03:10 PM

QUOTE
after many attempts have finally admitted that the VB .Net folder browsing dialog does not work properly, but the C# one does.


Well, I am not sure what you “mean” by not working?

We should try and be fair about “some” issue here. I mean, I do a lot of .net work, and when Access comes up, it often it gets a bad rap as to how supposed “bad” Access is, or how it is some “toy”.

Access is not so bad and I often have to defend it in public. Many simply choose to un-fairly say bad things about Access, and that tends to be just people making un-fair and un-warranted judgement s about something they know little about.

No big deal if something don’t work for you, but it is a VERY DIFFERENT matter to make a general public claim about some feature or part of .net that is considered to be broken.
So in regards to say x64 bit support in .net (and use with ACE), or that of folder or file browsing in .net? We owe the public a basic and fair shake on these issues, and it’s only fair we bear fair witness and testimony on these issues, else we do the Access (or .net) community a GREAT disservice here.

I attached a folder browser, and file browser example as a project in .net.

It has a form with two buttons like this:



The top one does a folder browse.

It uses this code:

CODE
        Using MyFolderGet As New FolderBrowserDialog
            If MyFolderGet.ShowDialog = DialogResult.OK Then
                Me.TextBox1.Text = MyFolderGet.SelectedPath
            End If
        End Using



The second button does a file browse.

And code to select a file is:

CODE
      Using MyFileGet As New OpenFileDialog

            If MyFileGet.ShowDialog() = DialogResult.OK Then
                Me.TextBox2.Text = MyFileGet.FileName
            End If

     End Using


So it’s about 2-3 lines of code to folder browse or about 2-3 lines of code to file browse. .net.

I would in fact say .net is rather good in this area, and the above shows how amazing EASY this is to do in .net. It not much code at all.

Keep in mind that c# and vb.net SHARE the system CLR (common language run-time system). At this point, BOTH in fact use the SAME compiler!

So c# and vb.net really work and feed from the same set of code systems. You can in fact take a c# project and include or reference that project from vb.net and use that c# code. And the reverse is also possible!

In other words, if something don’t work in c#, then it unlikely to work in vb.net. And the reverse is true.

So speed of code execution, the amount of memory used, and how c# works or vb.net works is really the same. And this is true in terms of features between the two systems. (A very few tiny insignificant differences in feature sets exits exist between c# and vb.net. They are as close to each other as you can get in terms of "how" they will work for two different languages (because they share the same runtimes, and same common language engines).

You can try the sample zip project attached. Just un-zip to a file and then from VS choose open project, and open the .sln file in the un-zipped folder. Now just hit F5 to run, and you can see the folder, and file browsing code in action. They both work rather well.

So, “some” issue might exist in c# vs vb.net, or the reverse, but I not aware of any issues in regards to file or folder browsing.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



 FileBrowse.zip ( 951.31K ): 1
 

Posted by: FrankRuperto Nov 7 2019, 03:56 PM

@dmhzx

The following vb.net code, developed by Albert, is a console project (i.e. formless) for copying files from a source folder to a destination folder.
Although there's no forms involved in this project, it works fine and is also being consumed by Access via COM interop.


CODE
Imports Microsoft.VisualBasic.FileIO
Imports System.Environment
Module Module1

    Public Sub Main()

        If GetCommandLineArgs().Count <> 3 Then
            Console.WriteLine("Missing from or to folder or too many values")
            Exit Sub
        End If

        Dim strSourceFolder As String = GetCommandLineArgs(1)
        Dim strDestinationFolder As String = GetCommandLineArgs(2)

        My.Computer.FileSystem.CopyDirectory(strSourceFolder, strDestinationFolder, UIOption.AllDialogs)

    End Sub

End Module



The Access VBA code:

CODE
Sub MyCopy()

   Dim F As New MyFolderCopy.clsFolder
  
   Dim bOk  As Boolean
      
   bOk = F.Copy("S:\Databases\Pawnshop\Data", "F:\")
      
End Sub

Posted by: FrankRuperto Nov 7 2019, 11:39 PM

@Raas

The vb.net FolderCopy project in my previous post was compiled into an x86 exe because it is being consumed by an x86 Access 2010 msi app. I have intentionally avoided using x64 and ClickToRun versions of Office for obvoius reasons. Are you required to use x64 Office? Unfortunately MS deprecated msi in Office 2019 and I'm afraid x86 is also going to disappear in the next Office version , while the rest of the components like VS and 3rd party addons continue to be x86.

Posted by: AlbertKallal Nov 8 2019, 02:20 AM

QUOTE
This is one time I'm asking for exact complete information. Can someone please just write out the step-by-step instructions that will take care of the situation? I would be so grateful!


I can do one better.

Simply download the accesstest zip folder attached to this post. It has two .exe files

AccessTest32.exe to try access as x32 bits
AccessTest64.exe to try access as x64 bits.

What is REALLY cool here? Just unizip the above, and click on either one of the above .exe files.
They are SUPER small!!! - no install, no nothing, no coding, no guessing!!!

Just click on them to run.

They are only 20kb in size (yes, you read that correct!!!). (that's smaller then this web page!!!!).
In fact, the stand alone .exe files are LESS in size then the picture I have in this post!

The reason of course is they are .net .exe files. So, everything they use is in the .net run times.

When you click on this .exe file, you get this:



So, if you want to test for Access x32 bits, then try AccessTest32.exe. And if you want to try for x64 bits, then try AccessTest64.exe

ALL you have to do is unzip the attached zip file to a folder. And then click on either one. Really as easy as can be.

The dialog simply lets you browse to (file select) a accdb file. Then hit the open database file. Now, all of the tables should appear in the combo box.

If the table has data, then the grid area in above will display the data.

So, in effect it is a simple access database browser. You select database file. Then hit Open database button. The combo box should fill with the tables from that database.

So, select a database table in the combo box, and the grid will display that table!!!


If neither work, then simply install the ACE data engine found here:

https://www.microsoft.com/en-us/download/details.aspx?id=54920


So, even if you are running a CTR (office 365) version, installing the above ACE data engine should mean you are up and running, and off to the races.

You are given two choices when you download the ACE engine, x64, or x86 - make sure you choose the right one.

Enjoy - and good luck!
R
Albert

 AccessTest.zip ( 16.88K ): 3
 

Posted by: isladogs Nov 8 2019, 05:18 PM

Albert
I just replied then deleted my response by mistake. Oops!
Trying again...

I've not followed this lengthy thread in detail but downloaded your zip file from the previous reply.
The utility is indeed very impressive, especially considering its tiny file size.

I tested it on a number of Access apps and it worked perfectly except for password protected files and those with specific start-up conditions such as open exclusive.

I created something similar in Access partly in response to a question by UA member payfast8898. See http://www.mendipdatasystems.co.UK/list-external-tables/4594461752
In that example I was focusing on the table properties rather than the contents. However I've also done that for my own use

I hope I'm not being too presumptuous but if you are willing to do any/all of the following your utility would be even better ...in fact excellent
1. Add ACCDE files to the browse window
2. Allow users to enter a password where needed
3. Ditto for other command line switches such as /excl
4. Change the tables combo to a listbox
5. Add table record count to the form

TIA if you are willing to do those changes

Posted by: AlbertKallal Nov 9 2019, 01:32 AM

QUOTE
The utility is indeed very impressive, especially considering its tiny file size.


It really is stunning - if there is one really cool thing about .net, it this stupid crazy small file and a stand alone .exe to boot!

You can place this on a USB jump drive and NOT even install it. You can even open and launch this from the USB jump drive. ZERO install!

A rather handy dandy tool, and no access need be installed for it to work! (but for accDB, then at least ACE need be installed).
I'm going to look into seeing what possible ideas would allow me to include ACE for this.

QUOTE
I hope I'm not being too presumptuous but if you are willing to do any/all of the following your utility


Great ideas - very little work to add. Done!!!!

REALLY like your suggest for a list box.

Now you can just tap (one click) on a table – and it displays.

I also anchored the list box and grid display. So now if you have a large screen, and max (or re-size) the form, then the table list and grid “expand” to full screen size. (so nice on small or large monitors).

I also added the choice to use JET or ACE. Of course if you run the x64 version, then JET will never work. But there not “two” versions of this code. I only compile the ONE program two times.

Here is what the screen now looks like:



Last but not least? Well, it still only 23kb. Still laughing my pants on this size issue!

It is a really nice way to browse and view access files. And for browsing Access files on say “run time only” machines, or even machines without Access it is ideal.
(they will have JET). And hopefully ACE - but if not, the install of ACE is certainly MUCH smaller then installing Access run time. I going to put ACE (installer) on my jump drive too!

I still have both a x32 and x64 bit version included. It “is” possible to only have one version that works for both bit sizes, but the “steps” to have a user launch correctly is likely too much of a hassle.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com



 AccessLook.zip ( 20.51K ): 3
 

Posted by: isladogs Nov 9 2019, 05:26 AM

Many thanks for making those changes. Its even better now. Still only 23 KB. Amazing

I’ve tested the 32-bit version on 2 PCs – both with A2010 & one with A365 also (that's the 2 downloads listed so far!)
Not yet tested the 64-bit version but I’d expect it to behave in exactly the same way

A few suggestions to further improve the user experience for you to consider:
1. In the browse window file type selector only show the All Access files option
OR move that to the top of the list and remember the last selected option for next time
2. The list shows local tables only – can you add linked Access / ODBC tables also
3. The list currently shows all non-system tables including those I’ve deliberately ‘deep hidden’ using dbHiddenObject (Flags=1)
Whilst that’s could be very useful for me, it also makes such tables less secure – suggest you exclude those tables
4. Password – suggest mask it (with option to display it?)
5. Table grid shows last selected table after new database selected including where that has no tables.
Suggest hiding/clearing table view grid when new database selected

A few issues / bugs:
1. If the read only option is unticked an error occurs: ‘Format of the initialisation string does not conform to the OLE DB specification’
2. Clicking the Switchboard_Items table in an old MDB (or ACCDB) file causes an unhandled exception: ‘Cannot find the table or query ‘switchboard’
Similarly clicking ‘Conversion Errors’ table in a MDB converted to ACCDB- can’t find ‘Conversion’
Further testing indicates the issue occurs in any table with a space in the name
3. All error messages including the above and a wrongly entered password include the message : err number = 5 ; ‘This is likely due to not having ACE installed for this bit size process
Possibly misleading

Thanks again. I'm sure others will find this utility useful

Posted by: FrankRuperto Nov 9 2019, 07:35 AM

I am curious to kno if Raas was successful in connecting to x64 Access 2019 CTR from VS 2019 by forcing the project to x64 and without first testing the connection. If successful, do all VS features, except testing connection, work?

Although we have detoured from the OP topic, it looks like birth can be given to the idea of creating a library of utilities for Access, Windows, SQL Server, and whatever else a compiled DotNet executable can connect to.

I am assuming that for DotNet interpreted and compiled scripts to work, they now require at least version 4.0 of DotNet framework to be installed?

Another cool feature I like about DotNet programs is that the class code stubs work exactly the same as the one's in Access, and that by checkboxing COM interop when creating a new project you can have your class exposed to VBA and call the DotNet code by setting up the reference in the VBA editor.

Posted by: AlbertKallal Nov 9 2019, 03:24 PM

Thanks for the inputs and kind words!

QUOTE
1. In the browse window file type selector only show the All Access files option
OR move that to the top


I actually was going to change this. Just playing with this, I found myself EVERY time choosing that setting. I was hesitant if I should up-load again (so JUST after uploading this was my #1 thought. I figured no one would notice! ;-)

I’ll make “all access databases” the first (default) choice.

QUOTE
Shows only local tables


I’ll take a look. I am not sure if this will work. I am using the oleDB “GetOleDbSchemaTable” feature. So, there is no “automated” copy of Access here. It is the provider catalog. Since you can’t link to a linked table, then from oleDB’s point of view, tables are tables, and linked are not considered as such. However, I will take a look if these linked tables can show. (but then again, code or highlighting to show the difference would be the next feature request! And THEN code to NOT try and display the data grid when clicked on would also be more code! (or worse, I am to grab the linked table information and display the data – this is also a good idea!!!).

HOWEVER, as I was playing, it dawned on me, that in place of the ACE and JET option? That should be a combo box (with allow additions). Right now I am REALLY considering adding SQL server to this! (in fact, any provider beyond ACE/JET could be added to the drop box).

I mean why limit the selection to JET/ACE? So, one could add FoxPro, dbaseII, Excel, Oracle, MySQL, SQLite etc.

Not sure how “universial” the get schema for oleDB is. (any oleDB experts in the room????). So, any experts here who used Access and ADO? (such people would know this answer instant – I just never been a big fan of ADO).

So, I am much thinking little reason exists to limit provider choices to ACE/JET.
As long as such providers are installed on the target machine, then this utility would/should work with any database.

Thus, over time, we would have a tool that can open + browse darn near any database system.

QUOTE
Hidden tables


Agree – could add a checkbox for this, but I’ll have to look “closer” at how the catalog works – see if these attributes are included – they likely are. However, this was a VERY quick and dirty testing program – really to just test if ACE x64 works.

The first draft was less then 30 minutes.

The WHOLE idea here was to make a quick utility to TEST if ACE or JET can work, open files and display data.

Thus, the original poster here can download this utility, and 100% determine in less than 1 minute if ACE x64 is up, working, and can open databases. So, this was REALLY only to test if ACE x64 is working on a given computer.

Somehow, this is “growing” on me, and I see use beyond JUST testing if ACE x64 is working!

However, as I keep adding a few things? Well like all silly Access projects, they often become rather useful!!! – this seems to be occurring here!!

QUOTE
Grid shows last selected table as new selection


Yes, I noted this too. I should also have a close button, but either way, new selection of database even before “open” is hit should clear the display. (always a wee bit more code to add – right?).

QUOTE
if the read only option is unticked an error occurs


Ok, the exclusive, read only, and password options might be messing this up. I’ll play with this.

Keep in mind, the read only should well be the default and NOT exposed.

HOWEVER, I could add a SAVE button. That gridview is 100% able to EDIT data! (already tested this!!!).

$100 dollar question?

So, should I allow editing in the data grid? It is SOOOO tempting!! (would have to add a save button, but .net can “edit” data in that grid. In fact the first version I uploaded allows editing – but no save button. This version I un-checked the allow data editing for the grid. So, I can enable editing, and all changes made (to one row, or many rows) would then be saved with one button. The data is in a in memory table, but .net keeps track of changes made. So one save button click would be required to save any dirty row back). I am mulling this one!

If not, then I will likely remove the read only button.

On customer runtime only sites, sometimes there are a few “config” tables etc., and having a handy dandy ability to edit maller tables could be added to this with about 10 lines of code max. So, the read only flag is NOT really needed to be exposed, but I put it there since data edit ability is possible here!

QUOTE
Further testing indicates the issue occurs in any table with a space in the name


Noted that one too! I simply need to enclose any and all tables with [ ] when I fire off a query.

QUOTE
a wrongly entered password include the message : err number = 5


Yes, for some reason, wrong bit size, wrong password, and a few others always seem to return error code 5. Note how the error message DOES correct note the password being wrong, but the err number code is always 5. (any ADO experts in the room????).

I know Access as a product VERY well – am a expert on Access. However, ONE area that I never spent HUGE time with is ADO. I mostly used DAO.

Now when I say “expert” on ADO, I mean that. So I have worked on a few Access (big) projects that used ADO. And I am working on a huge .net project (300k+ lines of code), and it also uses oleDB. So, not a big deal, but I don’t consider myself a guru when it comes to ADO. And right now I WISH I was!

Not a big deal, but if I had more ADO experience then I would INSTANT know the answers to the above error mode returned.

Anyone here with Access and ADO experience? How do you get different error codes for a failed open database?

I might just remove my error message, and let the message below be actual error message. Like all good developers, I started a nice “select case” block of code based on the error number returned. But, now I find out it returns error = 5 for just about everything. So I have this pretty looking case statement, but only one option for 5 and “else” error message.

CODE
Try
            mycon.Open()
        Catch ex As Exception
            Me.lblOpen.Text = "Error - can't open database"
            Select Case Err.Number
                Case 5
                    ' file not found
                    MsgBox("un able to open database file" & vbCrLf &
                   "This is likly due to not having ACE installed" & vbCrLf &
                   "for this bit size process" & vbCrLf & vbCrLf &
                   "Error msg" & vbCrLf &
                   Err.Description & vbCrLf &
                   "Err number = " & Err.Number.ToString, MsgBoxStyle.Critical, "Can't open file")

                Case Else

                    MsgBox("Can't open the database file" & vbCrLf &
                   "This is likly due to not having ACE installed" & vbCrLf &
                   "for this bit size process. Check error message below" & vbCrLf & vbCrLf &
                   "Error msg" & vbCrLf &
                   Err.Description & vbCrLf &
                   "Err number = " & Err.Number.ToString, MsgBoxStyle.Critical, "Can't open file")

            End Select

            Exit Sub
        End Try


So GREAT case statement. I just assumed over time I would and could add MORE custom error codes, but they are all = 5. But then again, if I had used Access and ADO, I would have known this ahead of time - but as noted, I don't have huge experience with Access + ADO.

So, if ANY of you folks have Access experience with ADO, is this how ADO works in regards to error codes on opening a database?

I could write code to look at (parse) the text error message. But that is a real suck way of dealing with errors. I am also hesitant to “parse” some text as opposed to an actual error number since on different language machines, that message is going to be a different language.

Just where are those Access ADO users when you need them!!! I’ll have to google oleDB and JET to see if anything comes up.

If I was doing .net + Access? I actual would consider using the ODBC providers as opposed to oleDB. The reason is that THEN if I wanted to move/use SQL server, then less changes would occur. When you move from ACE/JET oleDB to SQL server oleDB, then things like “#” for dates have to be changed to single quotes for SQL server.

With ODBC, the same SQL works for both SQL server and JET/ACE. So a LOT LESS code changes are required if you change databases for the same given code by using ODBC over oleDB.

I’ll take a look at some of these issues. The “all databases” should be the default, and clearing of info when selecting different database, and spaces in table names warrant fixes.

It’s oh so tempting to add SQL server to this. And really, allowing adding of more providers is JUST a string name. So adding any legal connection string to MySQL, Oracle, FoxPro, Access or whatever should for the most part allow this to work, and the “rest” of the code should not require changes. The super tiny 23kb size of this project will also not change by adding these features.

And allowing edits is tempting!! – I have tested this already!

Again, thank you kindly for the suggestions.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com




Posted by: AlbertKallal Nov 9 2019, 03:33 PM

QUOTE
I am curious to know if Raas was successful in connecting to x64 Access 2019 CTR from VS 2019 by forcing the project to x64


All he has to do it download my sample - run it. It will take 30 seconds. Right now, it VERY much looks like that 2016, or 2019 (full or runtime) don't register/expose ACE, but the above provided link to the "ace connective ki is quite small, and by installing it, then my utility (or his code) will then work.

So, a 30 second test by using my program will clear this up. (the WHOLE point of the download!)

But, so far, it looks like access 2016, or 2019 full or runtime will NOT do the trick - one has to install the ACE connectivity link above.
(however, that install is MUCH smaller then Access runtime, so in many ways this is not all that bad of a occurrence).

QUOTE
I am assuming that for DotNet interpreted and compiled scripts to work, they now require at least version 4.0 of DotNet framework to be installed?

Correct - I picked 4.0. I could lower it down to 3.5, but .net 4.0 should at least cover all the way back to windows vista. I think even these days windows XP boxes will have had .net 4.0 installed at some point in time.

If someone asked me for a windows xp version? I would suggest they install .net 4.0 on that box if not already.
Unless it was a real big issue, then I do not think it is worth to drop below .net 4.0 (there is a truckload of newer and better features from 3.5 to 4.0 (or later).

R
Albert

Posted by: FrankRuperto Nov 9 2019, 04:54 PM

@Albert

QUOTE
should I allow editing in the data grid? It is SOOOO tempting!!


IMHO, as a general rule its not a good idea to directly edit tables, unless your new DotNet utility is only destined to experienced developers.

Nevertheless, I think a compiled DotNet utility that can display data from multiple ODBC providers is a great idea. I have used SSMS, OracleDeveloper, PostgreSQL-pgadmin and it would be nice to atleast be able use one tool to view data from different providers.

Posted by: isladogs Nov 9 2019, 06:25 PM

Hi Albert
In my equivalent Access based utility, I read the tables in the external Access app by query MSysObjects so I can read all tables whether local/linked Access/linked ODBC/hidden/system etc. It can also distinguish between them using the Type and Flags values.
Is that approach feasible using .Net?

Posted by: Raas Nov 9 2019, 08:17 PM

QUOTE
I am curious to know if Raas was successful in connecting to x64 Access 2019 CTR from VS 2019 by forcing the project to x64


I went through all of the posts, taking a lot of time, and pulled out the pertinent information a bit at a time. I then followed my step-by-step compilation of instructions and with some more input from Albert's downloaded file, I now have a program that will run on Visual Studio 2019 and connect to Access 2019 - 64 bit.

As of about 15 minutes ago (6:12 pm Mountain Standard Time on November 9, 2019)

Thanks to all, even though the post got way off topic for me. I still ferreted through them, slowly.

Now, to find out the best way to retrieve and manipulate my data from the table!

At least I can now keep my course.

I will put my step-by-step instructions on this thread. If I have something that is redundant, I'd like to know. If I missed an important step for future data retrieval, please let me know.

Thank you to all, especially Albert for his continued patience with someone who is just trying.

If I come across questions on retrieving the data is this still the right thread to post on?


Posted by: FrankRuperto Nov 9 2019, 09:04 PM

@Raas

Glad to hear you got it working!... YES, please let us know if you encounter any new problems with the VS/x64ACE combo. Although this thread detoured off-topic into DotNet, it was educational.

Posted by: AlbertKallal Nov 9 2019, 11:04 PM

QUOTE
read the tables in the external Access app by query MSysObjects


Ah, very good. That certainly points to a solution (yes, reading a table is quite much the same as access + VBA).

I’ll consider adding a “hidden” table show/view option. I do note that the grid can’t display that table.

I don’t have a hidden check box in this update.

And it should catch errors for the switchboard, but I did not have one handy to test.

So, all your suggests are in this update, exception being the hidden table. (But your hint and direction on a solution here is a good one). I would “prefer” a pure ADO.net solution based on the Schema interface, since then this code will work for SQL server etc. (but, really not a huge deal to read/check those system tables).

So we now have this:



And it is still only 26KB in size!

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



 AccessLook.zip ( 22.86K ): 5
 

Posted by: AlbertKallal Nov 9 2019, 11:13 PM

QUOTE
If I come across questions on retrieving the data is this still the right thread to post on?


This thread is too long right now. I would start a new post. I would consider also using some .net forums, as there is a not a lot of .net folks here.

There is a Visual Basic 2003 and vb.net forum on UA. I can't say it has much traffice. (but, hey, we might change that!)

So this "general chat" forum certainly not a great choice for .net questions.

I mean, for any questions, you want as many as possible eyeballs seeing the question, and ALSO those with .net experience.

this thread as noted is un-likely to generate answers.

Good to see you have this working. The batch file test thus was a good confirmation that your computer was not messed up. And the simple .exe I posted here is a great quick and dirty test/check tool for the future.

Regards,
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Posted by: isladogs Nov 10 2019, 01:18 PM

@Raas
Apologies for hijacking your thread but Albert's table viewer utility really is extremely useful

@Albert
It just gets better & better.
I've reported my own post asking mods to separate this conversation into a new thread.
My suggestion would be to copy post #58 and move posts 59/60/61/63/66/69-71 (and any further replies to this).
I think that doing so will get a wider audience than this is likely to have at the moment

When you've finished modifying the utility, I also think it would be a very useful addition to the Code Archive

Some further comments:
1. Five of the standard system tables don't display the records : MSysComplexColumns/ MSysACEs /MSysObjects, MSysQueries / MSysRelationships
All say Err-table load. The common feature is that all have Flags value -2147483648 in MSysObjects

2.As you say, your system doesn't list any of the 'deep hidden' system tables such as f_...long string here ..._Data, MSysComplexType_Short etc, and if it did the contents wouldn't display anyway.
However it does list AND display any user created deep hidden tables (dbHiddenObject) including any also 'flagged as dbSystemObject.
For example there are 2 deep hidden tables in the attached DeepHiddenTables example app one of which is also read only.
Your app shows their contents BUT this is what I really want to see blocked (at least by default)

Also in case you're interested, attached is my ListExternalTables app created in Access.
This one deliberately excludes deep hidden tables (though I have another version for my own use to show these as well).

BTW Some time ago I discovered a way of displaying the contents of ALL deep hidden system tables such as those used to store MVFs and column history in memo fields.
Access makes it really difficult to do so but it is possible. The surprising thing is that most of them turn out to be editable!
For more info, see these links on my website:
http://www.mendipdatasystems.co.UK/purpose-of-system-tables/4594446646 ; http://www.mendipdatasystems.co.UK/column-history-memo-fields/4594523656 ; http://www.mendipdatasystems.co.UK/multivalued-fields/4594468763

 DeepHideTablesExample.zip ( 28.48K ): 5
 ListTables_v1.6.zip ( 69.5K ): 3