Results 1 to 3 of 3
  1. #1
    Kinetic is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    2

    SQL Storage within a table

    Hi all,



    I'm currently running a database with around 30 users, four or five of whom are capable of creating their own queries.

    Due to the number of bespoke requests I get for one off queries (and as Im not actually IT, i've just inherited the running of this), I dont have time to create new queries everytime information is needed, I'd rather allw those who can, do.

    Occasionally though I have to issue a new version of the front end (the DB is split) as some updates are inevitable.

    When I issue a new front end, I have to round up any new code written by others, and include this in the new version, to save their work being over-written. ideally I'd like to be able to save all the custom SQL in one place.

    In my previous employment we had a database within which custom SQL was saved to a specific table (the format was fairly simple: ID,name, SQL) with a memo field to hold any code written for a bespoke query.

    There was then within that database a form where you could select a custom query from a drop down list, push a button, and the SQL in the table would run.

    Any ideas how this was done so I can re-create this?

    P.S. I sort of burnt my bridges when I left the last place, so I can't really phone up and ask how they did it.

    All help appreciated.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I haven't had a chance to test this idea, but create your table. Store the sql statement in a memo field. Create a drop down with a name reference for the query.
    I would go with ID, qName, qSQL as the fields.

    In your VBA have the SQL statement be called from the column reference in your combo box. Add a command button to trigger the running of the Query based upon the selection in the combo box. I don't have time to recreate now but will look at this in the next day or so as time permits. In the mean time. Give it a shot and post back with any questions. There are lots of people in this forum who will be able to assist if you run into issues.

    Alan

  3. #3
    Kinetic is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    2
    Thanks Alan, I'll make a start.

    Could you let me know what you mean by this part: "In your VBA have the SQL statement be called from the column reference in your combo box."


    I think I need to add a command button, go into the code builder, and in the middle of this section:Private Sub Command83_Click()


    End Sub


    insert cose so that it runs the SQL from the relevent memo field.

    What is the command I need to use to insert the correct peive of SQL into that section?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter Critiria Storage
    By gg80 in forum Forms
    Replies: 2
    Last Post: 10-09-2012, 08:12 AM
  2. Record Storage DB Relationship Help
    By MintChipMadness in forum Database Design
    Replies: 12
    Last Post: 08-02-2012, 02:56 PM
  3. Loop and Max. storage corrupted db
    By boywonder in forum Programming
    Replies: 6
    Last Post: 11-15-2011, 09:33 PM
  4. General Storage Advice
    By GraemeG in forum Access
    Replies: 1
    Last Post: 03-13-2011, 02:01 PM
  5. PDF Storage
    By slash75 in forum Access
    Replies: 0
    Last Post: 04-18-2007, 02:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums