New to Microsoft Access world, should I invest the time learning it from scratch or go for the C#, SQL Server, and ASP path? Please advice (1 Viewer)

[hamaray]

Hi,

Please, let me first tell you what I've done so far.

Currently, I am using a complicated Excel set of files that I've created and using power query with pivot tables to arrange my work for a somehow huge project.

I never used Microsoft Access ever before.

Only used Excel for about +20 years.

I am not that even advanced in Excel, but just an intermediate user.

The project is about organing business contracts we have with all vendors, and it has several aspects, like:

1. contracts details.
2. vendor details.
3. paid installments for each contract.
4. determine which payments of which contracts need to be taken into consideration when closing the books at the end of the financial physical year, to transfer it to the new physical year.
5. Many contracts get a 10% or more or less as a percentage of increasing the total amount of contracts each year.
6. There is a different maintenance amount to be paid each year for each contract.
7. There are different conditions for each contract.
8. I am the main user who will work on this project, but there are an extra 2 or 3 people on the same network who may need to see the details and current status of any contract at any given time.
9. I've about 45 or 60 columns maximum on the current Excel file, I set a column for any given details that I am working with in case anyone needed it.
10. A few other requirements but still in the same zone of arranging data...etc.

In the beginning, I googled and thought that this kind of project requires custom programming, especially since my work will not pay for anything, but it is just me who wants to reduce the amount of work I am doing so I am trying to reduce time and increase efficiency as possible.

I started to google for which path to go for such a task, and after a while and because I do not know anything about programming, I ended up with an outline picture, that I will need to learn programming basics, then use a language like c#, then use a database server like SQL server (developer or express version will be more than enough), and all of these to be used with something like asp to access everything from the web.

• At some point, I came across some threads and articles about Microsoft Access, and I found a lot of fights regarding whether to use Access or not, which remind me of famous fights about which operating system is a better one.
• Also, I read many articles that Access will not be developed with more features because Microsoft forces you to drop it and go forward with SQL server, or even go for the dynamics solution on the web.
• Also, learning Access will not enable you to provide a solution for the web.
• Other thoughts were about Access is good only for desktops and for one user or a few more and not to be used concurrently in the same database.

So, long story short, I've found myself in a position where I do not know what is the right option.

I will invest my time in learning, and I am looking for the maximum benefits I can achieve for my time and my learning, so does Learning Access the better option?
Or the C#, SQL Server, ASP, and .Net is the way to go?
In all cases, I will set at my desk and read and learn and try.

I hope that I was able to express my situation.

I forget to mention that many recommended learning VBA as well with Access.

Note:
I've posted it in another forum, but can not add the link directly:
h t t p s : / / w w w . utteraccess . com / topics / 2065121 / posts /2816108
Just remove the spaces.

 

[theDBguy]

Hi. Welcome to AWF!

One huge advantage of using Access is you can get your project up and running fairly quickly. So, if you want to get going pretty fast, I would recommend to use Access. But, if you're not in a hurry and also think you want to create an application that's close to a commercial app, then you can those other things instead.

 

[Edgar_]

Access can do the things you mentioned for your job. It will also give you results fast. It's a specialized tool for that kind of thing. In this beginning phase for you, Access can do for you in one day what ASP with C# won't let you see until you're a few weeks in, and it'll probably make you think you didn't do it right considering the broad set of things you can do and their methodologies, whereas Access, being more limited, will let you know you're on the right track fairly quickly.

Pros of using/learning Access vs Learning C#, ASP, etc:
- Access can organize your stuff and do everything you mentioned FAST and EASY
- It'll be similar to Excel and other Office programs
- Multiuser if you have all your users in the same network, with just a few clicks by splitting the db
- Fast, predictable results that don't require too much learning
- Is low code , almost no code
- Awesome query builder, competitors are very expensive and not as complete as this
- User friendly
- You already have it installed, probably, so no cost and no extra tools needed

Cons of learning Access: None

Cons of using Access vs C#, ASP, etc:
- Constant concern about your database becoming corrupt at some point
- You're stuck with its features and limitations
- Anything that has something to do with the web, it'll be a workaround, not a full solution
- Bad security/ easy to hack
- Distributing Access apps for money is an overly blind optimistic venture, like a gamble
- Incompatible with current trends and technologies

If you're planning to create modern web applications, then go for C# and ASP. If not, go for Access.

 

[CJ_London]

I read many articles that Access will not be developed with more features because Microsoft forces you to drop it and go forward with SQL server, or even go for the dynamics

That is not true MS has no plans to drop access and continues to develop it (albeit slower than one would like)

note that sql server is just the database part, it has no facilities for creating forms or reports - the interface for us humans

but access can use sql server (and many others) as a back end rather than the provided ACE database

Also, learning Access will not enable you to provide a solution for the web.

Not directly but it can be installed on what is called a terminal server which a user can connect to from anywhere they have access to the internet. However you cannot develop a front end to run in a browser.

Other thoughts were about Access is good only for desktops and for one user or a few more and not to be used concurrently in the same database.

Only for desktops, agreed, but re my previous comment I can run my apps from my phone using terminal server or one of the products such as team viewer.

Number of users - not true, I have clients with around 60 users using an access back end- technically you can go to 255. But in that scenario I would use sql server, sql server express or sql azure as a back end.

I don’t disagree with Edgar’s comments but if looking for a new career path by all means look at html/asp/Java etc - you will need all of them plus sql server/similar for web development

if this is a one off, stick with access

either way be aware that any database bevit access j or r sql server works in a completely different way to excel. Excel stores data ‘wide and short’ whilst databases store data ‘narrow and tall’. further excel combines data and presentation in one view, databases store data in tables and presentation is done using queries which are then displayed in the user interface using forms and reports.

a single ‘table’ in excel might represent 2,3 or more tables in a database plus one or more queries

google ‘database normalisation’ to find out more, whether you go access or browser, you will need to understand and apply those principles.

 

[GaP42]

Reading you description:
I do not see any requirement that requires a web front end for users to maintain the content. (You may have a need to "publish" output or perhaps provide read-only access to data forms to find records, but no data maintenance.)

If you were to have it developed on SQL Server do you have technical support on site? a DBA? You would need to work out setting up a development and test and production environment in their server environment - as opposed to one you control if using the access database. This may be an easier starting point - I have the impression this will be all new to you. And then what you may do once you have a database application that is working well for you/ and the users (assuming a split of BE database and FE application) is work on migration - down the track if needed.

A further observation is that while your process works in Excel, to move to a database requires a re-think of your design. In particular, irrespective of which direction you go learn and apply the rigor of database normalisation in the design of your database. You have, through you Excel work gained extensive knowledge of your data and processes, however you need to translate this to storing and holding and maintaining data such that to a very high degree, there is only one place where any given piece of data is held. Further the data structures are generalised to ensure longevity/adaptability and maintainability.

Access, as a RAD tool, allows you to design and develop rapidly, tweaking as you go and a less steep learning curve. Learning VBA along the way for some tasks may also give some capabilities you may want to apply in EXCEL too.

 

[GPGeorge]

As I mentioned in your identical post on UtterAccess, the premise of this question is itself open to challenge.

Why consider it a binary choice? Why do you think can learn only web development, or only desktop development?

The future is wide open. Pursue the technologies that appeal to you and don't let artificial divisions among camps keep you from exploring the options as they come along.

To address your last question: VBA is the coding language used in Access; you can't "learn Access" without "learning VBA".

 

[Pat Hartman]

I have multiple clients that run Access applications entirely in the "cloud" by using Citrix. I have others that have both local and remote users and some use Citrix and others use Remote Desktop. What Access cannot do is run in a browser and so it can't support applications that need anonymous users the way websites like this one do.

Some of those clients use SQL Server BE's and others use Access (actually ACE since Access is the FE and the database engine is separate) Over the 25+ years I've been using Access, I have implemented applications with a variety of RDBMS BE's such as DB2, Oracle, and Sybase to name the most common options other than SQL Server. Access doesn't care. Using ODBC with linked tables and Access querydefs, you can swap BE's on a whim. I have one app which I sell to the public that can swap between ACE and SQL Server by pressing a button. The clients don't actually do that. They pick the install they want and they don't just change on a whim.

