Running access queries in background

[ianajones25]

I'm writing a front end application for an access database. In the database I have update, append and delete queries that update various tables in the database.

I was wondering if there was a way to run these queries from within a VB.net application.

I've tried dragging the query onto a form which when run fills a datagrid of the data to be updated etc but it doesn't actually do anything.

Is it something basic i've overlooked or is it harder than I imagined.

Many thanks for any help.

Ian

 

[Morn]

I think that I get what you are saying, however I think that we would need to see an example of the code that you are using.

I believe that pulling sql code directly out of access and place it in vb.net will not work as you need to use the formal sql in the application.

Post the code and we can have a look at it.

Regards

 

[Raven65]

It sounds like the code is what he dosen't have, so I'm not sure he'll have much to post. What I think your trying to achieve iana is to execute your access queries from within your .NET app and update the database in this manner.

This is all extremly doable and fairly straight forward. There is a little post here about the differences between the DataSet and DataReader classes to get you started (you'll need to decided which best fits your case).

Then there is a -ton- more stuff about using ADO.NET in similar manners to what I beleive you are trying here. There also appears to be an ok article about using ADO.NET specifically with Access databases here.

Once you've read over that stuff and gotten an idea of where to go post back your questions and we'll try to assit.

 

[verschha]

I think this will do the job...

Imports System.Data
Imports System.Data.OleDb
 
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  Dim affectedRows = ExecuteNonQuery(param1.Text)
  MsgBox("the number of affected rows = " + affectedRows.ToString)
End Sub
 
Public Function ExecuteNonQuery(ByVal param1 As String) As Int32
' This runs a Stored Procedure (named sp_Test) with 1 parameter (type string) in an Access database (located at c:\database.mdb) and returns the number of Affected Rows
  Dim command As OleDbCommand
  Dim conn As New OleDbConnection
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database.mdb;"
  command = conn.CreateCommand
  command.CommandType = CommandType.StoredProcedure
  command.CommandText = "sp_Test"
  Dim parameter1 As OleDbParameter = command.CreateParameter
  With parameter1
    .ParameterName = "param1"
    .DbType = DbType.String
    .Value = value
  End With
  command.Parameters.Add(parameter1)
  Try
    command.Connection.Open()
    Dim affectedRows As Int32
    affectedRows = command.ExecuteNonQuery()
    Return affectedRows
    Catch ex As Exception
      Throw ex
    Finally
      command.Connection.Close()
    End Try
  End Function
End Class

In this example a query named 'sp_test' with 1 parameter 'param1' is executed, you'll get a message of he the number of affected rows.

One important issue is that if you have more than 1 parameter in the Access query, you must add the parameters to the command in the same order as in the query.

 

[cjard]

I've tried dragging the query onto a form which when run fills a datagrid of the data to be updated etc but it doesn't actually do anything.

Read the DW2 link in my signature..

 

[ianajones25]

What i've found

What i have since noticed is that the update queries etc did not appear in the data sources section but have since found them in the server explorer as stored procedures.

The data structures used are:

Table: tblIncomeCodes containing: Code, Category, Description, Net_Balance

Table: tblIncome containing: ITC, Amount

Table: TestingTable containing: Code, Description, Net

Append Query: qryTesting2 with SQL expression:

INSERT INTO TestingTable ( Description, Code, Net )
SELECT tblIncomeCodes.Description, tblIncome.ITC, Sum(tblIncome.Net) AS SumOfNet
FROM tblIncomeCodes INNER JOIN tblIncome ON tblIncomeCodes.Code = tblIncome.ITC
GROUP BY tblIncomeCodes.Description, tblIncome.ITC;

As I mentioned the query appears in the Server Explorer as a stored procedure. I can drag it into a DataSet Designer (although get a warning that parameter info could not be retrieved).

When I right click and configure qryTesting2 from within the QueriesTableAdaptor that has been created I get an error "Error in GROUP BY clause, Unable to parse query text". When I add the SQL code in the query builder and press the Execute Query button it behaves as expected and performs the updates.

It then asks me what what I would like to name the function so give it an arbitary name such as updater. So I now have a function called updater in QueriesTableAdapter that I don't know how to access.

That's what i've managed to figure out so far. Am I nearly there?

 

[ianajones25]

I've now worked out how to paste the SQL statement above into a new query in a table adapter.So within the dataset (called sebdbdataset) there are the tables mentioned above. Tacked on to the TestingTable bit is TestingTableTableAdapter into which I have created a query called qryTesting2.What I don't know now is 1) Is this the correct way of doing it and 2) How to go about executing the query.When I try to add code TestingTableAdapter1.qryTesting2 nothing seems to work.Any help would be great.

 

[cjard]

Read the DW2 link in my signature? ..

 

[ianajones25]

Hi there,Thanks for the links, without them I wouldn't have got this far. I tend to get lost looking through all the pages hence this reply.In the query that I create on the xsd page when I go into the query builder and click execute query it performs the insert OK. When I right click the query and choose Preview Data I get an error message: "sebDataSet.Query2() could not be previewed.Object Reference not set to an instance of an object"Also when I try to run the query from code nothing happens. Are there some settings within the query I should be changing?Ian

 

[cjard]

When you click "Preview Data" - exactly what data are you expecting an insert query will return for you to preview?

"Nothing happens" - what does that mean?

 

[ianajones25]

I thought that it might show the data that's going to be added when you click the preview button. When I click the execute button within the query builder it manages to tell me that 2 rows were added which is why I thought it might show the actual data.

When I try to run it literally nothing happens. I did get it so that it returned an error which i'll try to recreate and post here. I'm thinking that just calling the name of the query is not enough.

Ian