Migrate Access DB to browser-based on local network (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
Hi All,

I don't know how many times (over the past couple of years) I have written the next sentence (lol): I work for a steel manufacturing company in QC. I developed an inspection database in Access 2016 to collect our daily inspections and provide reporting on activities. The inspections take place on the floor via a 12" rugged tablet.

Well...I am happy to report that it has been a success. So much so that President of the company wants me to continue developing to include our other two plants and also add several more upper-management folks to the list of end users (mostly for reviewing reports).

Here's the thing, not everyone has MS Access installed on their computers and it is my understanding that the free viewer is no longer available and....and ....and

Which led me to looking at alternatives. It didn't take me long to come across the idea of a browser-based solution.

Fortunately, I am not unfamiliar with HTML, CSS and front end design although translating the Access tabbed forms I currently have will probably take some mental backflips. I also link the inspection forms to a library of part drawing files in pdf that can be recalled instantly. It's pretty slick if I do say so myself.

So, I'm trying to figure out what the best stack will be for me to undertake this new project.

Some of you may recall that the business rules here lead me to having an almost EAV type table design which I ended up not doing because the queries were going to be so cumbersome. I ended up with a hybrid model that works very, very well although I have the types of inspection spread across several tables that all share a master linking table.

Somewhere in the past couple of weeks I read quite a bit about JSON files and light bulbs started going off in my head that that might be a great way to capture inspection data that is sometimes minimal and sometimes many, many fields.

My few opening questions then are:

1) am I barking up the wrong tree thinking JSON datatype would help me achieve a more robust approach to the myriad type of inspections that need to be performed;
2) If so, what would that stack look like?;
3) If not, what other stack(s) should I be looking at?

I've been thinking MySQL, Python, and Angular (what am I missing?). Or MongoDB alternatively but a good bit of my DB is traditional RDBMS - although it appears that would be fairly straight forward to migrate into a new model in a robust environment.

I've attached a snip of my current table structure. There are several more look up tables but they're fairly extraneous to the current discussion although they would be needed. I think it is fairly obvious that I have some redundancy in the inspection table structure. That's the area I'm wondering if the JSON datatype would be beneficial and collapse all of those inspection tables down to one.

I'm looking forward to your thoughts.

Thanks,

Tim
 

Attachments

  • Capture.JPG
    Capture.JPG
    148 KB · Views: 286

plog

Banishment Pending
Local time
Today, 17:03
Joined
May 11, 2011
Messages
11,612
JSON is a data interchange format, it's purpose is to move data from 1 system to another. It sounds like you are building a completely new system which wouldn't require the movement of data--the data would live within that 1 system itself. So at first blush, yes JSON isn't something that is relevant.

Every organization today has an IT department--do you? I mean this whole thing sounds like it falls under their domain, not some guys in QC. Even if your organization doesn't have a full IT department there must have someone who does some coding or has some experience in building a real data system. Even if they aren't to build it, I would ask them what they think and what they are familiar with. That way if you do end up building this you have an easily accessilbe resource to go to when you encounter issues.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:03
Joined
Oct 29, 2018
Messages
21,358
... and it is my understanding that the free viewer is no longer available
Hi. I won't touch, just yet, the topic about creating a web front end for your db, since I'm sure there'll be plenty of discussions still coming on that topic. I am more interested in what you said in the quoted part above. Where did you hear that? I don't think it's true.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
Hi. I won't touch, just yet, the topic about creating a web front end for your db, since I'm sure there'll be plenty of discussions still coming on that topic. I am more interested in what you said in the quoted part above. Where did you hear that? I don't think it's true.
There's more to the story - of course - which also speaks to what plog said above also.

I work for a company that is only very recently aware that it should move out of the 1980's. We have one IT guy and he is obsessed with network security to the point where our internet browsers are almost useless at work. He will be of no help to me until after I design this thing - on my own - which I have time and reason to do (without belaboring that much further).