Once you separate Access the RAD (Rapid Application Development) tool FE from Jet/ACE the desktop database engines, you eliminate many of the limitations and complaints about Access. You are no longer constrained to the soft 30ish limit for concurrent connections (hard limit is 255). Now your constraint is the number of SQL Server seat licenses you own. So, if you want to run your Access application on thousands of desktops, go for it. Switching to an RDBMS also lifts the 2G limit to your data storage and your constraint becomes whatever the chosen RDBMS supports. Security is much enhanced because your data is no longer in a file that someone can just copy and crack. Of course, you have to understand how Access interacts using ODBC so you design the application to work WITH the ODBC and not ignore it.

Learning Access and VBA is much easier for a non-developer (especially if you have used Excel VBA) than jumping into a bit-twiddling, low level environment like C+. Understand your tool and use the right tool for the right project. If you don't need remote connections or you can live with the limitation of Citrix or Remote Desktop, Access is the best (cheapest, fastest) way to develop a certain class of data-centric applications. Developing in Access will take a fraction of the time you would need if you developed using a more general purpose platform that doesn't have the RAD features to help you and time is money.

 

[jdraw]

hamaray,

Since you've been using Excel for +20 years, you're not just out of high school/college. Have you worked in different organizations, or in the current for most of those years? Are you seeking some kind of career change? I guess what we're all wondering is what is the motivation and eventual goal. Learning anything --programming constructs, database concepts, carpentry, video editing, small engine repair....... is increasing your knowledge. It's something you have and can't be taken away. It is a personal decision and depends on your goal. We can help if given some parameters/conditions.

If you are intending to stay with the described project, then learning Access and participating in the switch from multiple Excel spreadsheets to an organized database seems prudent and practical. Ideally such a project would have management approval and hopefully a "patron" who can stick-handle the inherent politics.
If you plan on changing jobs and are focused on web applications, then, as others have advised, Access is not a major player. However, I have seen Access used for many things such as prototypes and mock-ups/proof of concept (because of its RAD capability) that have led to coding/designs that have been implemented in Oracle, C, HTML etc. Access is often used for reporting against other RDBMS applications- again because it is a RAD tool and can be used with multiple backend databases.
There are lots of youtube videos related to software tools, languages, databases, design, maintenance that may help identify and focus your areas of interest.

Bottom line is --Where do you want to be in 3-5 years? Is learning a particular language or specific database system key to that?

 

[amorosik]

I have multiple clients that run Access applications entirely in the "cloud" by using Citrix.

Can you give us an idea, even a rough one, of the costs to be incurred in using Citrix to 'webizzation' an Access application?

 

[ebs17]

I am using a complicated Excel set of files that I've created and using power query with pivot tables to arrange my work for a somehow huge project.

What you can process in Excel, you can also process in Access. If necessary, you can remotely control Excel applications from Access and exchange data with each other. Certainly a comfortable situation for a transition.

The project is about organing business contracts we have with all vendors, and it has several aspects, like:

1. contracts details.
2. vendor details.
3. paid installments for each contract.
4. determine which payments of which contracts need to be taken into consideration when closing the books at the end of the financial physical year, to transfer it to the new physical year.
5. Many contracts get a 10% or more or less as a percentage of increasing the total amount of contracts each year.
6. There is a different maintenance amount to be paid each year for each contract.
7. There are different conditions for each contract.
8. I am the main user who will work on this project, but there are an extra 2 or 3 people on the same network who may need to see the details and current status of any contract at any given time.
9. I've about 45 or 60 columns maximum on the current Excel file, I set a column for any given details that I am working with in case anyone needed it.
10. A few other requirements but still in the same zone of arranging data...etc.

If you are thinking about refactoring and transitioning to an (any) database, the first and crucial task would be to design a usable database schema and move away from the big Excel spreadsheet. Because databases and their native language SQL work row-oriented, not column-oriented. With Power Query you have probably come across a number of approaches to this (a derivative of a SQL Server works internally).

 

[mike60smart]

Hi,

Please, let me first tell you what I've done so far.

Currently, I am using a complicated Excel set of files that I've created and using power query with pivot tables to arrange my work for a somehow huge project.

I never used Microsoft Access ever before.

