Help with SQL Statement

[Kitty]

I have a database that has an extract function. Certain
fields are extracted into a temporary database by several
individual users at the end of a project. That temporary
database is imported into a "master" database by whoever
is in charge of the project to create summary information.

Currently, the temporary database resides on a diskette as
work is generally done when not connected to a network.

Now, I want to provide more flexibility as folks are using
USB drives as well as wanting to be able to process on
network drives. I have no control over computer
configurations and drive letters vary considerably from
location to location.

I do not want to write a query for every possible drive
letter. Instead, I'd like the user to tell the program
what drive has the temporary database (I will provide a
list of available drives on a form for them to pick from
that is based on their computers' configurations and if
they're connected to a network). The code below is a SQL
statement in a module that takes the contents of an
extract table and puts it in the temporary database. I
want to be able to replace "A:\" with what the user
selects as the target drive. Assume that drive letter is
in a table called tblTransferDrive in a field called
DriveLetter. How can I feed the SQL statement the field
name?

DoCmd.RunSQL "INSERT INTO tblSample IN 'A:\TEMP.MDB'
SELECT DISTINCTROW tblSample.* FROM tblSample;"

Thanks for your help.

Kitty

 

[George Nicholson]

Kitty:

********
Dim strDrive as String

strDrive = DLookup("[DriveLetter]","tblTransferDrive")

DoCmd.RunSQL "INSERT INTO tblSample IN '" & strDrive & ":\TEMP.MDB' SELECT
DISTINCTROW tblSample.* FROM tblSample;"
********

Hope this helps,

 

[Kitty]

George, Thank you! Worked perfectly.

I was missing the first &.

I appreciate your help.

Kitty

-----Original Message-----
Kitty:

********
Dim strDrive as String

strDrive = DLookup("[DriveLetter]","tblTransferDrive")

DoCmd.RunSQL "INSERT INTO tblSample IN '" & strDrive & ":\TEMP.MDB' SELECT
DISTINCTROW tblSample.* FROM tblSample;"
********

Hope this helps,
--
George Nicholson

Remove 'Junk' from return address.

I have a database that has an extract function. Certain
fields are extracted into a temporary database by several
individual users at the end of a project. That temporary
database is imported into a "master" database by whoever
is in charge of the project to create summary information.

Currently, the temporary database resides on a diskette as
work is generally done when not connected to a network.

Now, I want to provide more flexibility as folks are using
USB drives as well as wanting to be able to process on
network drives. I have no control over computer
configurations and drive letters vary considerably from
location to location.

I do not want to write a query for every possible drive
letter. Instead, I'd like the user to tell the program
what drive has the temporary database (I will provide a
list of available drives on a form for them to pick from
that is based on their computers' configurations and if
they're connected to a network). The code below is a SQL
statement in a module that takes the contents of an
extract table and puts it in the temporary database. I
want to be able to replace "A:\" with what the user
selects as the target drive. Assume that drive letter is
in a table called tblTransferDrive in a field called
DriveLetter. How can I feed the SQL statement the field
name?

DoCmd.RunSQL "INSERT INTO tblSample IN 'A:\TEMP.MDB'
SELECT DISTINCTROW tblSample.* FROM tblSample;"

Thanks for your help.

Kitty

.