Is Microsoft Access the right solution? if yes, what next? if no, what option?

[ctackett6407]

Greetings Folks,

I'm trying to come up with a solution at work that will help a few departments. I do not have anyone to bounce ideas off of as I am the only "me" in the company and my knowledge is well intentioned, at best.

I'm trying to take away from me manually going into a tool one department uses called Smartsheet, and exporting two of their sheets from excel spreadsheets to .csv files every morning and then going into a web based ordering system we use and exporting two .csv files. I then drop them into a Tableau folder and go through and manually refresh every single report everyone uses. I've been told that Tableau cannot automatically update .csv files so I have to go in and manually update them and publish them so everyone can use them by 6am.

Anyway, one department works specifically in Smartsheets and the other team specific works in the web based portal. The web based portal has the ability to use a web hook, but that's about it and Smartsheet can use a web hook and that's about it. I found that I can use something called Zapier for Smartsheet.

Anyway, Microsoft Access..

I was hoping to try to create a application in Access that would allow either team to go in and look up orders and work orders and not have two separate worlds. I'm more focused on the team using Smartsheet, but feel the other team can benefit from a "read only" tool after the fact.

I'm curious if Access is able to have files dropped into a folder and it refresh automatically. I'm trying to see if any changes made would stay and avoid being updated when the excel file is refreshed, if that's even possible.

Any thoughts?

Right now I'm trying to learn everything under the sun. It's a new area to me but I'm very determined to figure all this out.

 

[petertenthije]

I am not entirely sure on your intentions.

Do you want your team to use Access as read-only, or should they be able to make changes? The former is easy. You can link Access to the CSV file, or even to the original Excel source file to skip a step.

Making a version that allows users to change data takes a bit more. You would have to import the data into Access and keep the changes there. Otherwise you will have multiple people trying to edit the same Excel file. Excel is not really designed to be used this way, whereas Access is.

You could also change the source file. Instead of using Excel as a source, make Access the source and couple everything to Access. But that depends on what the Excel file is doing with regard to calculations etc.

 

[ctackett6407]

Sorry about the confusion. I get lost in my own words when I'm not careful.

ideally, I would like the main department to be able to go into an application and "work" orders. Assign them to to contracts, change their status, make notes, cancel them, update customer information etc.

The problem is, for now, that I cannot update directly from the sources of the information.. ie Smartsheet or CrowdFiber. Smartsheet houses the work that the contractors and that specific team are working on and CrowdFiber is the registration site where orders are submitted by registrants.

I take 4 different excel and csv files and have a primary key that joins the customer and address files (address id) and then the customer file joins the two smartsheet files with the customer ID.

I go in and download them all and convert the two excel files to csv and then have to manually refresh everything. I'm looking into data connectors for Smartsheet and CrowdFiber but they do not have any that can read/write data.

I am not entirely sure on your intentions.

Do you want your team to use Access as read-only, or should they be able to make changes? The former is easy. You can link Access to the CSV file, or even to the original Excel source file to skip a step.

Making a version that allows users to change data takes a bit more. You would have to import the data into Access and keep the changes there. Otherwise you will have multiple people trying to edit the same Excel file. Excel is not really designed to be used this way, whereas Access is.

You could also change the source file. Instead of using Excel as a source, make Access the source and couple everything to Access. But that depends on what the Excel file is doing with regard to calculations etc.

 

[Micron]

Access can use a number of sources as raw data (csv, linked spreadsheets, imported spreadsheets, Oracle (etc.) databases and the like) and format/group it in ways that make sense. Then you can present this data for others to read only, modify or delete as required. One thing you mentioned I'm not sure how to interpret if Access is able to have files dropped into a folder and it refresh automatically . Not sure if you realize that there is no such thing as saving an Access db like you would a workbook. When you make data changes in Access, there is no cancelling by closing a db without saving the file like there is for Word or Excel, so it contains the latest info.