Only used Excel for about +20 years.

I am not that even advanced in Excel, but just an intermediate user.

The project is about organing business contracts we have with all vendors, and it has several aspects, like:

1. contracts details.
2. vendor details.
3. paid installments for each contract.
4. determine which payments of which contracts need to be taken into consideration when closing the books at the end of the financial physical year, to transfer it to the new physical year.
5. Many contracts get a 10% or more or less as a percentage of increasing the total amount of contracts each year.
6. There is a different maintenance amount to be paid each year for each contract.
7. There are different conditions for each contract.
8. I am the main user who will work on this project, but there are an extra 2 or 3 people on the same network who may need to see the details and current status of any contract at any given time.
9. I've about 45 or 60 columns maximum on the current Excel file, I set a column for any given details that I am working with in case anyone needed it.
10. A few other requirements but still in the same zone of arranging data...etc.

In the beginning, I googled and thought that this kind of project requires custom programming, especially since my work will not pay for anything, but it is just me who wants to reduce the amount of work I am doing so I am trying to reduce time and increase efficiency as possible.

I started to google for which path to go for such a task, and after a while and because I do not know anything about programming, I ended up with an outline picture, that I will need to learn programming basics, then use a language like c#, then use a database server like SQL server (developer or express version will be more than enough), and all of these to be used with something like asp to access everything from the web.

• At some point, I came across some threads and articles about Microsoft Access, and I found a lot of fights regarding whether to use Access or not, which remind me of famous fights about which operating system is a better one.
• Also, I read many articles that Access will not be developed with more features because Microsoft forces you to drop it and go forward with SQL server, or even go for the dynamics solution on the web.
• Also, learning Access will not enable you to provide a solution for the web.
• Other thoughts were about Access is good only for desktops and for one user or a few more and not to be used concurrently in the same database.

So, long story short, I've found myself in a position where I do not know what is the right option.

I will invest my time in learning, and I am looking for the maximum benefits I can achieve for my time and my learning, so does Learning Access the better option?
Or the C#, SQL Server, ASP, and .Net is the way to go?
In all cases, I will set at my desk and read and learn and try.

I hope that I was able to express my situation.

I forget to mention that many recommended learning VBA as well with Access.

Note:
I've posted it in another forum, but can not add the link directly:
h t t p s : / / w w w . utteraccess . com / topics / 2065121 / posts /2816108
Just remove the spaces.

Hi Welcome to the Forum
Can you upload a sample Excel file with no confidential data?

 

[Pat Hartman]

Can you give us an idea, even a rough one, of the costs to be incurred in using Citrix

I got prices of $30+ the price of an office subscription per seat. If you already have Office licenses, that should give you the lower end. If you have only a few users and no infrastructure and everyone needs to be able to operate remotely, then Citrix works.

You can also get quotes regarding Azure which I think uses RD. One of my clients used them but not for database apps. Then one day our database server died and that killed all the SQL Server apps we were running locally including my Access apps. They had also made the mistake of not keeping local backups. Do you have any idea how long it takes to download a server backup via the internet? You don't want to know. The upshot was they temporarily ended up running everything from the cloud since the backup was available there. They just instantiated a new server. But performance was abysmal. For almost a month until our local server was rebuilt, users were experiencing minutes to perform actions that on the LAN took seconds. Other users here swear by Azure. For my client it was a disaster. This was of course before COVID and everyone was working in the office although we had set up a couple of people (like me) with RD access to their desktops in the office. We didn't run an RD server. Just RD to a specific physical office computer.

Running your own server is the cheapest option but it requires infrastructure. You might purchase "cloud" servers (managed by you or by them) but I suggest that you read the contract VERY, VERY CAREFULLY if you will be doing business with a woke organization such as AWS who will cancel your access with 24 hours notice if they object to your politics. And I'm not talking about your web application. I'm talking about your back office applications that are running on the rented server such as your Accounts Receivable and email. Try doing any kind of business without those. Parlar is just one company that was "cancelled" by AWS because of politics. I'm not saying to not use AWS or any particular provider. I'm saying that you need to protect yourself against being "cancelled". If the provider decides they don't want to do business with you, that's fine. It is their choice. Just make sure your contract gives you enough lead time to find a new provider and move your stuff before you lose access to all of it.

 