As far as the viewer is concerned, yes, I know it is still available. My bad - my frustration with the IT person who is most definitely anti-Access though I don't know why. He attempted to install the viewer on a QC computer, had one glitch and trashed the entire idea. I'm really self-editing here as expletives are probably not allowed :).

To be really, really honest with you.....I want to do this 1) because it is a good idea; 2) because I am able (even though I'm rusty), and 3) because I am looking for a way to - shall we say - transition away from the 1980s as it were.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
JSON is a data interchange format, it's purpose is to move data from 1 system to another. It sounds like you are building a completely new system which wouldn't require the movement of data--the data would live within that 1 system itself. So at first blush, yes JSON isn't something that is relevant.

Every organization today has an IT department--do you? I mean this whole thing sounds like it falls under their domain, not some guys in QC. Even if your organization doesn't have a full IT department there must have someone who does some coding or has some experience in building a real data system. Even if they aren't to build it, I would ask them what they think and what they are familiar with. That way if you do end up building this you have an easily accessilbe resource to go to when you encounter issues.
Hi plog,

You would be correct for any modern company. I do not work for a modern company. When I started here four years ago there was nothing recorded digitally. It was still ALL paper in 2018. We have one IT guy and he is....recalcitrant. Please see my reply to DBGuy for moore. :)

Thank you!
Tim
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
So......yeah........some guidance to assist/direct me in what I know will be a year or two project to add a by-line or two to my resume would really be appreciated. :)
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
JSON is a data interchange format, it's purpose is to move data from 1 system to another. It sounds like you are building a completely new system which wouldn't require the movement of data--the data would live within that 1 system itself. So at first blush, yes JSON isn't something that is relevant.

Every organization today has an IT department--do you? I mean this whole thing sounds like it falls under their domain, not some guys in QC. Even if your organization doesn't have a full IT department there must have someone who does some coding or has some experience in building a real data system. Even if they aren't to build it, I would ask them what they think and what they are familiar with. That way if you do end up building this you have an easily accessilbe resource to go to when you encounter issues.
I should also add that we do have an MRP designed/built by an outside source but after a few years of using it, upper management realized that they got what they paid for by not taking part in the very beginning and spending the time with the designers to make sure that business rules were taken into acccount and now they have an expensive MRP that only kinda sort does the job. No - I'm not leaving this up to anyone else.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
JSON is a data interchange format, it's purpose is to move data from 1 system to another. It sounds like you are building a completely new system which wouldn't require the movement of data--the data would live within that 1 system itself. So at first blush, yes JSON isn't something that is relevant.

Every organization today has an IT department--do you? I mean this whole thing sounds like it falls under their domain, not some guys in QC. Even if your organization doesn't have a full IT department there must have someone who does some coding or has some experience in building a real data system. Even if they aren't to build it, I would ask them what they think and what they are familiar with. That way if you do end up building this you have an easily accessilbe resource to go to when you encounter issues.
And I have a background in database management from college and working for a private contractor for the Navy out of college. (yes a long time ago but nonethless). That's why I sometimes sound like I know more than I do but I'm also aware that I'm over a decade since I had my hands and feet in it - and everything has changed but the basic principles. And I know you didn't intend this exactly the way I reflexed to it: but I'm not just some QC guy. :) TBH - I am seriously over-qualified for the position i have. Fortunately it pays well.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
JSON is a data interchange format, it's purpose is to move data from 1 system to another. It sounds like you are building a completely new system which wouldn't require the movement of data--the data would live within that 1 system itself. So at first blush, yes JSON isn't something that is relevant.

