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)?
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.
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.
Originally Posted by Sam Landy
Also, the comment The error message is easily circumvented if you delete the table in VBA before running the query, as in
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):
If Exists(<TableName>) = True Then
CurrentDb.Execute "DROP TABLE <TableName>;", dbFailOnError
Public Function Exists(ByVal ObjectName As String) As Boolean
Exists = DCount("*", "MSysObjects", "Name='" & ObjectName & "'")