[amorosik]

I got prices of $30+ the price of an office subscription per seat. If you already have Office licenses, that should give you the lower end. If you have only a few users and no infrastructure and everyone needs to be able to operate remotely, then Citrix works.

You can also get quotes regarding Azure which I think uses RD. One of my clients used them but not for database apps. Then one day our database server died and that killed all the SQL Server apps we were running locally including my Access apps. They had also made the mistake of not keeping local backups. Do you have any idea how long it takes to download a server backup via the internet? You don't want to know. The upshot was they temporarily ended up running everything from the cloud since the backup was available there. They just instantiated a new server. But performance was abysmal. For almost a month until our local server was rebuilt, users were experiencing minutes to perform actions that on the LAN took seconds. Other users here swear by Azure. For my client it was a disaster. This was of course before COVID and everyone was working in the office although we had set up a couple of people (like me) with RD access to their desktops in the office. We didn't run an RD server. Just RD to a specific physical office computer.

Running your own server is the cheapest option but it requires infrastructure. You might purchase "cloud" servers (managed by you or by them) but I suggest that you read the contract VERY, VERY CAREFULLY if you will be doing business with a woke organization such as AWS who will cancel your access with 24 hours notice if they object to your politics. And I'm not talking about your web application. I'm talking about your back office applications that are running on the rented server such as your Accounts Receivable and email. Try doing any kind of business without those. Parlar is just one company that was "cancelled" by AWS because of politics. I'm not saying to not use AWS or any particular provider. I'm saying that you need to protect yourself against being "cancelled". If the provider decides they don't want to do business with you, that's fine. It is their choice. Just make sure your contract gives you enough lead time to find a new provider and move your stuff before you lose access to all of it.

This advice, dictated by direct experience, is very welcome
And I believe useful for anyone who is considering the possibility of moving data and applications to the cloud

 

[Steve R.]

Assuming that you are designing a database system from scratch, I would recommend using MariaDB, and using a browser, such as Firefox (on each PC on the local LAN) to access the database. This would mean having to learn PHP and HTML, plus associated languages such as JavaScript. This approach would be very similar to having your own WEB on a local LAN.

A side benefit of this approach is that it would free you from the Windows environment and you would not have to pay any license fees.

 

[amorosik]

Assuming that you are designing a database system from scratch, I would recommend using MariaDB, and using a browser, such as Firefox (on each PC on the local LAN) to access the database. This would mean having to learn PHP and HTML, plus associated languages such as JavaScript. This approach would be very similar to having your own WEB on a local LAN.

A side benefit of this approach is that it would free you from the Windows environment and you would not have to pay any license fees.

Yes, I understand, but transforming an existing procedure from Access to Php/Html is a huge job
For some features (for example drag&drop) not even possible
And therefore, although this is one of the possibilities for webizing an Access procedure, it is all the more difficult to apply the larger the dimensions of the procedure to be converted are.

 

[Steve R.]

Yes, I understand, but transforming an existing procedure from Access to Php/Html is a huge job
For some features (for example drag&drop) not even possible
And therefore, although this is one of the possibilities for webizing an Access procedure, it is all the more difficult to apply the larger the dimensions of the procedure to be converted are.

Your choice. Just suggesting a potential alternative database development consideration, assuming development from scratch. If it would not meet your planned criteria, do not use.

 

[Pat Hartman]

Number of users - not true, I have clients with around 60 users using an access back end- technically you can go to 255. But in that scenario I would use sql server, sql server express or sql azure as a back end.

When the BE is SQL Server or some other RDBMS, the number of users is limited by the number of seat licenses you have for SQL Server. If you have 3,000 seat licenses, then the Access app can be run by 3,000 users concurrently. The 255 limit is ONLY when the BE is Jet or ACE. When you distribute the application correctly, Each user has his own personal copy of the FE. So the "Access" part of the app has only a single user.

 

[gemma-the-husky]

Hi,

Please, let me first tell you what I've done so far.

Currently, I am using a complicated Excel set of files that I've created and using power query with pivot tables to arrange my work for a somehow huge project.

