What are /r/MSAccess' favorite Products & Services?

From 3.5 billion Reddit comments

Well, sort of, but no.

Information on reports comes from two places, its either put there as static text- eg. a label field that you create while in design mode, which remains the same all the time (or at least until you go into design mode and edit it again) or it comes from a table or query. There isn't a way to input information onto a report itself.

The reason for this is because reports are for displaying information in a user friendly way, forms are for inputting information. Even if its once off, never used again information, its still information. Because of this you have two options,

You can create a form specifically for this field and have the user enter their information before the report is generated (so maybe on the navigation page where the report is listed, you have a text box next to it for 'Special Instructions' or something like that)

Or, and what I'd probably do is to create a printable form. This would allow you to have an unbound text box, which the user could fill in but the information wouldn't be saved. You could turn off editing and adding data for the form, so it would essentially work like a report, but you could print properly and save a PDF. The link below talks about saving a form as a PDF in Access 2010 or greater.

http://www.addictivetips.com/microsoft-office/access-2010-save-form-in-pdf-format/

Here is an alternative to what /u/iambaman gave you. I modified the file he created.

File

I added
Table : ButtonAction, Buttons.
Queries : qryButtonActions, Modified qselReport
Form : Buttons, tblActionsSubForm

You are correct that an Access web app is far too basic for your needs. It's also a PITA to code

I would recommend against using SharePoint, even though it's included with Office 365. It's slow and hard to work with.

Check out Amazon Web Services. You can host your back end on their web server as a SQL Server database. Then you would simply link to your back end via ODBC, and, as long as you're connected to the Internet, everyone would have access to the same live back end.

You could even first convert your Access back end to a SQL Server back end using the built-in SQL Server conversion tool within Access (Database Tools tab; Move Data section; SQL Server). You would need a copy of SQL Server running on your PC. But Microsoft provides a free copy of SQL Server for development that you can download from their website (only allows one user at a time to access the database).

Or you can just recreate your back end on Amazon Web Services instead of converting it and uploading it, which might be simpler to do if your back end is not too complex.

AWS is also reasonably priced. They only charge you for what you use.

Here's a link: https://aws.amazon.com/rds/sqlserver/

<>

Another option, which is simpler, but also more expensive, is to simply host your Access database on a web server and access it through Remote Desktop. That would require no conversion. Your database would just be hosted as-is and users would connect to the web server to use it. But it costs $19/month per user.

Here's a link to AccessHosting, which provides that service for Access databases: http://accesshosting.com/remote-desktop-hosting

In addition to being more expensive, it also requires connection through RDS which, while very good, is not the same as having the front end on your C drive and running with your own copy of Access. Thus, the AWS option, I think, is best. But the RDS option, as stated, is simpler.

Hope that helps!

OK, there are a few ways you could go.

The simplest way, but also the most expensive way, would be to simply build an Access database and host it on a site for access through Remote Desktop. Everything would be exactly the same as it would be if your app was local. The only difference is that it would be hosted on the Internet instead of on your local PC.

Access Hosting provides this service for $19/mo per user, which I think is a pretty reasonable price. Here's a link for more info: http://accesshosting.com/remote-desktop-hosting

A second approach, which would be a little less expensive, and would also allow you to use your database front end on your local PC instead of needing to connect to the cloud to use it, would be to host your back end only in the cloud, but use the front end on each user's PC.

Amazon Web Services provides a very reasonably priced option for this, billing you a couple of pennies per hour of use. The drawback to this approach is that it would be a little more complicated to set up, and would require that the back end be in SQL Server, rather than MS Access.

But your front end, where all your objects and coding would be, would remain in Access, and you would get to use your front end locally on each PC, instead of having to connect through Remote Desktop.

Here's a link to Amazon Web Services for SQL Server: https://aws.amazon.com/rds/sqlserver/

So those are the two main options that I would recommend. If you're looking for a simple solution, and don't mind spending a few extra bucks a month, I'd go with Access Hosting and RDS. If you'd rather save the money and be able to use your front end locally, I'd go with Amazon Web Services.

(Note: if you do with with Access Hosting and RDS, be sure to still split the database into a front end and back end.)

You could, of course, set up your own server in one of your houses and go that route. But that would be a bit more work to do, and not sure if it's worth it.

As a fellow rookie, I have learned a lot from buying a course or two when they are on sale at udemy.com. Maybe it's worth a visit. Wait for a sale in the $10-20 range and not full price.

Surely something can be rigged up in VBA or treat Excel workbooks as SQL data source for Access. I've heard Visio can be scripted, but never attempted it myself.

Have you considered running all these scripts from a continuous integration server instead of office apps? For example Jenkins has all kinds of reporting for all the script pipelines you setup. It does not have to be used just to build and deploy software, it is at heart a framework to run and monitor script jobs. There are some visualization plug-ins and built in success/fail dependency tracking capabilities.

> Does Access VBA allow for multiple parameters in a script?

Well, since I used the plural form "parameters" when I said to just pass the parameters to the function, that would be the assumption, right? ;-)

> The most recent one seems to be for Access 2007, has much changed since then?

Huh. I didn't realize they stopped publishing it in 2007. Access went through a major overhaul in 2007, so you don't want anything prior to that. There have been some changes since 2007, but nothing major.

So I think the 2007 book would be OK. Or you may want to pick up a more recent one.

Looking on Amazon, I see that a lot of the books only go through 2007. I found one beginner book that uses the 2010 edition:

https://www.amazon.com/Microsoft-Access-Programming-Absolute-Beginner/dp/1133788955/ref=sr_1_7?dchild=1&amp;keywords=access+vba+programming+beginners&amp;qid=1618866062&amp;sr=8-7

There are also online tutorials and tons of YouTube videos, if you wish to go that route, instead. (I, like you, prefer to work with a book that I can page through.)

The FAQ of this sub has a list of resources that you might find helpful.

I think that you're making it more complicated than it needs to be. You don't mention whether the equipment is already barcoded, what kind of barcodes you want to use, or what barcode scanning hardware you have.

Assuming that you'll need to create the barcode labels for the equipment, you can start with some inexpensive polyester laser labels like this:

https://www.amazon.com/Avery-Permanent-Durable-Labels-Printers/dp/B0000AQNK4

If you don't already have a barcode reader, there are plenty of basic USB models that are under $100. Your computer recognizes the reader as a standard keyboard, so anything you scan behaves as if you're typing the data in. Just do a search on Amazon. Laser scanners are easier to use than CCD scanners.

The next component you need is a barcode font. There are plenty available for free. Code39 is a common, flexible, and easy to use barcode. This page has a free one for download, along with instructions on how to use it:

https://www.barcodesinc.com/free-barcode-font/

From your description, I don't see any compelling reason why the barcode would need to contain the part and serial numbers. It would be much more straightforward to add a field to your database that would contain a unique 5 digit ID. You could create a template in Word to generate a few thousand labels starting with 10000 and going as high as you need it to.

From there you would have to decide whether to undertake the tedious task of labeling everything at once, or waiting until a piece of equipment is going to be used. If you take the latter approach, you would just stick a label on the piece of equipment, manually find its record in the database, and scan the label into the new ID field. From that point forward when that piece of equipment is used you'd just scan the label into a lookup box to immediately jump to that record. The form and lookup box can be created through wizards, so no VBA knowledge or use would be necessary.

Sorry for taking so long to get back to you.

It looks like you may have found a solution to your problem, but I thought I would add my 2 cents.

I like to use the AfterUpdate() event with checkboxes in combination with a routine that sets the state of all the checkboxes.

In your case, you may actually need several routines to handle all the checkboxes logically.

Here's an example of a form with a simple list of checkboxes and an option to select all.

Let me know if that helps/makes sense:

• https://drive.google.com/file/d/0B0MbgFLez5yZeDZmcHhrajQzMkE/edit?usp=sharing

Contrary to u/mactreb you could start a table as a bit of a sheet just filling in al the details. Then you are already halfway in.

Having a table with fine date, due date, pay date, and the culprit's details will get you started of very nicely.

Then you can just build a few queries to begin with, like overdue fines, top Culprits etc.

Optimizing like making things relational can be done at any stage, when things start to grow and it get's to the point where converting it to a relational format makes more sense.

Additonal to u/nrgins mention of youtube tutorials I'm still a big fan of books, buy something like access 2019 bible to have all the topics in a proper order, in paper version so you can add sticky papers and scribble your notes.

I find trying to learn Access completely over the course of a single task to complex for most given time one can allocate to it, while also producing results to show for. But just start of simple, and set goals as you go along fitting your pace and needs of complexity (or simplicity, as once you get the hang of relations a whole world opens up).

And stick around in this sub, just to learn and get inspiured of other's questions and answers.

Develop a way to remove the columns from the file from the CSV before it reaches access. Either through file level vba or some other program. You'll need to make the dataset smaller at the file level.

My advice is don't use access to manipulate massive datasets. It can't handle it very well and I don't know what the 64bit limit is, but the 32bit version would stop after 2gb of data had filled it's temp tables. So if you load in 1gb that only gives you 1gb left to do join work.

Personally, my recommendation is to use a real database server, like the open source MariaDB. You would also need to use a DBMS to allow you to easily see the tables and run sql against it to manipulate the data, so use something like dbeaver. If you need to do something programmatic to manipulate the data, use python and connect it/your app to mariadb.

I know this isn't really the advice you want to hear, you're prolly thinking about how you really just need to figure out how to do it in access. I am not saying you can't maybe there is some crazy magic out there. But I did this (access) for 10 years in a corporate environment and let me just tell you that you can easily waste a lot of time trying to work with large datasets in access. Even assuming you get them imported you might not have much space left to query them.

I don't think anyone finds those kinds of questions to be a nuisance. Basically, if you do the work of learning the basics but you have questions about something specific, most of us don't mind answering those kinds of questions.

At the same time, it's always a good idea to read and learn on your own as much as possible (or watch videos or take online courses). But don't feel like you can't ask questions here.

That being said, there is a list of resources in this sub's FAQ section.

There are also tons of YouTube video courses and online courses.

As for books, I know many people like the Access Bible . I haven't read it myself, so that's not a personal recommendation.

And here's a list of the best Access books , based on expert recommendations.

You could do it in Access, but might be worth looking at modern cloud To Do software. Having this info in the cloud means you could triage To Dos/Tasks/Remiders' from any device Phone/Tablet/PC/Browser. i use https://ticktick.com for heavily lifting (work projects) and "Things" on ios for simpler tasks, like house stuff.

These two images should show that I have the queries linked the right way. Left to Right, One to many. The issue is that Query1-Provider is in a One to Many to Query2-Claims AND Query1-Provider is in a One to Many to Query3-Accretion. Eventually there will be a 3rd subquery linked to Query1-Provider.

http://tinypic.com/r/2ron5vs/8 http://tinypic.com/r/33lmhq9/8

Pass uniquely identifying information(first name,last name, testId,EmpId etc...) to the second form with the on_close event of the first form: examples then write an update query using the unique values to update the correct record with the test results.

It's been a while... and I'm not sure you can get the AutoNumber without VBA.

You could also use a subform instead of two separate forms.

Oh no worries! I will continue working on it and if I can't figure it out it's only part of the assignment. sorry to hear about the fires hope all is well over there! Thank you again for being so helpful and even offering assistance in the first place :) https://gofile.io/?c=YCNZs2

There are hundreds of reserved words for SQL. Check out this reserved word list. It is the reason r/cityinbetween left off your 'Order' field because that would cause an error. You may want to consider giving that column a new name.

Use a validation rule on me.roundtrip instead of the BeforeUpdate trigger (which runs when the control loses focus) .

You could use something like [RoundTrip] IS NULL for the validation rule and then enter some validation text "Roundtrip Name cannot be blank".

Then they more or less cannot exit that control until it's filled in once it gains focus.

Then use your save button to actually save the value once you're done?

Example image https://www.safaribooksonline.com/library/view/access-2010-the/9781449382384/httpatomoreillycomsourceoreillyimages643883.png

