Is this possible with VBA? (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 16:05
Joined
Nov 23, 2019
Messages
75
I'm thinking about a couple of new projects for work for use on site using laptops with touch screens or a windows tablet.

There are a couple of things I was wondering if they were possible or not.

1. If an access database is on a windows tablet & a form is being filled in would it be possible to have buttons on the form with VBA code behind them so once pressed the onboard camera can be used to take a photograph then import it into the form & then into a report once the form has been completed?

2. All the databases I have made have split front & backends & backup the backend using VBA on closing, what I was thinking was is it possible to complete a form on a database while on site on a laptop/windows tablet, once the form is completed for the day use a button with VBA behind it backup just the entry on the table rather than the whole backend so it can be emailed to the office & the table entry from the site laptop/tablet be imported into a table on the database there, be corrected if required?

Any help, links to something simular or VBA code would be appreciated.

Thanks in advance for anyone taking the time to read this & for any help
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 16:05
Joined
Jan 14, 2017
Messages
18,186
1. Yes - provided you know how to control the camera using vba. You should be able to find suitable code with a forum or Google search. I've never need to do this so don't have example code
2. Definitely Yes but it might be easier just to sync the data when the tablet is next connected to the network
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:05
Joined
Feb 28, 2001
Messages
26,999
A quick search for "vba control laptop camera" gave me over a dozen articles. Reading through some of them, the issue seems to be that the webcam driver needs a special library and for some folks, that is a sticking point. Some of the offerings are share-ware, others are commercial. Didn't see one that did it straight out, but I also didn't look at all of the articles.

You are describing an "incremental update" process, which is quite common among larger systems. The trick will be mostly figuring out the format of how you want the data sent. If there is no privacy concern, you could just build information as part of the text body of the mail message. Send it on one end, receive it on the other. Note: That probably implies using Outlook as the message receiver since Access doesn't play with other protocols and I don't recall that CDO reads SMTP directly. CDO writes it - but I don't recall that it reads SMTP.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:05
Joined
Feb 19, 2013
Messages
16,553
just to add to Colin's comment
2. Definitely Yes but it might be easier just to sync the data when the tablet is next connected to the network
potential issue is that tablets have a (user) habit of being connected wirelessly - which is not recommended for Access. So if syncing, ensure it is wired to the network.

Years ago before fast internet I developed apps along the lines you require, users were at an event or literally out in a field, no connection possible. If the data requirement was 2 way - user sends activity and receives updates then better to sync when connected to the network. Otherwise, if one way (user activity only) then download data to a csv file (recommend not excel. Doc makes a good point about data security so encrypt the data) and email to a specific email address. Master app can monitor the inbox for new emails and process as required.
 

PaulD2019

Registered User.
Local time
Today, 16:05
Joined
Nov 23, 2019
Messages
75
Thank you for all your replies, I could look at syncing the data when connected to the network for one of the databases I was thinking of but it wouldn't work for the 2nd as to get the data each day our staff would have to come back to the office each day which is one of the things I need to avoid, we have one of our supervisors who lives a long way from the office & is carrying out work near where he lives & another who travels from home to site by train.

I haven't got a windows tablet yet but I could look at x86 if it helps
 

isladogs

MVP / VIP
Local time
Today, 16:05
Joined
Jan 14, 2017
Messages
18,186
It doesn't make any difference whether the tablet is x86 or x64 but it must be Windows in order to run Access.
I have used both with Access. I have several applications designed to run on tablets including apps designed for 'field use'.
Syncing the data safely is definitely feasible

As long as the tablet can be connected to the internet with an Ethernet cable, the user does not need to physically be in the office. Using remote desktop connection or something like Terminal Services, the data can be synced safely. If a hard wired connection isn't feasible, then do a data transfer in two stages
1. From tablet to local computer using WiFi. Make sure the local computer is hard wired
2. After verifying that the data is still intact, transfer from the local PC to the office network using RDP or similar.

If that's not practical, then the user will need to email the data securely and someone else will need to do the transfer safely.. Avoid that if possible.
 

isladogs

MVP / VIP
Local time
Today, 16:05
Joined
Jan 14, 2017
Messages
18,186
There was an issue with Surface Pro tablets running on ARM processors which did limit the range of Windows apps that could be run. That really was a own goal by Microsoft. Whether that affected Office programs such as Access I've no idea. However, the issue was apparently fixed last year Windows on ARM is about to get lots of apps thanks to new x64 emulation - The Verge

I have been using much cheaper Windows tablets made by Linx for the past four years or so. These all have Intel Atom CPUs and all run Office/Access successfully - both 32-bit and 64-bit. I have the full version of Access on the tablets as part of my Office 365 subscription but runtime might well be appropriate for the OP
 

wvmitchell

New member
Local time
Today, 09:05
Joined
Sep 4, 2020
Messages
24
Have you considered using Remote Desktop Services? You could host the app on an office PC and then your remote users would log in and make their entries & updates in real time. They could even use wifi because the actual program is running on the host.
 

zeroaccess

Active member
Local time
Today, 11:05
Joined
Jan 30, 2020
Messages
671
There was an issue with Surface Pro tablets running on ARM processors which did limit the range of Windows apps that could be run. That really was a own goal by Microsoft. Whether that affected Office programs such as Access I've no idea. However, the issue was apparently fixed last year Windows on ARM is about to get lots of apps thanks to new x64 emulation - The Verge

I have been using much cheaper Windows tablets made by Linx for the past four years or so. These all have Intel Atom CPUs and all run Office/Access successfully - both 32-bit and 64-bit. I have the full version of Access on the tablets as part of my Office 365 subscription but runtime might well be appropriate for the OP
"X64" isn't really a thing; x86-64 is the actual name. So an Intel Atom would be fine as it's x86.

But if we're talking about ARM devices, like the kind that run iOS and Android, then you'll need to use the Runtime version of Access which should work in most cases except for some databases which it will baulk at. Just wanted to make that clear for the OP. He intends to use "Windows tablets" which is ambiguous and could mean either variant.

If it's newer and not one of the Windows RT types, there is a better chance:

"Using WOW64, an x86 emulator, ARM chips can run 32-bit apps, but not all 64-bit versions. The Surface Pro X can natively run 64-bit apps but only those that have been ported to ARM64, an evolution of ARM that supports 64-bit processing.

Confused? Check out the fine print on Microsoft's product page for the Surface Pro X:

"App availability and compatibility may vary. At this time, Surface Pro X will not install 64-bit applications that have not been ported to ARM64, some games and CAD software, and some third-party drivers or anti-virus software. New 64-bit apps are coming to ARM 64 all the time."

 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:05
Joined
Apr 27, 2015
Messages
6,281
Lol. How did you know it was you whom I was referring to?!?

...it was, but still...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:05
Joined
Feb 19, 2002
Messages
42,970
& backup the backend using VBA on closing,
This is really not a good idea in a multi-user environment. Backups should be scheduled and done preferably when the BE is closed. The BE should be compacted regularly also and that should also be done via a schedule. You can use a tool like the one on www.fmsinc.com or you can use windows scheduler to schedule your batch tasks. It is best to run these batch tasks on a server that is always on rather than relying on a desktop which may or may not be on/functional when it is time to run the task.

To sync a remote system with the "home base" really depends on what you want the satellite system to be able to do. If you are running a kiosk, you might not really want it to update client records. Maybe you just want to let it "log in". When you allow data updates both at the "home base" and at a satellite, you have the problem of determining which takes prescience. The satellite can flag a record as update or new which gives you a clue. But you also need to know the update date/time. And even then, you can never be sure that all the columns should be updated. You would probably have to log a last changed date/time for every single column. That way you would use the "timestamps" to determine differences. If the timestamps don't match, you would determine which one is newer and update the master copy to that value. If you have done this process by connecting the two databases, you can make the sync work both ways but that would be time consuming because you would have to process every field on every record to determine what needs updating unless you also keep a record last updated timestamp so that every time a record is updated, the record timestamp is changed. So the field timestamp is logged in the Control's BeforeUpdate event and the record's timestamp is logged in the record's BeforeUpdate event. If the syncis asynchronous meaning that the two databases are not actually connected but the satellite sends an email or writes to a temp file which is processed later, then the updating can't be bi-directional and you need a different method to update the satellite with new data.

All in all, I do not recommend allowing remote updates. The complications of syncing are major. You can use the satellite to add rows and that is the best/simplest thing to implement. Then you need to do batch updates to the satellites on a scheduled basis.

The one app where I needed to do stuff like this was for a client survey. The nurse/case manager would visit the client's home and ask a series of questions. The old method was to write the answers on a paper form and key them in when the case manager got back to the office. The new method is to have the cm do the data entry while doing the interview. This minimizes data transfer errors. Because we couldn't rely on the client's internet connection, this was done off-line. Every week the cm would set up her schedule for the following week and download the last survey answers so they could be shown side-by side with the new answers giving the cm an indication of change at the time the question was asked so she could make notes about the change. Then when she got to the office or connected from her home office, the New survey would be uploaded and added to the master database. Part of the weekly set up was to archive last week's schedule just in case it wasn't properly sync'd.
 

Users who are viewing this thread

Top Bottom