Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving variables (Access 97/SR2)

    I have a SQL statement stored in a string and I was wondering what the best place would be to store it temporarily while the database is closed.
    I thought of storing it either as a label, as the default value of an unbound text box, or in a memo field in a table - but didn't really want to start adding tables just for that purpose.
    Can anyone suggest the best solution for this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving variables (Access 97/SR2)

    What are you doing with the string? If you're building it in code, there isn't any reason to save it anywhere. If you have already created it, where? I have used a table to store SQL strings in the past, but the application was used to generate database objects, including queries. What is the purpose of storing this string?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving variables (Access 97/SR2)

    Thanks for your reply Charlotte.
    The SQL is written based on a form filled in by a user. It is then used to populate another form, report and query for export to excel.
    However, I currently have 2 problems with the solution being to save the SQL somewhere. Because the form and report are both based on non existent data sources, after being in design view, if I forget that this database is weird and click on the view button, I get a Dr Watson error and the whole thing bombs out! The export is written differently and just fails to run as the SQL statement is empty.
    A bonus to saving the SQL will be that the users last search will be remembered, which I think in this case, they'll find very useful.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving variables (Access 97/SR2)

    Four possibilities:
    Store a standard sql query in your form or/and report and change it at runtime.
    You could store the last query in the form and report, but than you have to open your form and/or report in design mode and prevent you to distribute the application as an MDE.
    Store the sql in a table but you don't like this.
    Store the sql in a text file outside the application.

    You could also do a test when the view button is clicked to see if there is a sql statement and cancel the view if not.
    Francois

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving variables (Access 97/SR2)

    Thanks for your replies.
    I think I'll have to go for the storing the query in a table.
    I had to check though, just in case I could have stored it elsewhere!
    Thanks for your help, much appreciated as always.

Posting Permissions

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