This has to be possible

[Ted]

Hi all,

I may be making this harder than it reall is but is it possible to run a
query from a database and export that data to another Access 2000 database
without actually creating that table in the current database.

TIA
Ted

 

[Guest]

I think I may get proved wrong on this but I am pretty sure you can't import
a query into another database without the table information to go with it.
Remember a query is not a method for storing data.

 

[Ondrej Psencik]

Yes,

it is possible, but not with DoCmd.TransferDatabase, but through interating
the recordset given by that query and saving records line by line.

Or you can use linked tables and use INSERT INTO.

Or you can export the query into the CSV file and import it in the second.

Ondrej Psencik

 

[Chris2]

Hi all,

I may be making this harder than it reall is but is it possible to run a
query from a database and export that data to another Access 2000 database
without actually creating that table in the current database.

TIA
Ted

Ted,

Yes.

Create a linked table to the destination table in the destination database. On the menus,
File > Get External Data > Linked Tables.

INSERT INTO <linked table name>
(<linked table's column name>)
SELECT T1.<column name>
FROM <table name> AS T1
WHERE <etc.>


Alternatively, if you do not wish to create a linked table, you may use the full filepath
and filename of the destination database in [], followed by a period ".", and the table
name.

Generic Example:

[c\Databases\MyMSAccessDatabase.mdb].MyTableName

In SQL:

INSERT INTO [c\Databases\MyMSAccessDatabase.mdb].MyTableName
(<column names>)
SELECT T1.<column name>
FROM <table name> AS T1
WHERE <etc.>

Sincerely,

Chris O.

 

[Ted]

I used the full file path in the SQL statement...
Worked like a charm! Perfect!
Thanks for your help Chris

Hi all,

I may be making this harder than it reall is but is it possible to run a
query from a database and export that data to another Access 2000
database
without actually creating that table in the current database.

TIA
Ted

Ted,

Yes.

Create a linked table to the destination table in the destination
database. On the menus,
File > Get External Data > Linked Tables.

INSERT INTO <linked table name>
(<linked table's column name>)
SELECT T1.<column name>
FROM <table name> AS T1
WHERE <etc.>


Alternatively, if you do not wish to create a linked table, you may use
the full filepath
and filename of the destination database in [], followed by a period ".",
and the table
name.

Generic Example:

[c\Databases\MyMSAccessDatabase.mdb].MyTableName

In SQL:

INSERT INTO [c\Databases\MyMSAccessDatabase.mdb].MyTableName
(<column names>)
SELECT T1.<column name>
FROM <table name> AS T1
WHERE <etc.>

Sincerely,

Chris O.

 

[Chris2]

Hi all,

I may be making this harder than it reall is but is it possible to run a
query from a database and export that data to another Access 2000
database
without actually creating that table in the current database.

TIA
Ted

Ted,

Yes.

Create a linked table to the destination table in the destination
database. On the menus, File > Get External Data > Linked Tables.

INSERT INTO <linked table name>
(<linked table's column name>)
SELECT T1.<column name>
FROM <table name> AS T1
WHERE <etc.>


Alternatively, if you do not wish to create a linked table, you may use
the full filepath
and filename of the destination database in [], followed by a period ".",
and the table name.

Generic Example:

[c\Databases\MyMSAccessDatabase.mdb].MyTableName

In SQL:

INSERT INTO [c\Databases\MyMSAccessDatabase.mdb].MyTableName
(<column names>)
SELECT T1.<column name>
FROM <table name> AS T1
WHERE <etc.>

Sincerely,

Chris O.

I used the full file path in the SQL statement...
Worked like a charm! Perfect!
Thanks for your help Chris

Ted,

You're welcome.

(I left out the : after the drive letter in the example above. Sorry about that.)


Sincerely,

Chris O.