none
Need help with Access Database RRS feed

  • Question

  •  

    Hi All,

     

    I am new to Access database and I need help on how to write VB scripts/queries in Access (Are they similar to stored procedures?).

     

    Also how can I access these queries inside access from a C# application?

     

    Please help me I think I am getting lost badly while looking at the online tutorials....

     

    Thanks

    Palak

    Wednesday, October 10, 2007 8:52 PM

Answers

  • Yes, you can write queries in Access and call them from your app. Below is an action query example which accepts a single parameter:

     

    Code Block

    Dim AccessConn As System.Data.OleDb.OleDbConnection

    Dim AccessCommand As System.Data.OleDb.OleDbCommand

    AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=e:\My Documents\db1.mdb")

    AccessConn.Open()

    AccessCommand = New System.Data.OleDb.OleDbCommand("QueryDefName", AccessConn)

    AccessCommand.CommandType = CommandType.StoredProcedure

    AccessCommand.Parameters.Add("@ParamName", System.Data.OleDb.OleDbType.VarWChar).Value = "SomeValue"

    AccessCommand.ExecuteNonQuery()

    AccessConn.Close()

     

     

    You should be able to find quite a few examples of ways to create a data access layer.

    Thursday, October 11, 2007 5:21 PM

All replies

  •  

    There are no stored procedures in Acces,

    You can write the queries in your application and pass it to the Access database using object of System.Data.OleDbCommand class.

    Thursday, October 11, 2007 10:41 AM
  •  

    There are no stored procedures in Acces,

    You can write the queries in your application and pass it to the Access database using object of System.Data.OleDbCommand class.

    Thursday, October 11, 2007 11:41 AM
  • Access QueryDefs are similar but not the same. The actual query statement is parsed at the time of execution and not when it is saved. You can use the Access QBE (Query By Example) designer to create Access QueryDefs.

     

    As was mentioned the OleDbCommand class can be used to execute an Access QueryDef. The code required would depend upon the type of query you're running.

     

    Thursday, October 11, 2007 2:47 PM
  • Thank You for writing back....

     

    Can I write queries in access and call them from my application.

     

    I need to create a datalayer using access database. How can I do that?

     

    Also what are VB scripts in access?

     

    Thanks

    Palak

     

    Thursday, October 11, 2007 2:54 PM
  • Yes, you can write queries in Access and call them from your app. Below is an action query example which accepts a single parameter:

     

    Code Block

    Dim AccessConn As System.Data.OleDb.OleDbConnection

    Dim AccessCommand As System.Data.OleDb.OleDbCommand

    AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=e:\My Documents\db1.mdb")

    AccessConn.Open()

    AccessCommand = New System.Data.OleDb.OleDbCommand("QueryDefName", AccessConn)

    AccessCommand.CommandType = CommandType.StoredProcedure

    AccessCommand.Parameters.Add("@ParamName", System.Data.OleDb.OleDbType.VarWChar).Value = "SomeValue"

    AccessCommand.ExecuteNonQuery()

    AccessConn.Close()

     

     

    You should be able to find quite a few examples of ways to create a data access layer.

    Thursday, October 11, 2007 5:21 PM
  • Well yes this is what I was looking for thank you so much for this code snippet.

     

    However one qucik ques, when I click on queries in access it has only two options create in design view or create in wizard then where I write this code for connection and command objects?

     

    Thank you once again for helping me out with this!

     

    Palak

     

    Thursday, October 11, 2007 5:46 PM
  • This code for connection and command would be written in your application not in Access,

    I think if you see a working example would be much useful for you

    Thursday, October 11, 2007 6:55 PM
  • The Access database engine does support stored procedures managed from code.

     

    Thursday, October 11, 2007 7:40 PM
  • This is more of an Access question but there are several ways to create the QueryDefs. As you mentioned, there is a wizard which walks you through the process but doesn't usually provide much flexibility. In design view you can drag and drop, add columns and tables, change the type of query, etc. which provides even more flexibilty. If you want to work directly with the SQL then you can click on the SQL button while in design view. There should be more information in the Access documentation on how to do this.

     

    After you've created and tested your QueryDef, save it and then reference it by name in your .NET code using the Command object (as in my example).

     

    Friday, October 12, 2007 11:47 AM