[Resolved-sort of] Import/Export/Copy a Query from one DB to another?

[A441OTA]

I resorted to a different approach eliminating the need to export the queries. Thanks!

************************************
Good afternoon,

I need to copy (or export/import) a query from one Access database to another..... I can do this with a table but unsure how to do this for a query?

Or, would you suggest that I simply rewrite the queries in VB?? One of them is VERY complicated (at least for me anyway!!) -- outer join of multiple select queries (12). Or, what do I need to look for/change if I copy the SQL statements from Access to VB to get them to work?

Either ADO or DAO is fine. Due to some time pressures I have resorted to a mixture of both (and by the way, if this will ultimately cause a problem with deployment please tell me now!!! I used DAO to import the tables, but have used ADO for the data entry portions.

Thanks,
Mary

[KevinJL]

Hi Mary,

To import a query from one db to a second. Try this:
1. Open the second db.
2. File|Get External Data!Import
3. Browse to the db where the query you want is located
4. Select the db
5. In the resulting dialog box, select the queries tab.
6. Click however may queries you want to import.
7. Click the advanced button
8. Make sure the import queries as queries option button is selected
9. Click OK, your all done.

I don't think your mixture of DAO and ADO will cause a deployment problem, as long as you have EXPLICITLY told your app what kind or recordset you want to use. You will need to include both object libraries in your distribution package though.

DAO.Recordset
or
ADODB.Recordset

Since both object libraries contain objects with the same name.

That could be a maintenance nightmare though. Personally I'd vote to go pure ADO.

HTH
Kevin

[A441OTA]

However, I'm sorry I should have been more specific....... how to you import/export/copy the queries from one db to another via VB code. I have successfully transferred all the tables via code like follows:

sql = "SELECT * INTO tablename IN dbname FROM table (in currentopendb)"

db.execute sql

I understand this is DAO (sorry, I'm such a newbie!!!), I couldn't find a solution that worked in ADO (but I'm sure there is one -- I thought I had one but it didn't work that's why I switched to this approach because it worked - this program has to work next week, eek!). This works well because it also creates the table if it doesn't exist.

Anyway, is there a way to write the code to get the queries from one db to another. I will keep researching in the meantime...

Thanks,
Mary