Results 1 to 6 of 6

Thread: Calling a Query

  1. #1
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Unanswered: Calling a Query

    All,

    This should be brief. I have a back up procedures established in MS Access 2007 using VB that copies the workspace, creates a new database and copies the current record source table into the new database. We tried this with the linked table but found that the copy of the new DB wanted to sync back up with SharePoint, thus defeating the purpose. The solution was to use a make table query first in the source db, then run the procedure against the copy of the linked table and no link back to SharePoint is created. What I need help with is calling the "Make Table" query that I have created first so the data is "refreshed in the copy" prior to copying the workspace over to the new database and table.

    The name of the query for make table is : qryMakeTableQuery

    The current code is as follows:

    Code:
    Option Compare Database
    Option Explicit
    Sub BUDB()
        Dim ws As Workspace
        Dim db As Database
        Dim LFilename As String
        Dim strLinkName As String
        Dim strSQL As String
        
        'Get default Workspace
        Set ws = DBEngine.Workspaces(0)
     
        'Path and file name for new mdb file
        LFilename = "D:\Documents and Settings\MY.NAME\My Documents\" & Replace("dbJAMBBACKUP_@M.mdb", "@M", Format(Date, "yyyymmdd"))
     
        'Name of table to be copied
        strLinkName = "COPYJAMB"
        
        'Make sure there isn't already a file with the name of the new database
        If Dir(LFilename) <> "" Then Kill LFilename
     
        'Create a new mdb file
        Set db = ws.CreateDatabase(LFilename, dbLangGeneral)
        
        'Copy Linked table into a new table along with its data
        strSQL = "SELECT [" & strLinkName & "].* INTO [" & strLinkName & "] IN '" & LFilename & "' FROM [" & strLinkName & "]"
        DoCmd. SetWarnings False
        DoCmd. RunSQL strSQL
        DoCmd. SetWarnings True
        
        'Cleanup
        db.Close
        Set db = Nothing
    I have intentionally put spaces behind the DoCmd to try and make sure that the code posts correctly. Any help is sincerely appreciated. Can we keep it simple please?
    Thanks in advance
    Fighting Terror One Query @ a Time

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. To execute a query stored as a Query object in the database (aka a 'stored' or 'permanent' query) you can use (for the query qryMakeTableQuery):
    Code:
    CurrentDb.Execute "qryMakeTableQuery"
    ' Or:
    DoCmd.OpenQuery "qryMakeTableQuery"
    2. A SELECT... INTO query creates a new table or (with: SetWarnings False) replace an existing one with the same name if it already exists. If you want to keep the table previously created with the query qryMakeTableQuery, you need to change the SELECT... INTO query to an INSERT INTO query (see: http://msdn.microsoft.com/en-us/libr...ice.12).aspx):
    Code:
    INSERT INTO target [(field1[, field2[, ]])] [IN externaldatabase]
    SELECT [source.]field1[, field2[, ]     
    FROM tableexpression
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Sinndho,

    Thank you! One point of clarification, since you helped me get to the above code in the first place. Where in the above code that I posted would you recommend I put the currentdb.Execute "qryMakeTableQuery" statement.

    You know the order I want to do this in. 1) Run the make table query 2) execute the rest of code to make my "Off Line Copy"

    Thanks again.

    Jim
    Fighting Terror One Query @ a Time

  4. #4
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Sinndho, Cancel my last question. DoCmd dot OpenQuery "qryName" worked JUST AS YOU SAID IT WOULD.....Thanks again!!

    Jim
    Fighting Terror One Query @ a Time

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The sequence should be:
    Code:
        'Make sure there isn't already a file with the name of the new database
        If Dir(LFilename) <> "" Then Kill LFilename
     
        'Create a new mdb file
        Set db = ws.CreateDatabase(LFilename, dbLangGeneral)
        
        ' Create the new table structure.
        CurrentDb.Execute "qryMakeTableQuery", dbFailOnError
    
    
        'Copy Linked table into a new table along with its data
        strSQL = "INSERT INTO [" & strLinkName & "] IN '" & LFilename & "' SELECT <Field1>, <Field2>, ... FROM [" & strLinkName & "]"
        
        DoCmd. SetWarnings False
        DoCmd. RunSQL strSQL
        DoCmd. SetWarnings True
    Have a nice day!

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Late to the party, sorry!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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