UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
4 Pages V « < 2 3 4  (Go to first unread post)
   Reply to this topicStart new topic
> Visual Studio 2019    
 
   
isladogs
post Nov 9 2019, 05:26 AM
Post#61


UtterAccess VIP
Posts: 1,806
Joined: 4-June 18
From: Somerset, UK


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

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Nov 9 2019, 07:35 AM
Post#62



Posts: 252
Joined: 21-September 14
From: Tampa Bay, Florida, USA


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.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
AlbertKallal
post Nov 9 2019, 03:24 PM
Post#63


UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada


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



Go to the top of the page
 
AlbertKallal
post Nov 9 2019, 03:33 PM
Post#64


UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada


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
Go to the top of the page
 
FrankRuperto
post Nov 9 2019, 04:54 PM
Post#65



Posts: 252
Joined: 21-September 14
From: Tampa Bay, Florida, USA


@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.
This post has been edited by FrankRuperto: Nov 9 2019, 05:37 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
isladogs
post Nov 9 2019, 06:25 PM
Post#66


UtterAccess VIP
Posts: 1,806
Joined: 4-June 18
From: Somerset, UK


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?

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Raas
post Nov 9 2019, 08:17 PM
Post#67



Posts: 639
Joined: 27-January 07
From: Northern Arizona


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?

Go to the top of the page
 
FrankRuperto
post Nov 9 2019, 09:04 PM
Post#68



Posts: 252
Joined: 21-September 14
From: Tampa Bay, Florida, USA


@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.
This post has been edited by FrankRuperto: Nov 9 2019, 09:07 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
AlbertKallal
post Nov 9 2019, 11:04 PM
Post#69


UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada


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


Attached File(s)
Attached File  AccessLook.zip ( 22.86K )Number of downloads: 3
 
Go to the top of the page
 
AlbertKallal
post Nov 9 2019, 11:13 PM
Post#70


UtterAccess VIP
Posts: 2,874
Joined: 12-April 07
From: Edmonton, Alberta Canada


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

Go to the top of the page
 
isladogs
post Nov 10 2019, 01:18 PM
Post#71


UtterAccess VIP
Posts: 1,806
Joined: 4-June 18
From: Somerset, UK


@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:
Purpose of System Tables ; Column History in Memo fields ; Multivalued fields ...and why you really shouldn't use them
Attached File(s)
Attached File  DeepHideTablesExample.zip ( 28.48K )Number of downloads: 2
Attached File  ListTables_v1.6.zip ( 69.5K )Number of downloads: 1
 

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
4 Pages V « < 2 3 4


Custom Search


RSSSearch   Top   Lo-Fi    16th November 2019 - 07:41 PM