MS Access without VBA?

Question

A simple question: Is it possible to use C# instead of VBA for MS Access? Can I extend Access applications with windows forms and (or) WPF? Would such a scenario make sense?

Truncated

Answer 1

Yes, you can write a GUI app in C#, then have MSAccess button (or menu) clicks shell out to the C# app, passing context information on the command line (e.g. Database name, form to open, ID of record, etc).

Alternatively, you can expose a COM interface to your application, so you can use CreateObject to create forms or access other functionality.

You'll find it very hard to go back the other way, to get access to your MSAccess forms & reports from the C# application, but it can be done using COM, or Windows Sockets).

You can of course just write a C# GUI application with the MSAccess database in the back end, and not use any MSAccess forms (NB If can and do decide not to use any aspects of MSAccess GUI, then I'd strongly recommend using a different database entirely such as SQL Lite or SQL Express).

Hope this helps.

Update In answer to Why would anybody do any of that? What's the point?

MsAccess database scale dreadfully. I've seen well written Access app suffer corruption and data integrity issues with 4 or 5 users. Granted network speed and stability has an effect, but really the problem is access (SqlExpress apps scale better with worse networks). See Limitations of MsAccess

From How scalable is MS Access

Access doesn't really have a place in any significant database project. It is basically aimed at the home market for people who want to be able to store info for home use without having to learn advanced Excel to do it

From an Inform IT article, where they spend much of the article telling you why you should use MsAccess, they add (this emphasis is mine)

• Scalability. Access doesn’t handle very large databases easily. Generally speaking, the larger the database, the more carefully the Access application has to be designed.
• Networking. Although Access is a multiuser database with built-in record locking and other transactional features, it doesn't work well over a network

"it doesn’t work well over a network" Really dude? In this day and age - with distributed computing the next big thing - what the hell good is that? So basically, if only one user on one computer needs to use the app, then that's ok, but if there's a chance you need to roll this out to multiple users, why spend the time and effort building it in Access, to roll it out you'd need to re-build the app have and a real RDBMS at the back end.

Really you're better off not using Access in the first place unless of course you're the only person in the world and you have the only computer :)

Heydon

@Truncated out of those possible options, the last one would probably give you the best results. It will be easier to code and support because you're not having to do tricky stuff to jump back and forth between apps. Plus, your end users will not need Access to run. Just .NET, your EXE (and DLL dependencies) and a valid connection to the database. – Lise

@David-W-Fenton: I edited my answer to answer your question too. – Heydon

Your information about "Access scalability" is riddled with problems and inaccuracies, not least of which the failure to distinguish between Access (front-end development tool) and the Jet/ACE database engine. Downvoting now, since it's clear you're basing your suggestion on nothing but inaccurate information about Access. – Kathleenkathlene

Everything you've added as clarification to your answer is subjective and WRONG. It's based entirely on a lack of understanding of how to use Access and the Jet/ACE database engine appropriately and following best practices. This attitude towards Access is widespread, but that doesn't make it any less wrongheaded. – Kathleenkathlene

@David: Everything I've added as clarification to my answer supports my own extensive experience with Access. I would never use access by choice, not when there are superior tools available. Obviously this is my own experience and personal opinion, which I have chosen to share with the community, and please, continue to down-vote answers you don't agree with :) – Heydon

I'm downvoting you not because I disagree with what you've written but because it is FACTUALLY WRONG in many aspects. – Kathleenkathlene

@David: first you said that everything I'd added was wrong, now just some of it is inaccurate. Tell me which points I was wrong on? 1) Access applications work well with hundreds of users? 2) Access applications work well with multi-gigabyte databases? 3) Access applications work well over slow networks? 4) How about all three together, do access applications work well then? – Heydon

1. Access front ends to server databases can work extremely well with 100s of users. 2. Access applications with an appropriately chosen back end can work great with multi-GB databases. 3. a well-engineered Access front end can work fine with a slow connection when it is optimized for efficient communication with the server database on the other end. 4. Access applications work just fine when you know what the hell you are doing and know how to artitect them appropriately for the operating environemt. You don't seem to understand the distnction between ACCESS and Jet/ACE, though. – Kathleenkathlene

@David: Thank you, I was wondering if I could get you to spell out – for the readers – that the database portion of MsAccess isn’t up to real world tasks. So to get any real world use out Access “applications” you need to run against a real RDBMS. Now,lets tackle the Access runtime. The runtime and development environment – without Jet/ACE – is simply a poor mans Vb6. Yes Vb6 was released in 1998 but it was always hampered by poor design decisions made for Vb3 (c 1993), Access suffers in exactly the same way. I’m going to turn your original question back to you. (cont) ... – Heydon

