General MS Access advice

[grabrail]

Hi, New to the forum and new to MS Access. I am looking for some advice on whether MS Access can do what I am wanting it to.

So, currently I have an Excel workbook set up with many macros for data collection, the workbook is effectively a vehicle inspection form, we have multiple engineers go out to site and perform a set of checks on a vehicle and record the results of each check within the inspection worksheet. There is a lot of information they need to gather.

Once they have completed the inspection, there is VBA set up to take all the data entered and move it to a seperate worksheet in the same workbook, which I will call DB for reference, in a specific format.

This is all done locally on the laptop/tablet the engineer has.

Once they are back online, the workbooks are copied to a central location (Dropbox) where another Excel workbook has VBA set up to import all the data form each workbooks DB sheet. Effectively creating a central Database of all the data from each inspection from each engineer.

This process works ok, but is a little clunky and I have been lokoig to move away from Excel to a proper database.

I've had a look around Access, and it seems like it woudl be possible to set up forms for capturing the data into the the database, but I am unsure on a few things.

1) If each laptop had Access running on it with the correct form set up so the data is captured locally, is it possible to synchronise the db from each laptop to a central DB hosted on our server? It needs to be this way as when the engineers are on site, most of the time they have no connection to the internet.

2) When setting up a form for data capture, how does the user run the form, do they need to open MS Access and then go to the form, or is it possible to have like an icon on the desktop that opens the form for them? so they effectively dont see MS Access, like can the form be run in a browser or similar scenario? The engineers we have are not IT literate and I need to keep it as simple for them as possible with any of the backend complications.



3) Can you create PDFs from forms to print out, as our engineers need to provide a PDF printout to the customer on site.

I'm trying to find out if this is possible to do, so I can determine if it is worth me learning MS Access. I am very tech savvy, and I can learn what I need to pretty quickly, but I've never had any exposure to MS Access previously, even though Ive workin in IT for 25+ years.

Many thanks in advance

[CJ_London]

1 - yes but need to be clear the direction the data is going and the nature of the data - all new data or amending existing data

2 - you an set the access file to open a specific form when it is opened

3 - yes, providing you have a printer on site - usually you would print a report to a printer or a pdf - might be easier to emails

You will find plenty of help on this and other forums on all of the above. Final solutions depend on your actual setup and requirements

[grabrail]

1 - yes but need to be clear the direction the data is going and the nature of the data - all new data or amending existing data

2 - you an set the access file to open a specific form when it is opened

3 - yes, providing you have a printer on site - usually you would print a report to a printer or a pdf - might be easier to emails

You will find plenty of help on this and other forums on all of the above. Final solutions depend on your actual setup and requirements

Great thank you, it sounds like what I need is perfectly possible with an Access application. Time to start learning

[CJ_London]

Recommend you state your requirement in detail - types of computers (windows/iOS) your network setup and the process you are considering. There is often more than one way to crack an egg and you need to be clear about the limitations of access, for example it won’t work via OneDrive or similar, a poor desin will have poor performance.

so don’t jump into a design until you have verified it will work with you setup.

access is not a bigger excel, it works in a completely different way. Easy example is excel combines data and results in one view which tends to be ‘short and wide’ whilst access has tables for data and results are determined in queries, forms and reports. Unlike excel, tables are ‘tall and narrow’

recommend google ‘database normalisation’ to understand the principles of good design

[Edgar]

1) If each laptop had Access running on it with the correct form set up so the data is captured locally, is it possible to synchronise the db from each laptop to a central DB hosted on our server? It needs to be this way as when the engineers are on site, most of the time they have no connection to the internet.
2) When setting up a form for data capture, how does the user run the form, do they need to open MS Access and then go to the form, or is it possible to have like an icon on the desktop that opens the form for them? so they effectively dont see MS Access, like can the form be run in a browser or similar scenario? The engineers we have are not IT literate and I need to keep it as simple for them as possible with any of the backend complications.
3) Can you create PDFs from forms to print out, as our engineers need to provide a PDF printout to the customer on site.

