Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Unanswered: Is it possible to pass tablename as parameter?

    I have this stored procedure in access 2000. I need to run this code for diferent tables. Can I pass the TABLENAME and PRIMARY as input parameters? I have seen examples for sqlserver not access. Here is what I have:

    SELECT MAX (@PRIMARY) AS MaxID
    FROM @TABLENAME

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not as far as i know - but why would you want to?
    A's saved queries are supposed to have the advantage of being saved in an "optimised" or "compiled" state ...but when you change the target table the optimisation wont make sense any more. so you can as well generate the SQL when you need it.

    here are three things you can try:

    search M Owen's posts for his way of keeping track of a max ID (basically a one-row table holding current max)

    use DMax() (yeah, i know... )

    if whatever you are doing needs a saved query, attack the saved query SQL

    here's DAO-HOW:

    dim dabs as DAO.database
    dim qdef as DAO.querydef
    set dabs = currentdb
    set qdef = dabs.querydefs("nameOfYourQuery")
    qdef.SQL = "SELECT and so on;"
    qdef.close
    set qdef = nothing
    set dabs = nothing


    izy
    Last edited by izyrider; 01-27-05 at 12:08.
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2004
    Posts
    9
    I wanted to reduce the number the store procedures into single logical unit that I easily maintain.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    understood - but access is not SQL-Server

    ?? did you consider using MSDE instead of JET - i believe it has SQL-Server style SPs tho i never played with it

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2004
    Posts
    9
    I have consider using it, but I am creating a desktop C# app. I have users that need to run the app here at my company on our local pcs. I also have users that need to run the app on a laptop away from our office. I thought If i used access database file then it would be easier ensure everyone was using the same backend.

  6. #6
    Join Date
    Nov 2003
    Posts
    167
    Skurge,

    I'm not 100% clear on what you're wanting, but you could always dynamically construct your SQL using VBA. Something like this:

    db.QueryDefs("YourQueryHere").SQL = strSQL

    Where strSQL contains your table name chosend from a combobox in a form.

    strSQL = "Select * from" & [Forms]![MyForm].[tablename]

    The string is composed on the AfterUpdate property of the form's combobox.

    Access also has a permutation of systables, but it's got some funky prefix, like d_stables or something like that, so you can actually populate your combobox with a set of tables from your db.

    Is this something like you're trying to do?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •