Couldn't agree more!

Thanks for the tip. I'll have to look into it.

Awesome! Thanks for sharing. :)

Access VBA is the best! I'm glad I picked it up recently in one of my ongoing projects (SPCA Serving Allegany County Theme Basket Auction, which is an annual event that I try to bring improvements to every year, and this past year I released it to the public to use for benefit auctions). One thing I try to do is make the entry form match the sheets that the ticket salespeople write on. Best way I figured out so far is to have a "from" field and a "to" field at the top of the form, and have a button to requery with a criteria with a "Between from and to" statement in it. So the enterers can look on the sheet for the first number, enter it in the from field, then enter the last number in the to field, and click the button. Problem is that I would have to prefill the database which caused it to be needlessly long and sometimes the organizers would get tickets in the hundreds of thousands range which meant I had to fill a million or so records at the very beginning. And had writing queries to count only the filled in ones problematic. Then I went to writing SQL queries to create a tally table using recursive CTE, then a second button assigned to an append query to append from the tally to the working table. That was tricky because everytime one of the other enterers would use it, they would ask me if they should click yes on the prompts or not. With around 10 lines of Access VBA I learned this past year, which solved all of these problems. No more need to prefill and no more yes/no prompts for append queries, and it all uses a second button.

And I even use it with multiple people entering at the same time on a MySQL/MariaDB backend because I couldn't get it to work with SQL Express and I was familiar with MariaDB using it for some of my website projects anyway. It works great as a backend on Access connecting it through the ODBC driver, as long as you build it with SQL code first, then just connecting Access to it using its linked tables manager. (Edit: and I can get it to work using an old computer running Linux as the "backend server" without having to buy another Windows license)

Access isn't perfect, but it's the best in its class.

I've found that C&R fixes most problems. If not, you can try 599cd.com/Decompile. But yeah... once in a while it's necessary to create a new file and import everything.

I agree that Access should have referential integrity between different database files, but that's just not the way it's built. If you want that, you'll have to go with a true server like SQL Server. However, everything that referential integrity does can be done with VBA code. It just requires a lot more work on the developer's part. If you want something like cascade deletes, you can do it yourself by deleting the child records before deleting the parent. I never use cascade updates. If you want to prevent widows and orphans, you can also do that with a little bit of coding. Again, it's more work for the developer.

Agreed 100%

Very true. Gotta put your databases in a Trusted Folder. But, once you set that up, you don't have to worry about it... until the user installs a new version of Office. :)

Ha ha. Agreed.

Crosstab queries can do most of that... but you're right... some things are better done in Excel. PivotTables is one of them. Use Access to STORE your data, and Excel to generate your PTs.

I've never pronounced it "sequel" but yeah, I know a lot of developers do.

I just think "IT Pros" look down on Access because with a little training ANYONE can build quality databases with it, and they don't like that they've spent lots of time (and sometimes money) learning some other "professional" system that, in all honesty, probably isn't that much better than Access. Access is BY FAR the best system for rapid app development. You can't get a good database up and running faster in any other app. As far as replicating and reselling? Meh. Possibly. But you can lock down Access to prevent that (ACCDE, some security).

There are some things a custom designed and coded system works better for, but that's kind of far and few in between. Most people want to just run basic CRUD operations on visual forms with minimal effort and dev time, and for those uses, Access is perfect.

And you would be surprised at how many commercial applications, especially niche ones are using Access at least on the backend. At my day job, we use Mitchell1's Teamworks to manage work orders and invoices in an automotive shop environment. The first versions I worked with literally used MS Access files on a shared drive as its back end. Newer versions seem to use SQL Express as its backend (at least according to how the software notates the "Server Location" and the fact the server install also installs SQL Express. My best guess is somewhere along the code stack there's still some MS Access code or at least inspiration in it. Our local firehall has a sort of recent version of an application called Red Alert Entry. Before they went to a multi user license, the single user version of the software also used MS Access files in the Public Documents folder of the computer. The multi user version seems to use SQL Express as well.

ACTUALLY THERE IS A WAY YOU CAN LICENCE YOUR SOFTWARE USING A PRODUCT KEY DEPENDING ON EACH MACHINE ID SO NO ONE CAN EVER RESELL YOUR PROJECT. I FOUND A WAY OF DOING IT AFTER A LONG SEARCH .

Wow. Great story. Thanks for sharing. And yes, I agree... The right tool for the right job.

​@@599CD Access did fine this past year too. Had three computers entering into an Access frontend and an ODBC/MariaDB Backend, with over 7,000 records in the regular ticket sales table, and 3,000 for specialty tickets. No systematic issues to report other than one human-error mistake which only messed up one line on the report.

Split your database. 599cd.com/Split

Up to you

I hear ya

Fortunately I don't know of any way to do that other than the things you mentioned or decreasing the resolution of your monitor. Microsoft gave a lot of love to Word and Excel and PowerPoint and they gave them the features to like zoom in and out of the interface but of course Access is the red-headed stepchild so we get no love.

@@599CD Oh well - maybe I need new glasses... Thanks for the reply.

I agree with everything you said. Who's Mike? :)

@@599CD Ouppsss ;o)== Richard ... sorry

This is true. Gotta make sure you include error handling EV.REE.WHERE. :)

Sounds like a corrupted DLL. Probably going to need to UNINSTALL and reinstall Office. See 599cd.com/Trouble

Yeah... gotta have that biz plan for $12.50 per month (currently). Still a bargain in my mind. I remember when Access used to cost like $200 by itself, and that was in the 90s, so more like $400 today.

Yep

