Is Access the best way to go about this project? (1 Viewer)

[kb44]

Hi all,

I have been tasked with evaluating whether Access might be the best solution to this issue.

*Background: I am fairly new to Access and am a new employee here. Microsoft software is used here for the most part.

Info:

• 800 employees in 30 different countries
• Sales Employee records (territories, positions, whom they report to, etc change weekly
• These records are currently being saved on Excel sheets and being consolidated by a regional employee

As you can imagine, this has turned into a nightmare with typos and other mistakes.

We currently just have a server that is mirrored. It is basically just a file share.
There are select employees in each country have access to all Microsoft apps (including Access) and can upload files to shared drives (this is how it is being done right now).

I was wondering if Access can handle this task without any issues. Or if it would even be the best tool to use to begin with.

I am thinking of creating a form (from a table) within Access that they can fill out.

Something that they can use to select an employee number (drop down), which then fills out the rest of the employee info in the other fields, so that we don't have 25 forms from the different countries.

Then also give them the ability to make changes to most of this form in order to update with current information.

I was also wondering what export options are available from Access? Can the data be exported to Excel or other programs?

Thanks for any help you all can give.

 

[Dreamweaver]

Access with SQL server should work just fine I'm not into SQL Server yeat so can't advise there but if you have a server your company uses thoughout the comapny then thats where I might put SQL Server.


I really don't think I would use a front/backend accdb arrangemant even if the datafile was stored on a global server you could end up with a lot of corrupted datafiles.


I'm sure there are a lot here with a lot more experiance than me that would be able to help more.

 

[isladogs]

Access can export to a variety of different file types including Excel / text/ PDF etc. However, that's the least of the issues you need to consider

Any database that is intended for multiple users needs to be split.
Each user needs to have their own copy of the FE on their own workstation hard drive.
Although the FE file can be distributed using shared drives it is ESSENTIAL that it is NEVER run from those drives or corruption WILL occur

The BE needs to be in a shared network folder (but see below)
For this type of database, a SQL Server BE would normally be more stable, secure and scalable than an Access BE.
However the big issue is that you are talking about a worldwide operation (WAN) therefore you need a solution that will work in that scenario.
You should investigate the use of Azure, Terminal Services or Citrix for your purposes. You could start by doing a forum search. If none of these seem suitable, then a non-Access solution will be required.

 

[plog]

No, not from a technological standpoint. But maybe from an available resources standpoint.

Maybe I'm reading too much between the lines, but it doesn't sound like your company's IT department is putting much resources into this, or even involved at all. Are you in IT or are you part of the department that uses this solution?

I've made a nice living working with Access in spite of IT departments. A department goes to IT to formalize a process, but IT either doesn't care or quotes $500k to get it done. Instead the Excel guy turns into the Access guy because everyone has Access, and builds an abomination of a database that runs because he knows all the "tricks". He leaves in a few years and so do all the "tricks". That's when I get paid because I'm less than $750k which is the new quote from IT to make this thing work.

Software and serverwise, this should be done with something else (e.g. SQL Server & web interface). But from the sounds of it, those might not be available.

 

[The_Doc_Man]

Basically, Access can handle everything you said EXCEPT:

There are select employees in each country have access to all Microsoft apps (including Access) and can upload files to shared drives (this is how it is being done right now).

Shared drives has TWO meanings. Which one applies to you? Only you can know this, so I'll tell you about both.

There are apps out there like Drop Box and various other storage sites. They connect over the web and can trigger an upload or download of a file from the storage site to the client machine. When they do this triggered transfer, they use web-based FTP or SFTP or some similar method. In doing so, they transfer whole files via a protocol that sends parts of a file in order from the source end and reassembles the parts on the destination end. The protocol allows for a retransmission of a failed partial transfer because the parts have sequence numbers. The receiving end can say "Sent part 1532 again." However, until ALL the parts are assembled, that file is not available. (It hasn't been closed yet and is read/write locked to all other users.)

Windows File Sharing uses a protocol named Server Message Block (SMB) and there is also a secure version if you set up the sharing properly. This form of file sharing allows the requesting program (Access) to ask for a specific block or two from the target file. The server side of the process uses the file system of Windows itself to copy the data to the requesting computer. The write operation can be done either way because the client can read from or write to the server. The server manages data locking while any transfer is under way. This method can request a retransmission of the last operation but once it moves on, that's all she wrote.

FTP and its variants are WHOLE FILE transmission methods. SMB and its variants are partial transmission methods. IF you start to write two blocks to the server side using SMB and the network drops halfway through, you CANNOT reestablish the connection in a way that will allow you to update the 2nd block because the port number of the virtual connection will change (the socket ID, if you prefer that name for it) and the transmission sequence number will change as well. So the file system will not allow you to break into the sequence that was interrupted.

FTP can ask for specific block numbers if a network glitch occurs. SMB works OK when a small transfer has to be retransmitted. However, Access sends whole recordsets down the network from back-end to server (and vice versa), so the problems that crop up come from incomplete updating of a database file. This is what we call "corruption" in this forum. A corrupted file can SOMETIMES be recovered enough to keep it usable but sometimes, you are not so lucky.

Therefore, everything else you described is WELL within the abilities of Access. But the method of getting data between your central office and your field sites is going to be your major pain in the rump. Why go through the long-winded explanation? Because SMB does not work very well (or sometimes doesn't work at all) over WAN connections. If you don't have a local area connection, SMB is GOING to barf. And you ARE going to corrupt your database. Sorry, but that's life in the world of Access.

 

[Dreamweaver]

thanks The_Doc_Man always enjoy your posts you a fountain of good information

 

[The_Doc_Man]

You are welcome, MickJav.

 

[Solo712]

Hi all,
We currently just have a server that is mirrored. It is basically just a file share.
There are select employees in each country have access to all Microsoft apps (including Access) and can upload files to shared drives (this is how it is being done right now).

I was wondering if Access can handle this task without any issues. Or if it would even be the best tool to use to begin with.

I am thinking of creating a form (from a table) within Access that they can fill out.

I take it you mean 'storage replication' rather than 'server mirroring'. I am not aware Microsoft has server mirroring capability; actually I believe the idea of mirroring networked servers went out of fashion after the catastrophic SFT technology by Novell. Microsoft has committed to clustering, but the clustered servers need be fairly close and and on a super high-speed link (dark fibre). At any rate, given the wide geographic area you operate on (and resulting possible issues with latency) I would not recommend using Access as a shared (distributed) back end for you data. MS SQL has its own mirroring which would likely work (I have no experience with it).

Best,
Jiri

 

[The_Doc_Man]

Jiri is right twice.

The term 'mirror' is rarely used for servers themselves, but it is a common term for what is referred to as a RAID 1 disk drive configuration. (RAID = Redundant Array of Inexpensive Disks.) RAID 1 is mirrorred so if member A of the mirror dies, member B contains the same data. Other RAID numbers exist but the most commonly seen are 0, 1, and 5, with a touch of 3 now and then.

Unless we ALL missed the announcement, Microsoft does not mirror systems. The ONLY company I ever heard of that could do this was Tandem with their non-stop O/S technology. Before its absorption by COMPAQ, Digital Equipment Corp. was working on a method whereby their VAX/VMS machines could physically share memory in a way that would allow multiple, physically separate VAXen to share the "running task" list. I.e. every task resided in shared memory and any connect VAX could run the task. If you think about it, this was a forerunner of having multi-core CPUs. But it pre-dates that ability by many years, and the COMPAQ buy-out ended that project anyway.

As of this date, I know of no one who actually has a truly physically redundant CPU setup to "mirror" the operations of another system. Even with clustering, you lose all user processes that were active on a failed cluster member.

Jiri's comments about the implications of wide geographic separation are ALSO on point. However, I might even hesitate to use Access as a front-end-only tool for a WAN-based topology. This might be something you need to develop using web-based data access tools.

 

[kb44]

Wow, thanks for all the replies!

I misspoke.. Yes I meant storage replication .

IT is not involved yet. I am just doing an evaluation of possibilities given what I was told our capabilities are.

My job with this right now is just process improvement (it is a royal mess right now).

What I meant by "sharing" is a drive on the server that is mapped to each PC where all files are stored. I believe it is Windows File Sharing.

So, from your replies I take it that Access would not be a good solution for this. I will ask them about SQL Server & web interface, Azure, Terminal Services or Citrix as suggested.

I have just learned that the information would have to be updated by all countries within two days. That means a lot of users doing updates at the same time.

From your replies I see the main reason Access would not be a proper solution is because it is not scalable to 26 different users (data corruption).. Are there any other reasons that I can give to justify other solutions?

Thanks again guys!

 

[jdraw]

kb44,

I agree with the other responders that Access is not a practical nor solid solution for your project. But having said that I have to wonder what your company IT set up is. With ~800 employees spread over 30 countries, there must be some HW/SW and procedures to run this as a viable and profitable business.
For example, how do they pay these people and maintain financial records? Globally?
It seems there may be a lot missing from your requirements. And as Colin said in post #3, there are many other things to consider before picking or rejecting Access as a feasible option.
Good luck with your project.

 

[isladogs]

From your replies I see the main reason Access would not be a proper solution is because it is not scalable to 26 different users (data corruption).. Are there any other reasons that I can give to justify other solutions?

No that's not true. A split database with an Access BE can have far more than 26 simultaneous users though it will slow down. The maximum possible is 255 though performance may be poor
Using a SQL Server BE is certainly much more immune to issues of corruption and speed shouldn't be significantly affected with many users.
Some of mine have over 200 users at once.

I did mention scalability but I was talking about file size. Access has a 2GB size limit. The free SQ Server Express edition can handle up to 10GB and some of the paid versions go to 1TB or more

The main issue for you is working over a wide area network and/or the internet.

 

[kb44]

Sorry for the long hiatus from this thread guys..

As it turns out, the company has decided not to use Access for this project.

As a large corporation, there is much division between the different corporate departments as to what software and resources they share. Most do not like to share partly because of licensing issues, but mostly because they choose to not be team players.

I have been trying to get licensing for our department but there is a lot of cost cutting right now and it was not approved.

There is much corporate politics and I am a small fish.

Thank you all for all of the awesome help! I really appreciate it! This forum is a great resource for someone like me just getting started.

 

[The_Doc_Man]

Glad you were able to get some answers to help you put things in perspective.

From your final comments, it will take a commitment from somebody very high up the corporate food chain to talk to those different departments that haven't yet learned the fine art of cooperation. Been there, done that, glad I left that situation. It can be a pain in the toches to navigate through that mine field.

Don't be a stranger. You might find someone saying, "OK, we can't do this big scale, but maybe we can do it for OUR department." I've seen that before.

 

[Frothingslosh]

You might find someone saying, "OK, we can't do this big scale, but maybe we can do it for OUR department." I've seen that before.

Welcome to my life. IT has a 'No Access ever under any circumstances' rule for everything it does. All the assorted departments, on the other hand, keep dedicated Access developers on-hand for all the department-level Access applications.

And we are not a 'small business' in any sense of the word.

 

[kevlray]

Not to side track the discussion. But our IT tolerates Access. The big issue is that a number of our users (with little or no training) have created Access DB's and sometimes they ask us to fix their issues. Also they do not understand that Access does not have much in the way of built-in security.

 

[kb44]

Glad you were able to get some answers to help you put things in perspective.

From your final comments, it will take a commitment from somebody very high up the corporate food chain to talk to those different departments that haven't yet learned the fine art of cooperation. Been there, done that, glad I left that situation. It can be a pain in the toches to navigate through that mine field.

Don't be a stranger. You might find someone saying, "OK, we can't do this big scale, but maybe we can do it for OUR department." I've seen that before.

Thanks for the advice. I will keep trying.:banghead:

 

[The_Doc_Man]

Frothy, we had a network security department that HATED Access and then was faced with the project from BUMED (U.S Naval Bureau of Medicine) that was WRITTEN in a combination of Access and SQL Server, for which they had a mandate - "thou shalt run this through thy network or we shall run you through with our budget-paring tool." After that, they relented somewhat. Then, when the only way they could get certain reports they wanted was to use Access, they grumbled and reluctantly asked my help. I did my best to not say anything disparaging but if you understand S.E.G. then you will know that I had an unending grin on my face.

 

[Frothingslosh]

Frothy, we had a network security department that HATED Access and then was faced with the project from BUMED (U.S Naval Bureau of Medicine) that was WRITTEN in a combination of Access and SQL Server, for which they had a mandate - "thou shalt run this through thy network or we shall run you through with our budget-paring tool." After that, they relented somewhat. Then, when the only way they could get certain reports they wanted was to use Access, they grumbled and reluctantly asked my help. I did my best to not say anything disparaging but if you understand S.E.G. then you will know that I had an unending grin on my face.

Heh. The place I worked from 2013 to 2016 had me write an Access app that takes any of roughly 15 different spreadsheets, identifies which spreadsheet is being loaded, pulls only the required data, and exports it into a CSV file.

The problem is that the spreadsheets are not only created by hand, meaning that not only do the header rows vary both in location and in field names, spelling, and order, but the data itself can change format, and sometimes important data is grouped (like categories A2-A7 and B3-B7 all using a single entry) or contained inside another field entirely. The grouped data, especially, was a pain, since the output format needed to include a separate record for each category, and they could be listed like 'A3, A5-A7, K3-K12'.
Age was especially bad - it might have its own column or be inside a Notes colulmn, and it might have entries labeled '18+', '18-65', >= 18, or even '18 to 25', all of which needed to be located and parsed.

It took about 3 months to create that beast during my free time from my other tasks, and thank GOD I had already built that 'Import Excel via VBA' module I have over in the Code Repository, because I had already solved half the issues with that.

Then we got a new supervisor who laid down the law, 'No Access under any circumstances whatsoever', and had the team's programmers (I was a glorified data entry clerk there) start converting all the existing apps to C#. Apparently they all revolted when they got to THAT app, because the logic needed to parse all that BS is so complex that they couldn't POSSIBLY justify the multiple man-months it would take to duplicate my work in C#. It's kind of a shame I was gone by that point.

From time to time, I check up on it with a friend who still works there, and every time he tells me they're still using my app over her objections, I get that aforementioned SEG on my face.

(I especially enjoy it since we parted on bad terms.)

 

[The_Doc_Man]

I understand the joy of parting on bad terms. To avoid naming names, let's just say that my first "real" job ended when the Louisiana company I worked for got bought out by a Maryland company, but they kept both shops open for a while. When it came time to close stuff down, I had to refuse the relocation offer because of a hardship to my family. My mom was already in a nursing home with a fatal prognosis (but not an immediately fatal prognosis). They said that was equivalent to resigning. Fortunately, Louisiana labor law at the time clarified that family illness was a valid exception and that I qualified for a hardship exception. Which in turn meant that they owed me a severance package. Since they were cut-throat businessmen, I was glad to be rid of them because they were bad to our former customers. I squeezed a 12-week severance run out of them and by then I had found a new job. So I DEFINITELY enjoyed sticking it to the hardball businessmen.

It even made me happier when I learned that these cutthroat bastards had themselves gotten bought out by a company from Ohio. It's rare, but sometimes business karma strikes soon enough for you to feel exhilarated by subsequent events.