none
Open a MSAccess Report that uses a parameterized query using C#...

    Frage

  • Let's say I have a table in MSAccess called Person:

    Person { PersonID AutoNumber, FirstName Text, LastName Text, Age Number(Long Int) }

    Create a new query with parameters called PersonQuery:

    Select FirstName, LastName, Age From Person Where Age >= [@beginAge] and Age <= [@endAge]

    Create a simple Report called PersonReport using PersonQuery as it's RecordSource.

    Here is the C# code I'm trying to use to open the report, but I always get prompted for the Parameters from access. 

    Access.Application accessApp = new Access.Application();

    accessApp.OpenCurrentDatabase("Database.mdb",faslse,null);

    accessApp.CurrentDb().QueryDefs["PersonQuery"].Parameters["@beginAge"].Value = 21

    accessApp.CurrentDb().QueryDefs["PersonQuery"].Parameters["@endAge"].Value = 35

    accessApp.DoCmd.OpenReport("PersonReport",acViewNormal,null,null,acDialog,null);

    IS THERE ANYWAY TO PASS PARAMETERS TO A REPORT WHOSE QUERY REQUIRES THEM, WITHOUT PROMPTING???

     

     

     

     

     

     

     

     

    Mittwoch, 21. Juni 2006 20:19

Alle Antworten

  • You cannot have named parameters in MS Access as in MS SQL Server

    you must use ? symbol instead @ParamName

    your statement should be

    Select FirstName, LastName, Age From Person Where Age >= ? and Age <= ?

    and code:

    Access.Application accessApp = new Access.Application();

    accessApp.OpenCurrentDatabase("Database.mdb",faslse,null);

    accessApp.CurrentDb().QueryDefs["PersonQuery"].Parameters[0].Value = 21

    accessApp.CurrentDb().QueryDefs["PersonQuery"].Parameters[1].Value = 35

    accessApp.DoCmd.OpenReport("PersonReport",acViewNormal,null,null,acDialog,null);

    hope this helps

    Mittwoch, 21. Juni 2006 20:32
  • Thank you for responding so quickly!

    However, you actually can have named parameters in MS Access and need to.  When I changed my query to what you suggested, my code still caused MSAccess to prompt me for parameter,?, but only once thinking it was the same parameter throughout.

    Any other ideas?

     

    Mittwoch, 21. Juni 2006 20:55
  • I am pretty sure about named parameters...

    ok ... lets try this... execute your query within MS Access environment and of it passes than we will know who is right :)  and where is the error...

    Mittwoch, 21. Juni 2006 21:26
  • Using your query in MSAccess:

    Select FirstName, LastName, Age From Person Where Age >= ? and Age <= ?

    When I execute this in the MSAccess environment, I get promted for 1 parameter, ?.  So, when I answer the prompt as 30, the query returns a result consistent with

    Select FirstName, LastName, Age From Person Where Age >= 30 and Age <= 30

    --------------------------------------------------------------------------

    Using my query in MSAccess:

    Select FirstName,LastName,Age From Person Where Age >= [@beginAge] and Age <= [@endAge]

    When I execute this in the MSAccess environment, I get promted for 2 parameters, @beginDate and @endDate.  So when I answer 15 and 45, the query returns a result constistent with

    Select FirstName, LastName, Age From Person Where Age >= 15 and Age <= 45

    FYI, the @ I'm using before my parameter names is not necessary, I only do it because it's habit working with SQL Server.

    --------------------------------------------------------------------------

    My real problem is how do I call the Report which uses this query, and pass the parameters in, so that I do not get promted by MSAccess for the parameters.

     

     

    Mittwoch, 21. Juni 2006 21:48
  • Hi, dmassat,

    Have you solved this problem? I am having the same problem.

    Donnerstag, 6. Juli 2006 15:51
  • Unfortunately, I have not found a solution.  I've been forced to generate my sql statement in code and change the query at runtime.

    Access.Application accessApp = new Access.Application();

    accessApp.OpenCurrentDatabase("Database.mdb",false,null);

    accessApp.CurrentDb().QueryDefs["PersonQuery"].SQL = String.Format("Select FirstName, LastName, Age From Person Where Age >= #{0}# and Age <= #{1}#",15,30)

    accessApp.DoCmd.OpenReport("PersonReport",acViewNormal,null,null,acDialog,null);

    Donnerstag, 6. Juli 2006 18:14
  • Based on the microsoft Article ID: 317114, The solution is simple:

    http://support.microsoft.com/kb/317114/

    It is a little bit different from what you did there. I tried it but It did not work for me.

    It may give you some ideas.

    Donnerstag, 6. Juli 2006 22:58
  • Me too having the same problem.
    Donnerstag, 5. Mai 2011 10:31
  • // I use the following...

    // in my main handler ...

     

    Access.

    Application accessApp = new Access.Application();

     

    try
     

    {

    accessApp.OpenCurrentDatabase(

    AccessDatabase.getDatabase, false, null);

     

    try

    {

    RunMacro(accessApp,

    new object[] { code, key });

    }

     

    catch (ArgumentException ex)

    {

     

    MessageBox.Show(ex.Message,"Access Parameters issue.");

    }

    

    //to run the Access Db macro...

    

     

    private void RunMacro(object oApp, object[] oRunArgs){

    oApp.GetType().InvokeMember(

    System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,

     

    null, oApp, oRunArgs);

    }

    

    

    "Run",

    Donnerstag, 5. Mai 2011 11:16
  • // not sure why the end of my code dropped off...here again...

     

    private void RunMacro(object oApp, object[] oRunArgs)

    {

    oApp.GetType().InvokeMember(

    "Run",

    System.Reflection.

    BindingFlags.Default |

    System.Reflection.

    BindingFlags.InvokeMethod,

     

    null, oApp, oRunArgs);

    }

    Donnerstag, 5. Mai 2011 11:17
  • this thread started in 2006! 
    Donnerstag, 5. Mai 2011 11:19