Every organization today has an IT department--do you? I mean this whole thing sounds like it falls under their domain, not some guys in QC. Even if your organization doesn't have a full IT department there must have someone who does some coding or has some experience in building a real data system. Even if they aren't to build it, I would ask them what they think and what they are familiar with. That way if you do end up building this you have an easily accessilbe resource to go to when you encounter issues.
Hey plog - one last thing - and I'm not at all trying to be argumentative or even defensive. It's just that I realized this is a prime opportunity to toot my own horn about something I have never had the opportunity to do before. In 1998, I worked for NPRDC, a civilian contractor for the US Navy Personnel Department in Millington, TN. At the time, the Navy had DBs all over the world in a vast array of different languages (C, Cobol, Basic, Fortan, etc.). Each of these contained bits and pieces of every piece of information related to any given individual associated with the US Navy.

I got tasked with (because nobody else wanted to take it on and I was fresh out of college and dumb) with finding a way to connect all those databases. I had no idea how I could accomplish it until one day I saw a two paragraph article in Scientific American about a new technology that MIT was developing called XML. Light bulbs went off then too. Long story short, I know what a data interchange format is. I'm the culprit that hooked all of the Navy's personnel records together.

The reason that I asked about JSON is that 1) I have seen articles question whether to use an EAV model or JSON files; and 2) because if data can be stored then it must be able to be extracted and formatted somehow. All this company is after are 2 dimensional reports. Nothing fancy. And betwixt the two notions previously stated, I though it was worth inquiring about.

Thanks for your patience with my outbursts. :)

Tim
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:03
Joined
Feb 28, 2001
Messages
27,001
You have two possible approaches. Forcing this to a true web design pretty much limits the use of Access, because web protocols and Access protocols don't play well together. If you wanted to stay with Access, there might be some licensing issues, but if you can set up Remote Desktop Protocol and configure the individual user folders properly, you can open an RDP screen and use Access as though it were on the web - but it is not. In this forum, look up discussions on RDP and CITRIX as topics. Pat Hartman has extensive experience in CITRIX usage and has also use RDP.

You were with NPRDC. I was with NEDC-NO (an enterprise-level data center) so I know about Navy DBs. We had varying numbers of unclassified projects, from 16 about the time of Hurricane Katrina growing slowly to 80 by the time I retired in 2016, and I can't tell you how many classified projects we had. Well, I could tell you but then I'd have to maim you. (I wouldn't have to kill you because they weren't THAT highly classified.)

ADDENDUM: There I was, making a suggestion and I forgot to make the point of WHY you would use those products. If you have not heard of these products, then ...

Using Remote Desktop protocol allows you to open a session on your workstation as a client for a session at some other machine as the host. You can do all sorts of Windows things including launching programs that are running on the remote machine but they LOOK like they are running on your local machine because your mouse and keyboard are relayed to the host and the apps responses are relayed to your local window. If you want to stay with Access, RDP sessions could be what you want. You are not running web apps, you are simply running the Access apps through a non-local window.

CITRIX and RDB do this. Our friend ArnelGP recommends extended Terminal Services through TSPlus. These are all similar products with similar purpose. Cost and availability will be part of the deal. Licensing of Access should be carefully observed, but it IS possible for you to do without having Access installed on EVERY desktop. The folks who don't have Access on their machines can RDP to a host that has Access installed. Since this becomes a time-share situation, this is where you would need to consider Office Multiple User licenses.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:03
Joined
Jan 14, 2017
Messages
18,186
Unlike XML, Access cannot handle JSON files natively. For that reason, I created a utility a few years ago allowing Access to work with and use the data from JSON files. If you do want to explore that option further, suggest you look at my utility as a starting point.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
Unlike XML, Access cannot handle JSON files natively. For that reason, I created a utility a few years ago allowing Access to work with and use the data from JSON files. If you do want to explore that option further, suggest you look at my utility as a starting point.
Thanks isladogs! Sounds promising - informative for sure. I'll take a look.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
You were with NPRDC. I was with NEDC-NO (an enterprise-level data center) so I know about Navy DBs. We had varying numbers of unclassified projects, from 16 about the time of Hurricane Katrina growing slowly to 80 by the time I retired in 2016, and I can't tell you how many classified projects we had. Well, I could tell you but then I'd have to maim you. (I wouldn't have to kill you because they weren't THAT highly classified.)
Hi Doc,

