Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Dynamic SQL

  1. #1
    Join Date
    Nov 2002
    Posts
    49

    Unanswered: Dynamic SQL

    Is it possible to create a generic SQL statement to search archived tables. I am looking to archive some of my data each year based on certain criteria (that's no prob). Each year, I would like to create an archived table (either in another db or same) with the name (tblYearXXXXArchive). I have a need to search this archive and am looking for a way to have the SQL string be able to keep referencing these new tables as they appear. I would like to have this as automated as possible to eliminate any "Down time" while the queries are updated. Any help will be greatly appreciated.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    what exactly are you trying to do?

    because there are many ways to do this, but the one you need depends on a few points?

    are you trying to create the SQL that creates the archives

    are you looking to search the current table if no match search through the archives

    are you trying to merge the archive tables back with the origanals for some reason

    ... bah bah bah you get the idea
    Last edited by m.timoney; 12-13-02 at 06:17.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by m.timoney
    what exactly are you trying to do?

    because there are many ways to do this, but the one you need depends on a few points?

    are you trying to create the SQL that creates the archives

    are you looking to search the current table if no match search through the archives

    are you trying to merge the archive tables back with the origanals for some reason

    ... bah bah bah you get the idea
    The way that I created a dynamic query is:
    I created variables and set them to combo boxes on my form. I then wrote the query using the variables.
    In this instance the combos made up the Where clause of my query. Doing it this way you will have to know the syntax of the statment farly well. for example when i set a variable that i knew would be in the middle of the clause i put
    variable = "and table_column_name like forms!from_name!form_object"
    This type of logic can also be applied to selecting columns. Just set the variable to the column name and then put the variable in the select section of th query.
    Hope this helps
    Jim

  4. #4
    Join Date
    Nov 2002
    Posts
    49
    m.timoney,

    I am able to move my data from the current table to the archived table. I am looking to build in a search capability for the archived info. Each year, I would like to avoid having to add the code to the SQL for a recordset if at all possible. For example, once you receive (or used to) your checks back from the bank (once they have been cashed) you store them for a few years (or so they used to tell us). Now you ask your self how many checks have I written in the past that are between X and Z, and you placed last year's old checks in a file with no label. You wouldn't be able to check all the old checks to determine the correct answer until you found/labeled (updated the sql behind the recordset) the new folder that you placed last years checks in.

    Don't get me wrong here, I can create the sql statement for the recordset. I am only concerned for the length of time that the statement can't find/isn't told to look for the newest archive folder; the database isn't at full capacity and some data may be incorrectly referenced.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    right so if i understand correctly all you want to do is check last years archive which means you need a variable from statement, which i would suggest that you use the DAO data object,

    Code:
    Sub dynamicSQL()
        Dim db As Database, qry As QueryDef
        Set ad = CurrentDb
        db.QueryDefs.Delete "Query Name"
        db.CreateQueryDef "Query Name", "select <from Current query>" &_
    "From Archive" & Year(Date) & " Where <<from Current query>;"
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Nov 2002
    Posts
    49
    You are mostly correct. I really need to be able to search all archived files (say I have 4 tables archive_1998, archive_1999, archive_2000, and archive_2002) and to create the new table as of jan 01 each year. I think a For Next may work where I set the string variable to a name i.e.
    GLOBAL VARIABLE= year started archive (i.e. 1998)
    currentyear=year(date)
    vardif=currentyear-GLOBAL VARIABLE
    string="<<<begining of SQL statement>>>
    for var =vardif to 0 step-1
    varfile="archive_" & (currentyear-var)
    string=string & "'" & varfile & "'" & " " '' space is intentional
    (this is where the varname is inserted for each
    archived file)

    next var


    Just a thought at this point. Haven't tested it yet.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    to be perfectly honest i think you'd be better off with a single archive table that has an extra field that store the date the record was archived from.

    but if your set on this method i think your on the right path now to solve it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Nov 2002
    Posts
    49
    I agree that one table would be much better (and easier), but I have several thousand records each year that have tha possibility of being audited. I would hate to have designed a db that I can't query these archived files. I am also concerned with the size growing to large for the server (even though we're receiving an upgrade soon), so I may be just trying to "bang my head off the wall" just to see if it can be done. Thanks for the help and if you come across any other helpful ideas, please fwd them (I'll greatly appreciate it)

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by saundone
    I agree that one table would be much better (and easier), but I have several thousand records each year that have tha possibility of being audited. I would hate to have designed a db that I can't query these archived files. I am also concerned with the size growing to large for the server (even though we're receiving an upgrade soon), so I may be just trying to "bang my head off the wall" just to see if it can be done. Thanks for the help and if you come across any other helpful ideas, please fwd them (I'll greatly appreciate it)
    something that you may want to know, if an access database gets bigger than 1 gig in size it will die. Watch your size it may be time to upgrade to ms sql server

    Jim

  10. #10
    Join Date
    Nov 2002
    Posts
    49
    that was a concern originally. I feel that I may be stretching the limits of Access2k, but I feel comfortable that the size of the archive file will not be that large for many years (probably past the life of this app I'm working on). One question I have is how do I test if a table exists w/o having to open it (or if I try to open it, will I receive an error)? Any ideas/suggestions?

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Code:
    Function searchfortable(tblname as string) As Integer
        Dim db As DAO.Database, rs As DAO.Recordset
        Set db = CurrentDb
        For i = 0 To db.TableDefs.Count
            If db.TableDefs(i).NAME = tblname Then
                searchfortable = i
                Exit Function
            End If
        Next
        searchfortable = -1
    End Function
    would be a good place to start
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by m.timoney
    Code:
    Function searchfortable(tblname as string) As Integer
        Dim db As DAO.Database, rs As DAO.Recordset
        Set db = CurrentDb
        For i = 0 To db.TableDefs.Count
            If db.TableDefs(i).NAME = tblname Then
                searchfortable = i
                Exit Function
            End If
        Next
        searchfortable = -1
    End Function
    would be a good place to start
    Riddle me this codeman....
    how would I try to find info in a table in a seperate database?
    Jim

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by JDionne
    Riddle me this codeman....
    how would I try to find info in a table in a seperate database?
    Jim
    set db = dao.OpenDatabase "<databaseName>"

    in the words of Tommy Cooper "Jus like that"
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by m.timoney
    set db = dao.OpenDatabase "<databaseName>"

    in the words of Tommy Cooper "Jus like that"
    Thats what I was thinking, now lets say that the db isnt there yet, and I create some type of error handeling to trap that error, in that error handeling can I make it go by that error with out prompting?

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    then you get lots of other errors because you haven't set any data to the database
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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