[ https://www.guru.com/work/detail/1689023 ]

Posted 12 hrs ago | Job ID: 1689023

access database

Fixed Price or Hourly

Quotes (8) · Premium Quotes (0) · Invited (1) · Hired (0)

Send before: 19 August 2020

Programming & Development

Programming & Software

Microsoft

Using Access, you will create a NEW database, including creating tables, queries, forms, reports, and advanced formatting tools.

Some sample Access ideas include (1) inventory of technology at work, (2) inventory of your home/possessions for insurance purposes, (3) a list of all contact information you have on others.

PROJECT REQUIREMENTS

Our project MUST include the following:

1. Three tables that are linked (a minimum of 10 records per table).

2. Two types of queries.

3. Two types of forms.

4. Two types of reports.

5. Advanced formatting tools.

6. Organized, formatted, and professionally presented Access file, including grammar items (correct spelling, etc.).

>=[qrySumofTable1_subreport].Report.[SumofField1] + [qrySumofTable2_subreport].Report.[SumofField1]

Thank you so much Mr. AccessHelper! It works!

I tested with different values. Result: https://snipboard.io/zjWtrA.jpg

All I can do is google using the error your getting without any context. So randomly, this?

http://www.experts-exchange.com/questions/27946417/Access-2010-operation-not-supported-for-this-type-of-object.html

Check out udemy.com, they have great classes for access that share a whole bunch of knowledge. I saw someone suggest YouTube, it's a great tool as well. Also, try the northwind database. I usually reverse engineer stuff, so the nortwind database really helps figure stuff out for me in my db.

I like to use Draw.io. It is a good beginner tool for creating flow charts and diagrams.

Another more powerful tool for creating database diagrams that will also generate the SQL code is Genmymodel.com

I think the answer to that is definitely yes. The power suite can and will take over the roll which access filled for self developed business solutions. Check out this article from Microsoft to get a feeling of what can be done: Account Planning App. It is all about the CRM system which is used internally at Microsoft by their global sales team, something like 5k users. It is totally built from the power platform!

Access might not be the best approach for this kind of application. Microsoft have dropped Access' web functionality.

The company is trying to move people over to Microsoft Powerapps for developing online applications.

https://powerapps.microsoft.com/en-us/

Generally, if in-house developers won't give access to the code to the end-users, there is a good reason for it. You may also have limitations on installing outside software as part of your employment handbook or employment policies.

If none of that dissuades you, look into AutoIt - https://www.autoitscript.com/site/autoit/downloads/

Try installing something like Telerik's Fiddler if you want to see what applications are generating http traffic and in what context. It's possible your app is doing something odd. You can tell for sure with Fiddler if it's different from what other apps are doing.

Are you asking if the 'Power' Family is compatible with itself.

Power Automate, Power Apps, Power BI and Power Query... Yes. Yes they are if you want to program an automated process from a button press in power apps button you can do that

You would be looking for recordset appendnew methods, to be adding your data into the proper fields.

Just parse the variables from your looping through textfiles into an addnew function. simple method is just do it one by one while opening and closing a/the recordset on each append. Might be not the most optimal, but quick enough on an SSD drive.

If you grasp the general concepts of Access (e.g. stuff in Access Bible) and have some programming general knowledge the Microsoft-Access-2019-Programming-Example or newer is good for getting a solid foundation of methods to program these kind of things in VBA. Was wel worth the money in my view.

Additionally, since the VBA word has fallen in this thread (and at some point access benefits from VBA, e.g. to do queries, add data, open forms) at that point

"Access 2019 Programming Example" would be a good source to get some advanced methods.

If the boss is not convinced just build a version in your own time. That's how I do it if I'm sufficiently annoyed by existing crappy methods.

If not being paid, it's also good for your own education/experience of how to build a database properly. For one project I just purchased my own access bible and programming access book.

Just make it something small enough to convince the boss, or the team (who should then pressure the boss).

Good thing is you'll be the boss of the idea then, and initially not hampered by feature creep.

The argument of already having invested money and not seeing the need for re-investment is a typical manager problem. While of course not feeling right, it would be your, and your colleagues' duty to quantify the future lost time, risk effects of wrong data (e.g. sending stuff to wrong address) and offset that to a realistic budget of rebuilding the database model, while still being able to do work while developing the tool ^((migration point at some time?))

But as first argument a 30 column sheet and having data in two places is a no go to begin with.

What I'm pointing out below, might seem very fancy, but are essentials to use access (and other databases) efficiently. These are things I would have wanted to learn on day one, but was never taught, as I trialed and errored a lot myself. .

Back in the day, there was not to much around to learn from on the web. A good book like Access-2019-Bible . A paperback would be nice to glance through and make remarks in.

I totally get it. It's hard when you're starting from scratch, but I think that understanding the fundamentals of database design and then translating them into Access is a better approach than just trying to figure out Access without knowing what the background is.

I don't know if you have the time or budget for a book, but many years ago I read this book and it was basically what made everything click for me. I see there are newer versions out now.

I really learned a lot from an earlier version of this book.

Microsoft Jet Database Engine Programmers Guide (Microsoft Professional Editions) https://www.amazon.com/dp/1572313420/ref=cm_sw_r_cp_api_i_9-nYCbMFDEPZM

Pretty dry but still readable. A big focus on queries not excruciating detail of building your first table and form.

As you figured out, this isn't an SQL issue, it's a database normalization issue.

You wouldn't have a table of winners and a table of losers -- you'd have a table of teams, and a table of matches that links them together, and a table of players, and a table of stats. and you'd link them all together using various linking tables.

I used this book when learning about setting up databases.