Right on. You're one of the rare few who has ever heard of NPRDC. lol. "Maim" instead of.....that's hysterical!
 

GPGeorge

Grover Park George
Local time
Today, 15:03
Joined
Nov 25, 2004
Messages
1,776
I just wanted to add that a significant number of Access Developers started out very much in the same situation you find yourself in: an "IT department" or "IT guy" who knows nothing about Access, doesn't want to know, and yet can't provide the applications you actually need to get your work done. It also sounds like it comes from the top, i.e. management who threw money at something without really understanding what was involved.

It falls to people too committed to doing things right to let things go. Hence, you start Access and try to build something useful. Take heart in not being alone, even if you can't see or hear from other in your shoes that easily.

The Access Runtime really is not that hard to install. The fact that "the IT guy" failed isn't very reassuring. (What else doesn't he know how to do?)

Unfortunately, that leads back to the recommended solutions: using some sort of RDP. That's going to involve the cooperation, if not actual involvement, of that same IT guy. Therefore, as a success strategy, I'd recommend swallowing your disappointment and trying to engage him as a partner in solving the company's problem. It's not yours, although it sounds like you've taken responsibility for it. One of the more successful strategies I've used in that regard is buying the IT guy breakfast and just asking about his hobbies outside work. Or coffee, if you want to start small.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
The folks who don't have Access on their machines can RDP to a host that has Access installed. Since this becomes a time-share situation, this is where you would need to consider Office Multiple User licenses.
We
I just wanted to add that a significant number of Access Developers started out very much in the same situation you find yourself in: an "IT department" or "IT guy" who knows nothing about Access, doesn't want to know, and yet can't provide the applications you actually need to get your work done. It also sounds like it comes from the top, i.e. management who threw money at something without really understanding what was involved.

It falls to people too committed to doing things right to let things go. Hence, you start Access and try to build something useful. Take heart in not being alone, even if you can't see or hear from other in your shoes that easily.

The Access Runtime really is not that hard to install. The fact that "the IT guy" failed isn't very reassuring. (What else doesn't he know how to do?)

Unfortunately, that leads back to the recommended solutions: using some sort of RDP. That's going to involve the cooperation, if not actual involvement, of that same IT guy. Therefore, as a success strategy, I'd recommend swallowing your disappointment and trying to engage him as a partner in solving the company's problem. It's not yours, although it sounds like you've taken responsibility for it. One of the more successful strategies I've used in that regard is buying the IT guy breakfast and just asking about his hobbies outside work. Or coffee, if you want to start small.

We have a setup similar to that right now using RDP and time sharing. We have three plants: Rankin, Greensburg, and Bartow.

Greensburg and Bartow time share a database on a dedicated server via RDP. Due to their operations being extremely streamlined as far as product inspections are concerned it is relatively simple for them to have an hour on and an hour off. Rankin, where I am, has a much more robust operation happening at all times. Our DB is on a different server or we would be interrupting them constantly. And no, neither Greensburg or Bartow has a license for Access and there is a lot of pushback about spending more dollars on them as the RDP solution has been working for them for a long time - many years.

The VP of Quality/Engineering and the owner would like to be able to have and see everything in one locale. They don't quite understand what that means/entails from a technical/logistical aspect.

Which led me to asking the question here instead of Stackflow or somewhere else. I see us migrating away from Access because 1) zero buy-in from the IT guy; 2) a lot of pushback to purchase more licenses in order to give everybody there own front-end and 2) the requested requirement that all three plants are integrated.

Ultimately that is why I am thinking MySQL/MongoDB/Postgres BE; TablePlus/dbForge; and Python/PHP.