I never used Microsoft Access ever before.

Only used Excel for about +20 years.

I am not that even advanced in Excel, but just an intermediate user.

The project is about organing business contracts we have with all vendors, and it has several aspects, like:

1. contracts details.
2. vendor details.
3. paid installments for each contract.
4. determine which payments of which contracts need to be taken into consideration when closing the books at the end of the financial physical year, to transfer it to the new physical year.
5. Many contracts get a 10% or more or less as a percentage of increasing the total amount of contracts each year.
6. There is a different maintenance amount to be paid each year for each contract.
7. There are different conditions for each contract.
8. I am the main user who will work on this project, but there are an extra 2 or 3 people on the same network who may need to see the details and current status of any contract at any given time.
9. I've about 45 or 60 columns maximum on the current Excel file, I set a column for any given details that I am working with in case anyone needed it.
10. A few other requirements but still in the same zone of arranging data...etc.

In the beginning, I googled and thought that this kind of project requires custom programming, especially since my work will not pay for anything, but it is just me who wants to reduce the amount of work I am doing so I am trying to reduce time and increase efficiency as possible.

I started to google for which path to go for such a task, and after a while and because I do not know anything about programming, I ended up with an outline picture, that I will need to learn programming basics, then use a language like c#, then use a database server like SQL server (developer or express version will be more than enough), and all of these to be used with something like asp to access everything from the web.

• At some point, I came across some threads and articles about Microsoft Access, and I found a lot of fights regarding whether to use Access or not, which remind me of famous fights about which operating system is a better one.
• Also, I read many articles that Access will not be developed with more features because Microsoft forces you to drop it and go forward with SQL server, or even go for the dynamics solution on the web.
• Also, learning Access will not enable you to provide a solution for the web.
• Other thoughts were about Access is good only for desktops and for one user or a few more and not to be used concurrently in the same database.

So, long story short, I've found myself in a position where I do not know what is the right option.

I will invest my time in learning, and I am looking for the maximum benefits I can achieve for my time and my learning, so does Learning Access the better option?
Or the C#, SQL Server, ASP, and .Net is the way to go?
In all cases, I will set at my desk and read and learn and try.

I hope that I was able to express my situation.

I forget to mention that many recommended learning VBA as well with Access.

Note:
I've posted it in another forum, but can not add the link directly:
h t t p s : / / w w w . utteraccess . com / topics / 2065121 / posts /2816108
Just remove the spaces.

I'm a bit late to this.

Access isn't a power version of Excel. It needs a completely different mindset. It needs the development to be about the data, not about the processes. Get the data structure correct, and developing processes becomes more straightforward and harmonious. You don't, or shouldn't, think about a system in terms of "I want a form to do so and so". You structure the data in a way that just makes it easy to produce " the form that enables you to do so and so".

A key difference is that access tables are unordered. There is no concept of a previous or next record. So any excel formula that processes a cell by reference to data in a contiguous row just won't work in a database. You start to think about processing all of your data, or more likely a subset of your data, in a particular way. You are more interested in the total of a set of data, rather than a running sum to produce the total. In fact it's very hard to obtain a running sum. The total of X rows of data is the same whatever order it's in. That's why the running sum is generally not important.

This little point means that working with access and any database needs a different approach, and a lot of experience and practice. You just can't easily start using access to develop a database in the way you can build a spreadsheet

You mention learning VBA alongside access. In my experience you cannot develop a decent database without a lot of code. Much of the code is defensive and is there to protect users, including the developer, from doing stuff that might damage data. It's easy to do that in a database because manipulation of data gives you so much power. all updates are realtime, and there's no undo/cancel/don't save as there is with excel.

Delete all the rows that match a certain pattern. Get the pattern wrong, and you can trash your data. Change all the values by a certain percentage. Use the wrong formula, or select the wrong set of rows and you can trash your data.

You hardly ever see all of your data, so you have to be sure that your processes work exactly as they should.

Get all this right, and it's powerful, but it's quite different to excel.

I think MS keep trying to add tools to access to help you not need so much code, but I find that can actually make it harder to use.

Once you get skilled with access it's actually quite hard to go back to excel for any application that processes a lot of data.

I hope this helps.