Visitor registry with signatures in access ?

[MasterBash]

Hello !

First time using Access, trying to learn it as we speak.

I have a small project that I am trying to create. It is a form for visitors to fill when they come in. One point of entry and that form requires a signature that needs to be stored. We need to be able to keep the records for 3 years.

Now, I started with excel, I am attaching the workbook if anyone is interested to see what I am trying to accomplish in Access : registry_test.xlsm

I am trying to create a form with multiple fields and some are required : First name, last name, company, Time of arrival (should be filled in automatically when visitor starts entering data) and signature. We will be using a graphical tablet for this. I also created an e-mail tab in my workbook, so the subject, body and email address can easily be modified. Is that also possible in Access ? Maybe easier ?

Now, I am new to Access and I really don't know its full capabilities. I need to be able to store the visitor's signature (with the other data). When the form is filled, it should be sent by E-mail with the "Submit" button, but also create a new row in the table to be filled when a new visitor comes in. Would my excel macros transfer well to Access or not at all ?

Before I put a lot of energy into learning Access, are those things above possible ? The signature will done with a finger or a touch-screen compatible pen. In excel, those appear as shapes. I started in Excel, but I am open to using Access if it is possible to accomplish what I am trying to do.

After the project is done, I also have to lock stuff up so visitors don't have access to other visitor's data (the database). I want the form to be full screen and I will be disabling some keys (Esc...) so visitors do not try to do unwanted things. The team will have access to the file (Shared between mulitple people and should be accessible on OneDrive), as we will have to manually add Time of Departure ourselves and we will need to see some other information.

Thank you ! Looking for input and how to get started.

[June7]

Excel macros (actually VBA code) would not transfer easily to Access. Processing logic may be same but references will be completely different.

Signatures would be an image file. Images can be saved in Attachment type field. However, saving images into file (whether Excel or Access) increases file size and can slow performance. Depends how many records will accumulate. Access has a 2GB file size limit. Alternative is to save signature file in folder and save filepath to text field.

Locking stuff would be an issue in Excel or Access but maybe easier in Access to restrict user privileges.

I am not certain about sharing file on OneDrive. Will multiple users interact simultaneously? OneDrive is not feasible for that scenario. A multi-user database should be split design with each user running frontend on their local machine linking to backend which should be on a file server.

Data management is easier in Access.

Device must run Windows.

Review https://www.youtube.com/watch?v=m3IMLn11j90

[MasterBash]

Excel macros (actually VBA code) would not transfer easily to Access. Processing logic may be same but references will be completely different.

Alright, I will be reading about references.

Signatures would be an image file. Images can be saved in Attachment type field. However, saving images into file (whether Excel or Access) increases file size and can slow performance. Depends how many records will accumulate. Access has a 2GB file size limit. Alternative is to save signature file in folder and save filepath to text field.

If it is saved in the same folder with a link to the filepath (automatically), then that is okay.

Locking stuff would be an issue in Excel or Access but maybe easier in Access to restrict user privileges.

From what I read, it shouldn't be very hard to lock an Excel file and force it to run full screen/disable shortcut keys. No idea about Access - I will read more about it.

I am not certain about sharing file on OneDrive. Will multiple users interact simultaneously? OneDrive is not feasible for that scenario. A multi-user database should be split design with each user running frontend on their local machine linking to backend which should be on a file server.

Multiple users will have the file open, but they will rarely interact with it.. Entries will come from the graphical tablet and visitors will fill the information. The only thing we will be doing is entering the "Time of departure" and maybe correcting some mistakes that visitors make while filling the registry. This can be done on the pc (graphic device plugged to a windows pc). I will ask the IT teams what alternative do we have. I know we also use SharePoint...

I wonder if that is one of the reasons why we keep using Excel, even for databases, because it is easy for multiple users to interact with it.

Data management is easier in Access.

Device must run Windows.

Review https://www.youtube.com/watch?v=m3IMLn11j90[/QUOTE]

Agreed and the device will be running Windows.

[June7]

By references I mean instead of worksheet and cell referencing, in Access would be referencing tables/queries and fields and textboxes.

Multiple users cannot simultaneously open and edit same Excel file.

[Gasman]

Multiple users cannot simultaneously open and edit same Excel file.

Well they can @June7, we used to do it in Lloyds Bank, however they do get corrupted easily.

@MasterBash, all you have asked, I believe is available in Access.

See here for an example of signature capture.

[June7]

Okay, I should have qualified my statement to say "without possible conflicts and corruption when saving".

Same goes for an unsplit Access database.

[MasterBash]

I will definitely be splitting an Access database. Tomorrow, I will contact my IT team to see what solutions we have. I believe we only use OneDrive and SharePoint. I don't think they will allow me to use anything else for the backend, and unfortunately, it does not work with OneDrive.

What are the most likely cause of Excel file corruption ? User error ? I must say that I have been lucky so far, but I had no corruption so far. Is there a way around that ? I suppose the enterprise system-wide backup would fix it, or a daily backup of an individual file or something. The most annoying problem I have seen, is that changes appear to not update when when using the windows version of the app instead of using it online.

[MasterBash]

After talking to my IT team, they want me to use excel, because it is easier to share and other than OneDrive and SharePoint, I can't use anything else.

That is unfortunate. Oh well, that narrows down my options. I will mark this thread as solved and I will use the excel thread.

Thank you for your input.

[Gasman]

Be prepared for constant corruption then.

We used to work on different rows each, and still constant corruption.

The annoying thing was that you would pin the filename in Excel, and the team leaders would name the new copy 1, 2, 3, 4, instead of renaming the corrupt one and leaving the live one with the same name.