Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: Making an edit macro

    the database i am making is required to create archiving tables by date so in effect i will have tables for 13/02/09, 14/03/09, 15/02/09 ect. how ever i have a query that is needed to collect data from these tables. this is simple enough to create a multiple table query however i was wondering if any one could tell me how to create a macro that can edit the sql so that i can add a table to the queries sql which would be todays date.

    so basically i was wondering if some one could explain how i would make a macro to edit the sql from SELECT 14/02/09.Product_ID to SELECT 14/02/09.product_ID, 15/02/09.ProductID

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not in a macro but it's rather easy to do in VBA. Simply recreate the query each time a new table is added:
    Code:
    Function CreateQuery(strQueryName As String, strSQLText As String, Overwrite As Long) As Long
    '
    ' This function creates a new query into the current database.
    '
        On Error GoTo Err_CreateQuery
        
        Dim dbsCurrent As Database
        Dim MyQuery As QueryDef
        Dim lngRetval As Long
    
        Set dbsCurrent = CurrentDb
        Set MyQuery = dbsCurrent.CreateQueryDef(strQueryName, strSQLText)
        lngRetval = True
    
    Exit_CreateQuery:
        CreateQuery = lngRetval
        Exit Function
    
    Err_CreateQuery:
        If Err = 3012 And Overwrite = True Then
            lngRetval = DeleteQuery(strQueryName)
            If lngRetval = True Then
                Resume
            Else
                Resume Exit_CreateQuery
            End If
        Else
            lngRetval = Err.Number
            Resume Exit_CreateQuery
        End If
    
    End Function
    
    Function DeleteQuery(strQueryName As String) As Long
    '
    ' This function deletes an existing query into the current database.
    '
    #If ERR_ON Then
        On Error GoTo Err_DeleteQuery
    #End If
    
        Dim dbsCurrent As Database
        Dim lngRetval As Long
    
        Set dbsCurrent = CurrentDb
        dbsCurrent.QueryDefs.Delete strQueryName
        lngRetval = True
    
    Exit_DeleteQuery:
        DeleteQuery = lngRetval
        Exit Function
    
    Err_DeleteQuery:
        lngRetval = Err.Number
        Resume Exit_DeleteQuery
    
    End Function
    Have a nice day!

Posting Permissions

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