Additional:
1. Laptops and the server need to be connected to the same local network via ethernet for the sync. Could be WiFi, but data corruption is very likely in slow environments because Access is very sensitive to network issues.
2. You'll need to hide Access and let only a main form show, this form must have everything necessary to avoid your users from having to see the Access app.
3. Yes, you can create PDFs from your reports and other sources.

[grabrail]

Thanks all,

I have a good understanding of how databases are structured and writing and using queries etc. When I inherited the Excel Workbook, I totally redesigned it with the plan of moving to a database structure in the future, this is the point I am at now.

I watched a video last night showing how to configure a pull synch using 'copies' of the database, so my idea to move forward is as follows.

Design the correct structure of the Database to encompass the various elements of the data captured in the workbooks (Customer table, engineer table, site table, etc)

This will be the master DB, sat on our server. This data will only be used to populate something like PowerBI to create dashboards using the data

Each engineers laptop will have a copy of the master db, and will use random autonumbers as the PK to reduce the chance of conflict.

The laptops will have a front end form, which will be the inspection sheet that enters the data into their local copy of the database.

The idea will then be that once they have a network connection, they can put a copy of their DB into our shared Dropbox folder (They currently do this with the workbooks). Someone in the office can then have a daily task to take these copies of the DBs and using linked tables to each copy, perform the import to the master DB.

Its not a huge amount of data, and nogt a lot of engineers/copies of the DB that are required. We are a small company and run in a simple way.

I see that in the short term this approach would work, with a view to automating the process more as we move forward and deal with more and more data.

[CJ_London]

The idea will then be that once they have a network connection, they can put a copy of their DB into our shared Dropbox folder (They currently do this with the workbooks). Someone in the office can then have a daily task to take these copies of the DBs and using linked tables to each copy, perform the import to the master DB.

So to be clear, the engineers never need to receive any data from the master db? They are only ever posting to it?

Each engineers laptop will have a copy of the master db, and will use random autonumbers as the PK to reduce the chance of conflict.

i.e. once they have a copy, they never need any updates?

[grabrail]

So to be clear, the engineers never need to receive any data from the master db? They are only ever posting to it?

i.e. once they have a copy, they never need any updates?

Yes that is correct, Once synchronised with the master DB, the engineers will never need to visit that data again, the synch is one way (to the Master) only

[NoellaG]

You can use Access on the local laptops, but for the main DB where all data are brought together, I would consider to use a SQL database (SQL server, SQL server express, MySQL, ...).
Not only is it more stable and not easily corrupted on network failures, but it is more secure and with SQL server or SQL server express you can use the reporting service to design reports that can be viewed from a website or automatically e-mailed to your customers in the format you want.
Using the integration services you can easily design a package that imports data from the Access databases.

[saheransari]

Hello! It sounds like you have a well-defined process for data collection and consolidation..

[June7]

1. I had similar situations. Engineers in field enter data regarding project. End of project submit file to main office. Main office imports data to master database. I had to write code to accomplish import. In another case, contractor collected data and I had to merge multiple files to one master. More code. The situations were different due to how table relationships were maintained. In the first I used a text project number as key and in the second had to deal with autonumber keys.

2. I have customized database to hide/disable things like ribbon, navigation pane, shortcut menus, shortcut keys, X close, etc. Never went so far as to hide the Access app frame. Keep in mind that without additional coding, these settings can be bypassed with Access shift-key bypass when opening.

3. better to create PDF from report, not form

All can be coded. The more 'user-friendly' the more code.

[NoellaG]

All can be coded. The more 'user-friendly' the more code.

That's a bit the everlasting discussion betwee developers and DBA's. A lot can be done without coding, certainly in Access. Example: when using SQL server (express) as back-end, the only code you need in Access to start the data merge is a line that starts the SQL import, which can be done using a SSIS package without much coding. These days I rarely use Access, but I remember it to be a fun application creator because a lot can be achieved without coding.
As I read it, the laptop users are engineers, so I can assume they are tech savvy. Meaning, if you have a correct manual/training they can learn to use the build-in features of Access and know in general how to handle software/data.