NEWBIE - Access 2016 order Inventory database with forms

[PinkfudgeDebs]

hi,
Could really do with some help and advice in 'talk for dummys' please? (Only been using Access for a month)

Have used the Product Inventory Database in Access 2016 and tailored it to my requirements. (small business)

I have three employees that I need to be able to access the Dashboard and input data via the Forms (New product, New Supplier etc) and be able to view updated product, inventory, supplier, shipment data etc but do not want them to make design changes.

Current Equipment - BT Hub 5.0 + ADSL/FTTC, have no networked folders nor do we have a server.

Could someone please explain to me what my options are (and if possible how)?

Ideally would prefer my small team to be able to access dashboard/forms form the web (or even our a subdomain of our website (Hosted with One.com) if any of this is possible/

Have looked through the MS Access support instructions on splitting databases.....then uploading to SharePoint etc, however I then appear to get conflicting MS Support info saying this is now not possible?!

Am completely confused! Am more than prepared to put in the time and effort to learn my way through this, but, like I say, if I could be steered in the right direction with a little help please?

Once again. my sincere apologies for my lack of knowledge folks.
Any help and advice truly greatly appreciated!
Debs

[CJ_London]

but do not want them to make design changes.

best way is to save the file as a .accde. Or remove the full version of access from their machines and provide them with access runtime (which is free)

Ideally would prefer my small team to be able to access dashboard/forms form the web (or even our a subdomain of our website (Hosted with One.com) if any of this is possible/

not possible. With multiple users your database must be split and you can locate the data (the backend) on the web (sharepoint/sql azure/amazon web services)

then uploading to SharePoint etc, however I then appear to get conflicting MS Support info saying this is now not possible?!

Access web services (which had a html front end/sharepoint backend) is being discontinued but so far as I am aware, you can still connect to sharepoint lists using a client based access front end. However sharepoint is not ideal, would recommend sql azure.

If you want help, please be clearer about your current setup. Does each employee have their own machine? are they in the same office? can they share a folder for the backend? Do they work from home/on the road? Are they all using windows? do they all have the same version of Access? Are they all on 32bit or 64bit office (not windows, that a given these days)? or a mixture? Do they all have similar sized screens? How important is uptime, or to put it another way, how is your business affected if there is a power outage/loss of broadband?

[PinkfudgeDebs]

best way is to save the file as a .accde. Or remove the full version of access from their machines and provide them with access runtime (which is free)

not possible. With multiple users your database must be split and you can locate the data (the backend) on the web (sharepoint/sql azure/amazon web services)

Access web services (which had a html front end/sharepoint backend) is being discontinued but so far as I am aware, you can still connect to sharepoint lists using a client based access front end. However sharepoint is not ideal, would recommend sql azure.

If you want help, please be clearer about your current setup. Does each employee have their own machine? are they in the same office? can they share a folder for the backend? Do they work from home/on the road? Are they all using windows? do they all have the same version of Access? Are they all on 32bit or 64bit office (not windows, that a given these days)? or a mixture? Do they all have similar sized screens? How important is uptime, or to put it another way, how is your business affected if there is a power outage/loss of broadband?

Hi Ajax,
Firstly, just wanted to say THANK YOU!

Understood everything you said, (mostly) huge thanks for explaining in simple 'dummy' terms, you have no idea how much it helps!

Further info as requested:
We are a small CMT Unit, Athleisure & Performance Wear Design & manufacturer of 4-way stretch garments.

Current Studio setup:
1 x Desktop - Windows 10 & Office 365 - 32 bit (am looking to upgrade this asap, but unsure as to what to upgrade to) - used for vinyl cad cut software, emails, Excel dispatch notes
1 x Laptops - Windows 10 & Office 365 - 64 bit (sometimes work from home) - Mostly Adobe Illustrator, emails, Word, & internet research
1 x Laptop - Windows 10 & Office 365 - 64 bit (mostly works from home but soon to be more studio based) Sage, Excel, Word, email, admin for website
1 x Laptop - Windows 10 & Office 365 - 64 bit - me, I predominately work from home - Access, Excel, Website work, Brackets etc, Corel Photoshop, Adobe etc

All similar sized screens & all access Web based CRM system

Not really an issue if there is a power outage as mostly during the day its manufacturing.

Really hope this helps.
Thanks again Ajax.
Debs

[CJ_London]

OK, so your machines are basically distributed.

With regards your desktop with 32bit access, if you are not using this for your access app, then not a problem from that perspective. 64bit office is only of benefit to excel power users (handling millions of bits of data) it has zero benefit for access, word, outlook, powerpoint etc. Also, there are a number of specialised functions and form controls across these apps (which you may or may not ever need) which work in 32bit but not in 64bit. Whatever you decide, it is better if everyone is on the same 'bit' version so you don't have to maintain more than one app. My personal recommendation is if you are not an excel power user, stick to 32bit but that may not be an economic choice for you.

The reason for talking about power outages is access (backend where the data is, not the front end where the code is) doesn't take kindly to interruptions, whether they be power or the connection (i.e wireless) - there is a risk that it is halfway through a transaction (updating/appending/deleting) when it loses some of the data, leaving an incomplete transaction which results in potential corruption. So given the fact you have a distributed user base, you will have to host your backend on the web and use something like sql azure which will handle this much better. This doesn't make access a bad tool, it just needs to be used in the manner intended.

A potential problem with sql azure is its firewall. This is based on only allowing access to known IP addresses (as admin, you provide them). This is not a problem if you are only working in known places (home, office) where you can arrange with your broadband provider for a fixed or static IP. Most do this for a small charge. The problem comes if you want to work from a coffee shop/hotel/a client where the IP address is not known and also used by many others, and may not even be fixed. Yes you can open it up and put in a range of IP's, but you are then potentially letting the world in, which is not what you want. Another way is using what are called push and pull API's which gets round the fixed IP firewall but you do need good vba skills to implement and build your app with this in mind.

One of the things you could look at doing is using remote desktop - all win 10 machines will have it as an app. You install your access app on your desktop machine and allow your colleagues to access it remotely (again use IP address as firewall) or look at using something like Jump Desktop or Teamview which does much the same thing but has a different security protocol. The user effectively open a window on their machine which is the desktop of the target machine from where they can run apps etc. as if they were sitting in front of it. The only thing being communicated between the two machines are key presses and mouse moves one way and screen refresh data the other. The downside is only one person can be connected at a time - so depends on your working practices - they can try again later.

Don't bother investigating using facilities such as Dropbox - it is totally unsuited to using access and simply will not work for multiple users.

One word of caution - if you are involved with the business and your skillsets lie elsewhere, do consider contracting someone to do this work for you. The number of times I have see a business owner get sucked in to developing an app and neglecting their business as a result - in one case so much so, their business failed. They will probably take 10 times as long, no doubt produce flaky code and then still have to pay someone to sort it out. Like anything these days, there is rapid change in the development world and it is difficult to keep up unless you specialise.

Hope this helps with deciding which way to go.

[ArviLaanemets]

The downside is only one person can be connected at a time - so depends on your working practices - they can try again later.

With the team being so small, the possibility OP has terminal server installed is probably zero?
With Terminal Server, every user connects to his own TS profile using remote desktop client - and it is possible to allow such connections from anywhere. Of-course to buy a TS licence for only 4 users is overkill, and I don't know, are there free and trustworthy alternatives available.

Edit: An afterthought - is it possible to rent online TS Service for n users like you can rent ordinary server?

[CJ_London]

With the team being so small, the possibility OP has terminal server installed is probably zero?

the other option is Citrix

[ArviLaanemets]

the other option is Citrix

as much as i understood from Wiki, it is more like RDP, not like TS. It allows to connect to server in your network, but when you have 64-bit Office on your laptop, and the back-end in your network is made in 32-bit Office, then this doesn't help. With TS, you install 32-bit Office on TS, and store for every user front-end in his-her profile (p.e. on desktop, or in My Documents). The user connects to TS, starts his/her front-end in 32-bit Office (even when he/she has 64/bit Office installed locally), and works without problems. Additionally, the only case when database can be corrupted because broken connection will be, when whole server room shuts down.

[CJ_London]

you do the same for citrix

[PinkfudgeDebs]

Hi Ajax,

THANK YOU! I understand completely your advice and once again, thank you for putting it into 'Dummy Speak' so that I could digest it.

Your advice has most definitely helped move my situation and dilemma forward and of which I am truly grateful.

With reference to 'contracting someone else' to undertake the technical side, wise words indeed!......and advice I will of course take.
My sincere thanks to all of you who were gracious enough to take the time to help and advise (although I must confess did not understand some of the technical 'speak')

Ajax..... THANK YOU!!!!

[ArviLaanemets]

Btw, Microsoft Small Business Server 2016 can manage up to 5 active remote desktop clients at same time.