Trying to run an Access query from a c# application

Question

I have created a c# windows application that connects to an Access database.

My connection to the database is perfectly fine, but is there a way to run my Access developed queries so that the user can easily print the results? I know I can create reports in c#, but just want to use the ones developed in Access already. The query is qrySuppliers

// setup connection con to our database    
OleDbConnection con = new OleDbConnection("Provider = 
Microsoft.ACE.OLEDB.12.0; Data Source = Database3.accdb");

// create command object and open it
OleDbCommand cmd = con.CreateCommand();
con.Open();

Answer 1

Im not sure I got what you are trying to do but if you are trying to run the existing access queries you can do something like below:

OleDbConnection conn = new OleDbConnection(@ "Provider = 
Microsoft.ACE.OLEDB.12.0; Data Source = Database3.accdb");
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "YOUR_QUERY_NAME";

If you query expects any parameter, then set them like below..

cmd.Parameters.AddWithValue("@ParameterName", "YOUR_PARAMETER_VALUE"); 

Thank since it is a windows app, I assume you will need to fill it in a datatable or something. So you can do that like below:

OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

And if you already have a gridn and datasource on your form, than you can bind it as:

bindingSource1.DataSource = dt;
this.dataGridView1.DataSource = bindingSource1;

This should give some way to go.

Answer 2

Ok, so we can connect to a database engine from c#. Ok, all good.

But now we want to not use C#, but actually use access saved queries. Those queries are going to be VERY problematic to run from C#, because often those queries will have used VBA functions - you don't and can't call + use VBA functions in a query that you created in C#.

So, you could even pull the sql query from the access container, but when you try to run them from c#, you need use of VBA function calls. Now a signfiant portion of Access SQL queries do stick to and ONLY use JET/ACE data engine queries, but it still rather common that Access sql queries if developed for an Access application will (and can freely) use VBA functions in those queries. So, this is not going to work.

However, this narrative even gets worse, since you tossed in on top of this, the need and want to now run Access reports. Well, at this point, then you not talking about a connection to the database, but again using the Access object model and you want to run Access reports. Once again, Access reports are likely one of the most "killer" features of Access. The reason of course is that Access reports can also use VBA in the report. (the on print, on format events for a report can use and have VBA code. The result of course i that even better then SQL reporting services, event code (VBA) can be used in reports for detail section parts of reports - beyond powerful the results are.

So, I would suggest that you forget about the connection to the Access database engine (which is ALL you get if you using the .net oleDB provider, or the .net ODBC providers). You don't mention which provider you are using for the .net connection but the oleDB provider, or the ODBC provider of course can't run and use VBA code from c#.

The end result? You have to launch a copy of Access as a object, and then tell that object to run or print the report. In effect, you don't even thus need nor require a connection from c#, but only have to create a instance of Access.

eg:

{
object accessApp;

accessApp = createObject("Access.Application");

accessApp.OpenCurrentDataBase(@"C:\MDb\Invoices.accDB");

accessApp.Run("TimeUpDate");
accessApp.Quit();
}

The above would call + run a VBA sub called TimeUpdate that does whatever.

You could also run a report to default printer with

accessApp.DoCmd.OpenReport ("rptInvoice");

In other words, just like you can create a instance of word, or Excel, or in this case Access? You would not connect to Access, but create an instance of the Access application, and simply execute the same commands you would as if you had launched Access.

So, you can likely use/run some sql queries with a connection (oleDB, ODBC providers), but if those SQL statements have VBA in then, then they will not work. And for reports? Well as noted, that going down even further the rabbit hole, and at that point you might as well just launch Access here and forget about C#

Do keep in mind that if you create that instance of Access, and open a database? Then all of the access startup code and even the main form will launch when you do this. If Access spits out any prompts or dialog boxes (or dialog forms), then you not from C# be able to answer the prompts inside of "accessApp". So, unless the startup code in the Access application is relative clean, automating a copy of Access will be problematic.