But I'm also thinking about the reply I am writing to GPGeorge also......as I see the validity of what he is telling me also.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:03
Joined
Feb 28, 2001
Messages
27,001
GPGeorge is spot-on. You can engage the IT guy ... OR you can tell your boss that the IT guy is not a team player in your project. Try direct engagement first since there might be some hard feelings if your boss talks to the IT guy's boss about being a helpful member of the team rather than a roadblock. Basically, once you get pushback from the IT guy, this problem ceases to be technical and becomes political. That is WHY you might have to go to the boss and point out that the IT guy doesn't recognize the value of the project. But in the long run, even the IT guys can "see the light" (since NOBODY likes to have the spotlight shining on them.)
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
It falls to people too committed to doing things right to let things go. Hence, you start Access and try to build something useful. Take heart in not being alone, even if you can't see or hear from other in your shoes that easily.
Thank you very much for that. FWIW, I have actually enjoyed learning Access through this trial by fire.
The Access Runtime really is not that hard to install. The fact that "the IT guy" failed isn't very reassuring. (What else doesn't he know how to do?)
There is a lot that he doesn't know. Almost all of his obstinance stems from a political broohaha with the person who attempted to develop the original DB for quality and did a miserable job at it. And the IT guy won't let it go - and that was years ago. I don't know if you are familiar with Pittsburgh, but outside of working at Google, this place, culturally, is 10,000% against change or forward movement.
Unfortunately, that leads back to the recommended solutions: using some sort of RDP. That's going to involve the cooperation, if not actual involvement, of that same IT guy. Therefore, as a success strategy, I'd recommend swallowing your disappointment and trying to engage him as a partner in solving the company's problem. It's not yours, although it sounds like you've taken responsibility for it. One of the more successful strategies I've used in that regard is buying the IT guy breakfast and just asking about his hobbies outside work. Or coffee, if you want to start small.
Ugh........scheisse (can I say that on here? lol) ....I know what you are saying. I'm dug in because this is far from the first pushback that I've received from him. Fortunately the owner will listen to me IF I show him the results of the effort - which means even more developing without a guaranteed outcome - shmoozing the IT guy will go nowhere. I have to do an end run and I hate doing that. But it is the most direct solution with the most predictive outcome.
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
GPGeorge is spot-on. You can engage the IT guy ... OR you can tell your boss that the IT guy is not a team player in your project. Try direct engagement first since there might be some hard feelings if your boss talks to the IT guy's boss about being a helpful member of the team rather than a roadblock. Basically, once you get pushback from the IT guy, this problem ceases to be technical and becomes political. That is WHY you might have to go to the boss and point out that the IT guy doesn't recognize the value of the project. But in the long run, even the IT guys can "see the light" (since NOBODY likes to have the spotlight shining on them.)
Hi Doc, see my reply to George :)
 

Zydeceltico

Registered User.
Local time
Today, 18:03
Joined
Dec 5, 2017
Messages
843
GPGeorge is spot-on. You can engage the IT guy ... OR you can tell your boss that the IT guy is not a team player in your project. Try direct engagement first since there might be some hard feelings if your boss talks to the IT guy's boss about being a helpful member of the team rather than a roadblock. Basically, once you get pushback from the IT guy, this problem ceases to be technical and becomes political. That is WHY you might have to go to the boss and point out that the IT guy doesn't recognize the value of the project. But in the long run, even the IT guys can "see the light" (since NOBODY likes to have the spotlight shining on them.)
I should also mention that I already know just how much he hates Access (for no good reason). I asked him to install MySQL on my computer. He instantly said no. I told him that I was researching a means to not use Access at all (knowing his Achilles Heel). He even more instantly - within seconds - said yes. And had it done within the hour which is like warp speed around here. So yeah - if I were to show him how to install Runviewer that would - (not going to use the expletive) - make him more angry than if I go over his head with the owner. :)
 

Users who are viewing this thread

Top Bottom