Parameter in IN clause

A

Amy Blankenship

I want to so something like

INSERT INTO MyTable IN [Enter the path to a database] SELECT * FROM MyTable
WHERE UserID = [Enter User ID]

I know the second parameter part is possible, but has anyone successfully
used the first part to specify an external database at runtime?

Thanks;

Amy
 
A

arthurjr07

just a suggestion.

Why not linking that MyTable of another database to
your present database.

Goto Files > GetExternalData> Link tables

then after you have link that table
you can now use a simple query like this

INSERT INTO MyTable SELECT* FROM MyTable1


Hope this will help
 
M

Marshall Barton

Amy said:
I want to so something like

INSERT INTO MyTable IN [Enter the path to a database] SELECT * FROM MyTable
WHERE UserID = [Enter User ID]

I know the second parameter part is possible, but has anyone successfully
used the first part to specify an external database at runtime?
Click to expand...


Not that I ever heard of.

In situations where a linked table is inappropriate, I
always use a VBA procedure to execute that kind of query and
in a procedure where it's easy to construct the SQL
statement. Here's vastly simplified example:

strSQL = "INSERT INTO MyTable IN """ & _
InputBox("Enter Path") & """ SELECT * FROM mytable " _
& "WHERE UserID = " & InputBox("Enter User ID")
CurrentDb.Execute strSQL, dbFailOnError
 
A

Amy Blankenship

Because

1) I won't know where the database is.
2) Access won't be open and probably won't be installed on the end user's
machine

Thanks;

-Amy
 
A

Amy Blankenship

No, that's not going to work for what I need. Thanks though.

-Amy

Marshall Barton said:
Amy said:
I want to so something like

INSERT INTO MyTable IN [Enter the path to a database] SELECT * FROM
MyTable
WHERE UserID = [Enter User ID]

I know the second parameter part is possible, but has anyone successfully
used the first part to specify an external database at runtime?
Click to expand...


Not that I ever heard of.

In situations where a linked table is inappropriate, I
always use a VBA procedure to execute that kind of query and
in a procedure where it's easy to construct the SQL
statement. Here's vastly simplified example:

strSQL = "INSERT INTO MyTable IN """ & _
InputBox("Enter Path") & """ SELECT * FROM mytable " _
& "WHERE UserID = " & InputBox("Enter User ID")
CurrentDb.Execute strSQL, dbFailOnError
Click to expand...
 
Top