Nope. No reason. They're on my website. 599cd.com

Yep... lots of pros and cons for each. I don't know why so many developers don't want to use Access because they don't want their customers / end-users knowing it's an Access application. Like they're ashamed of it or something. So silly.

Mine too

Nothing will beat Access for rapid database development - at least not in the next couple of years.

Coming from Access development, I find PowerApps terrible and super gimmicky

I agree with you about "consultants." Sure... let them go work for some big company doing "real" dev work with C++. No problem. We'll make twice as much as independent consultants working with small businesses. :)

Agreed.

Sweet

Make-table queries, my friend. Pull down the info you need from SQL and then build your reports to those tables.

Actually, this is not a problem, nor a issue. I for the most part do significant work with Access and SQL server in corporate environments. So, first up? You just don't need to use ADO for this. It does not give you better performance, and in does not offer any significant advantages to the alternatives. Ok, having stated the above, then just what are the alternatives (to using ADO). Well, first and foremost - if the report in question is only one table, and not a complex sql of joins and multiple tables? In this (lame and simple case), then of course just bind + link the report to the linked table. Even if the table has say 1 million rows, you can simple feed the report with critera, and ONLY that data is pulled down the network pipe. eg: docmd.OpenReport "rptInvoices",acViewPreview,,"InvoiceDate = #10/30/2021#" So, in above, EVEN with the report bound directly to the linked table of say 1 million rows, only records matching the criteria get pulled, and thus performance is fantastic. Ok, now what about a REALLY fancy pants complex multiple table join, row_number Over, t-sql that walks the dog also? Take that t-sql, or the nasty client side access sql with all those joins and stuff, and create + save that as T-SQL and a view. You can now use 100% valid T-SQL for that view. You now delete the client side messay query, and link access to the server side view. (give it the same name as the messy client side sql query you had). NOW your report data source is based on 100% super duper t-sql syntax. And even more amazing? The above open report command will TILL ONLY pull and filter down the critera you send with the above "where" clause of the open report command. So, even better? so far we not written ONE line of ADO code, have we? And we not needed to do so!!!! What about if I need custom t-sql for the report? Well, in 99% of cases, you don't write nor use string concatenation with t-sql either - it is VERY frowned upon. However, i place of all that ADO? Just create in Access what is called a pass-through query. Base the report on that PT query. Now, you can do this in VBA. CurrentDB.QueryDefs("MyReportQueryPT).SQL = "Your 100% valid t-sql goes here" docmd.OpenReport "rptInvoice", acViewPreview Presto!!! - you are now feeding the report 100% valid t-sql syntax sql and even commands by doing the above. And once again, ZERO need for ADO. and last but not least? Why of course based the report on a T-SQL store procedure! Yup, you can set the store procedure in the PT query. So, say that t-sql was the mother of all complexity. Too much for a saved T-SQL view? Then do this: Currentdb.QueryDefs("MyReportQueryPT").SQL = "EXEC dbo.SuperComplexStoreProc @invDate = '2021-10-20'" docmd.OpenReport "rptInvoices",acViewPreview Now I hard coded the date above, but of course that would be a VBA var or value. So now the report is based on a t-sql stored procedure. Once again, no ADO code, and no messing around with even connecting strings or messy ADO code in VBA. Bottom line: You have MULTPILE choices and options from t-sql views, to a PT query, or even using t-sql store procedures for the report data source, and most of them allow use of 100% T-SQL syntax and options. And none of the above suggestions required ADO, or the need to set the reports data source to a ADO record set. You are 100% free to use a view, or PT query, or a PT query that uses a T-SQL store procedure as the data source. So, don't need to use ADO, and fact the above suggestions are better and less efforts anyway. And, last but not least? Richard's suggestion to pull data local, process it anyway you wish to slice and dice it, and then use that for the report is also a option. In fact, I probably left out a few more suggestions. The fact that we have all these options? Once again, just shows how great and flexible Access is. And Access + SQL server, or in fact most server database systems is just another fantastic option we have for Access. Regards, Albert D. Kallal (Access MVP 2003-2017) Edmonton, Alberta Canada

Awesome tips. Thank you very much. I cover most of this in my Access SQL Server Seminar. 599cd.com/AccessSQL. One of the things I show is how to write passthru queries on the fly, much like you showed there. Works like a charm. :)

@@599CD Great stuff. Really nice to see someone putting out first rate material and showing that Access is a remarkable tool. Access is a great tool - it spans from the simple all the way up to remarkable abilities that quite much any company from small to large can use. Regardless, a up vote and double up vote for the quality of your videos and how much this helps the Access community, and ultimately opens up amazing abilities for people that Access offers.

Agree to disagree. I have very little bugs in my databases. PBKAC error? :)

Well that sucks. I've got a video coming up on how you can kick a user... :)

The actual title is "ADDRESSING the Common Criticisms..." Yeah, I know the title slide in the video doesn't include that. I added it afterwards when I realized I spent most of the video defending Access. LOL

Is that what they're getting now? Damn. Back when I used to be a full-time Access consultant, I was only charging $85. Then again, that was back in 1996. So I guess with inflation that comes out to... $150 today. OK, not crazy then. :)

There's a lot that Microsoft has poured into Excel that they haven't put in Access yet. Look at Conditional Formatting and Charts.

Well, it not currently NDA anymore, but they plan to introduce a new sql editor for access. It will be based on the same code base as the one used for SQL server. So this long time issue is expected to receive some love and care in the not so distant future.

Yeah I read about that. Looks neat!

Welcome

Welcome

Whoop whoop!

I know. I am. :)

Thanks