Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Unanswered: Create Table in another DB

    I'd like to use the createTable query to create a table in another DB on the fly

    Select * INTO tmpTable In 'this_mdb'
    From thisTable ;


    where this_mdb is an access .mdb file in the same directory where the main .mdb calling the query

    this functions good as long as the mdb file name is given as a constant
    But I'd like to give it as a function which gets the file name

    Then using it in the query like that

    Select * INTO tmpTable In Get_TempMDB
    From thisTable ;

    doesn't function



    PHP Code:
    Function Get_TempMDB() As String
      
    If IsNull(glb_TempMDB_FileName) Or glb_TempMDB_FileName "" Then
        Get_TempMDB 
    CurrentDb.Name
      
    Else
        
    Get_TempMDB glb_TempMDB_FileName
      End 
    If
    End Function 
    Where glb_ ????? are global variables.
    Last edited by hammbakka; 12-21-06 at 07:26.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Just a quick thought:

    Add () so that the DB Engine recognizes the item as a function and not a field name.

    Select * INTO tmpTable In Get_TempMDB() From thisTable ;

    If that doesn't work, how about dynamically writing the query - you create a new query or edit an existing one in code, and edit it's SQL property to your desired SQL statement. Better yet - don't bother with the query - run the SQL directly using DoCmd.RunSQL

    tc

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    First of all thank you for your reply,
    unfortunately the first solution, I have already tested it but hasn't function.

    The second one, would function, but I have so many queries and don't want to rewrite them again, I'd like to use the DoCmd.OpenQuery to execute the create table queries.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You could get crafty and make a function that inserts the table name in the correct part of the string.

    If you take a little leg work up front, you could make automating it really easy.

    I have a few ideas if you are interested.

    tc

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Thank you agian for your idea, one can make such a function, but I have to make a lot of changes in the porgram, so I let this as a last solution or I can use a constant name for the IN part
    Select * INTO table name IN 'constantFileName'
    Thanks again and merry christmas

Posting Permissions

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