Parameter in IN clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amy Blankenship - 30 May 2006 19:22 GMT

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

Reply to this Message

arthurjr07@gmail.com - 31 May 2006 01:07 GMT

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

Reply to this Message

Amy Blankenship - 31 May 2006 03:00 GMT

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

> just a suggestion.
>
[quoted text clipped - 9 lines]
>
> Hope this will help

Reply to this Message

Marshall Barton - 31 May 2006 02:34 GMT

>I want to so something like
>
[quoted text clipped - 3 lines]
>I know the second parameter part is possible, but has anyone successfully
>used the first part to specify an external database at runtime?

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Amy Blankenship - 31 May 2006 03:03 GMT

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

-Amy

>>I want to so something like
>>
[quoted text clipped - 16 lines]
> & "WHERE UserID = " & InputBox("Enter User ID")
> CurrentDb.Execute strSQL, dbFailOnError

Reply to this Message