none
Retrieving input parameters for an Access query... and related questions RRS feed

  • Question

  • Hi, hope you can help.

     

    I am using the Microsoft.ACE.OleDb.12.0 provider to run queries in an Access database. Here are my questions:

     

    1. The app needs to be generic - it does not know the structure of the database until runtime. In the event that a user selects a query (in my app) to execute, if that query has input params, I need my app to display these in the UI so that the user can input them prior to execution (pretty much like how Access itself does it). I can't see any way to retrieve params in a query. My SelectCommand.Parameters always has a Count of 0, regardless of whether I prepare, etc.

     

    2. Using the GetSchema() method of the OleDbConnection, I can see all the parameterized queries in the database, as they appear as "PROCEDURES". Can anybody tell me what the PROCEDURE_TYPEs mean?

     

    3. The Microsoft.ACE.OleDb.12.0 provider has a number of Extended Properties (e.g. for opening Excel files). Is there a complete reference anywhere?

     

    Thanks in advance for your help.

     

    best wishes,

     

    Sigol.

    Monday, January 28, 2008 12:54 AM

Answers

  • Calling GetOleDbSchemaTable should provider you with a bit more information. If the QueryDef has parameters then they will be specified in the PROCEDURE_DEFINITION column. You will have to parse through the string and extract the parameters and also map them to corresponding OLEDB parameter data types.

     

    The PROCEDURE_TYPE can be one of three values, indicating whether the procedure returns data, doesn't return data or it is unknown whether it returns data.

     

    The below code will display the procedure properties available when using GetOleDbSchemaTable:

     

    Code Snippet

    Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection

    Dim SchemaTable As DataTable

    DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb"

     

    DatabaseConnection.Open()

     

    SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Procedures, _

    New Object() {Nothing, Nothing, "ProcName"})

     

    DataGrid1.DataSource = SchemaTable

    DatabaseConnection.Close()

     

     

    I'm not aware of any functional "Extended Properties" parameters for Excel other than HDR, IMEX and the ISAM name.

     

    Tuesday, January 29, 2008 3:27 PM

All replies

  • Calling GetOleDbSchemaTable should provider you with a bit more information. If the QueryDef has parameters then they will be specified in the PROCEDURE_DEFINITION column. You will have to parse through the string and extract the parameters and also map them to corresponding OLEDB parameter data types.

     

    The PROCEDURE_TYPE can be one of three values, indicating whether the procedure returns data, doesn't return data or it is unknown whether it returns data.

     

    The below code will display the procedure properties available when using GetOleDbSchemaTable:

     

    Code Snippet

    Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection

    Dim SchemaTable As DataTable

    DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb"

     

    DatabaseConnection.Open()

     

    SchemaTable = DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Procedures, _

    New Object() {Nothing, Nothing, "ProcName"})

     

    DataGrid1.DataSource = SchemaTable

    DatabaseConnection.Close()

     

     

    I'm not aware of any functional "Extended Properties" parameters for Excel other than HDR, IMEX and the ISAM name.

     

    Tuesday, January 29, 2008 3:27 PM
  •  

    Hey thanks Paul,

     

    I'll try that out tonight. Shame you've gotta parse the SQL, but fair enough at least there's a solution.

     

    Re the "Extended Properties" parameters, the reason why I ask is that apparantly, the driver also supports SharePoint but I can't find any documentation on this feature. Do you have any ideas?

     

    Cheers,

     

    Simon.

    Tuesday, January 29, 2008 5:09 PM