Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    11

    Unanswered: Make table query....separate database

    I have read-only credentials to a database that I access via a vpn but I need make a MAKE TABLE query to park some data to use in a subsequent query. How should I best do the MAKE TABLE query...can I save it to another database or can't I do this since I don't have write access to the original database (from which I want to run the second query)?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try something like:
    Code:
    SELECT * INTO tbl_Local FROM Table1 IN 'U:\Access\Test.mdb';
    where U:\Access\Test.mdb is the path to the remote database,
    tbl_Local is the name of the table to create in the local database and Table1 is the name of the table in the remote database (U:\Access\Test.mdb).

    Note: tbl_Local is created in the process. An error will occur if it already exists.
    Have a nice day!

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Wait a sec. Can't you just link to the table? I think you can link to a read-only table, no?

    Also, when you create the new table, there's no reason on Earth to park it in the other database. Park it in the current one. That's where your query2 will be, so why put it elsewhere?

    Also, the comment
    Note: tbl_Local is created in the process. An error will occur if it already exists.
    The error message is easily circumvented if you delete the table in VBA before running the query, as in
    Code:
    DoCmd.DeleteObject acTable,("TableName").
    Sam

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Sam Landy View Post
    Wait a sec. Can't you just link to the table? I think you can link to a read-only table, no?

    Also, when you create the new table, there's no reason on Earth to park it in the other database. Park it in the current one. That's where your query2 will be, so why put it elsewhere?
    I can see several reasons why you would chose another solution than an attached table. An attached table actually is a dynaset. It's heavier than a simple SELECT... INTO query, it's persistent (unless you write code to manage the attach/detach processes), it forces to load the whole row set (a process that can be time-consuming) while you can add a WHERE clause to the SELECT... INTO query and only import the rows you need, it forces a connection to remain open which is not always possible when you use a WAN or a VPN, you cannot further process the data without altering the original, etc.

    Quote Originally Posted by Sam Landy View Post
    Also, the comment The error message is easily circumvented if you delete the table in VBA before running the query, as in
    Code:
    DoCmd.DeleteObject acTable,("TableName").
    Sam
    If you unconditionally execute such a statement, you'll receive an error when the table does not exist. I would use (I actually often do so):
    Code:
    If Exists(<TableName>) = True Then
        CurrentDb.Execute "DROP TABLE <TableName>;", dbFailOnError
    End If
    With:
    Code:
    Public Function Exists(ByVal ObjectName As String) As Boolean
    
        Exists = DCount("*", "MSysObjects", "Name='" & ObjectName & "'")
        
    End Function
    Have a nice day!

  5. #5
    Join Date
    Jul 2012
    Posts
    11
    Ok, I think that I get most of this and it is all extremely useful! I am working on it now and just wanted to make sure that you knew that I appreciated your guidance.
    Thanks very much!!!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    If you unconditionally execute such a statement, you'll receive an error when the table does not exist.
    I have a slightly different workaround. I use

    Code:
        On Error Resume Next
        DoCmd.DeleteObject acTable,("TableName")
        If Err.Number = 7874 Then
            Err.Clear
        End If

Posting Permissions

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