... When there are so many superior tools (some of which are free) out there for developing apps against server databases - why would anyone choose to write that application in MsAccess? Why would anybody do any of that? What's the point? – Heydon

I do NOT agree that Jet/ACE is a poor database engine. It's actually quite good, with lots of great features and excellent performance. But it has to be used in the environment in which it is appropriate. Too many people never bother to understand the basics of how Jet/ACE works so they end up trying to use it in circumstancs where it is inappropriate, which leads to them concluding that there's something wrong with Jet/ACE, rather than that their own understanding of how to use it is deficient. – Kathleenkathlene

VBA is a SUPERSET of VB, not the other way around. The version of VBA within Access is adapted specifically for database-oriented functions, so it's much easier to work with than VB, which is a general-purpose language. The reason Access is still a great tool for developing database applications is because it's so FAST (even compared to the latest and greatest), and because it's almost infinitely extensible (within the confines of COM, which for .NET-heads is a problem, but for people who care about solutions instead of technologies, is a complete non-issue). – Kathleenkathlene

@David: Apologies for the delay, I've been on vacation. You raise good points, but you'll never convince me that access is the correct tool for a client/server app, I've been burned too often in the past, and probably because I didn't have the time and/or inclination to get to know Access as well as you. Too often my experience with access was "Here one of our BA's (or junior devs, or in one instance CEO) put this Access app together but it has a few issues. Go iron them out this afternoon". – Heydon

. . . I don't think either of us will change each others minds on this particular issue, but I have enjoyed the discussion, thanks again mate. – Heydon

You're judging Access by the work of people who are ill-equipped to use it well. That they can produce something that is "good enough" actually is one of Access's strengths, just as one of HTML's strengths was the fact that it was so forgiving of crap that it allowed people to do all sorts of creative things (however horrid they may have been from the standpoint of an SGML). Access in the hands of a professional Access developer is a very strong front-end developer tool. Many development tools are very strong in the hands of competent users. And not so much in the hands of those who are not. – Kathleenkathlene

Answer 2

Well,

if you do not want to use Acces's VBA (and I guess all related objects like forms, modules, etc), I guess you'd better not use Access at all. Store your data in any 'real' database engine, free or not (SQL Express, MySQL, etc) and build your user interface in your favorite language.

Sesquipedalian

I don't understand why people think it's faster and easier to buld database applications using general-purpose development tools. – Kathleenkathlene

If you want to have something up and running few systems can beat the MSAccess environment. I wish the tools to build efficient web designs could be as easy. The charm is everything is self contained. Presenting forms and subforms and producing amazingly complex reports. Cheap and fast development if users are few. As you run on your own CPU reporting is superfast. Also generating many records sampling data is fast. Deployment is not an exe. Users need MSOffice installed. Server deployment rather not. Great for quick mockups/demo/research and personal reporting tool – Bombshell

Answer 3

You can use C# to control everything in Access but it seems like complete overkill. I would think VBA can handle any/all demands and with VBA you can even create custom functions in Access to do things that would be impossible using just standard built-in Access tools. Again, if you want to use C# to mange Access, yes, you can certainly do that.

Here is a good link to get you started.

http://csharp.net-informations.com/data-providers/csharp-oledb-connection.htm

There are lots of other links at the bottom of that page that should help you with many other things.

Calisa

Answer 4

Of course you can do that. You can do so many things when you integrate C# and Access. For instance, here is a way to load a GridView from Access.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your .mdb file;";
            string sql = "SELECT * FROM Authors";
            OleDbConnection connection = new OleDbConnection(connetionString);
            OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            connection.Open();
            dataadapter.Fill(ds, "Authors_table");
            connection.Close();
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "Authors_table";
        }
    }
}

Take a look at the link below for some more ideas of what can be done with C# and Access.

https://www.codeproject.com/Articles/1060352/Using-Microsoft-Access-Database-In-Csharp-ADO-NET

Although............I have to say, you will probably never get away from VBA. Also, VBA is the low-hanging fruit. It's a whole lot easier to use VBA with Access rather than trying to tame C# to do essentially the same thing that VBA would do for you. Also, VBA is probable a lot more limiting, and C# will be a lot more flexible, in terms of what you can achieve.

Protolithic