There are several considerations to be made in deciding if you should migrate to Access. There is no more web support for Access and you do NOT want to use it over wifi or open and edit on the cloud (e.g. One Drive) so accessibility may be an issue. The usual answers I see given for this is remote access using something like Citrix or Terminal Services. You could potentially upload db data into a web based server, but if they are allowed to edit, you will now have disconnected and diverse sets of data. Another approach is to use Access as a front end that collects and mashes data but use a sql server type of back end, which allows more flexibility in terms of accessing data from other points. You might want to formulate some questions and do some research based on those and see what you get; e.g. Can I use Access remotely? Can Access publish data to the web? It sounds to me like you have a lot of research to do that's specific to your situation. I'll end by saying that the efficiency of a task can sometimes be improved exponentially by Access if you think it through. I once developed a small app that reduced a 3 man 6 hour task to 10 minutes - and no, no one lost their job. However, the salaried guys no longer had to work overtime on the weekend either, so I heard no complaints.

 

[ctackett6407]

I can't take a day off because I have to keep manually updating reports for people, or I can't take full day off because I still have to log in in the morning and update things or I get a ton of emails pointing out the data is wrong.

So I'm trying to come up with something. I'm living on a few web sites learning everything I can about coding languages, trying to learn API's and Web Hooks, and I know Access can probably do something for me, I just have to learn it. I'm taking an online course for SQL and Python.

I'm probably making $2.00 an hour with all the time I spend learning and working. I'll get there though!

Access can use a number of sources as raw data (csv, linked spreadsheets, imported spreadsheets, Oracle (etc.) databases and the like) and format/group it in ways that make sense. Then you can present this data for others to read only, modify or delete as required. One thing you mentioned I'm not sure how to interpret if Access is able to have files dropped into a folder and it refresh automatically . Not sure if you realize that there is no such thing as saving an Access db like you would a workbook. When you make data changes in Access, there is no cancelling by closing a db without saving the file like there is for Word or Excel, so it contains the latest info.

There are several considerations to be made in deciding if you should migrate to Access. There is no more web support for Access and you do NOT want to use it over wifi or open and edit on the cloud (e.g. One Drive) so accessibility may be an issue. The usual answers I see given for this is remote access using something like Citrix or Terminal Services. You could potentially upload db data into a web based server, but if they are allowed to edit, you will now have disconnected and diverse sets of data. Another approach is to use Access as a front end that collects and mashes data but use a sql server type of back end, which allows more flexibility in terms of accessing data from other points. You might want to formulate some questions and do some research based on those and see what you get; e.g. Can I use Access remotely? Can Access publish data to the web? It sounds to me like you have a lot of research to do that's specific to your situation. I'll end by saying that the efficiency of a task can sometimes be improved exponentially by Access if you think it through. I once developed a small app that reduced a 3 man 6 hour task to 10 minutes - and no, no one lost their job. However, the salaried guys no longer had to work overtime on the weekend either, so I heard no complaints.

 

[petertenthije]

Rather than redesigning half the company’s excel files... you might want to look into automating your tasks.

Windows has a build-in feature that will automatically run scripts. It’s the Taskplanner. It calls up a VBS script.

This VBS can be used to open an Excel file, and run a macro.

If you can use Excel macros to perform your daily updates and corrections, then you can automate your job. Only requirement would be that you’ve got a computer running.

You can even build in automated email, so you can get a notification when the work is done (or if it has crashed ).

 

[ctackett6407]

I'll look at that. I keep finding more and more resources that I can try to use and then trying to learn about each one. I'm starting to make a list and just going down one at a time.

I really appreciate the help!

Rather than redesigning half the company’s excel files... you might want to look into automating your tasks.

Windows has a build-in feature that will automatically run scripts. It’s the Taskplanner. It calls up a VBS script.

This VBS can be used to open an Excel file, and run a macro.

If you can use Excel macros to perform your daily updates and corrections, then you can automate your job. Only requirement would be that you’ve got a computer running.

You can even build in automated email, so you can get a notification when the work is done (or if it has crashed ).

 

[jay_py]

First time hearing about Smartsheet, so I did a little research. It turns out, they have a Python library for interacting with the software. Of course, nowadays every software has an API for Python...

You can try to reach out to CrowdFiber see if they also got an API for Python.

I'm not sure what role Tableau plays in your original post, but you can also use Python to automate Tableau process (check out Tableau Tools library).

So Python is your answer!