Access 2003 and/or 2007 copy in objects from another mdb

[Matthews_416]

Is is possible to write code in an Access MDB that will allow a user to
select another Access MDB and then see a list of report and/or queries so
they can select from the list. Then programatically copy in those objects
from another mdb in to the current mdb? I know that access allows for the
exporting and importing of objects but I have a need to do this
programatically.

 

[Roger Carlson]

Sure, it can be done. Here are the general outlines:

1) Use the Open/Save API that you can find here:
http://www.mvps.org/access/api/api0001.htm to select the database file you
want to inspect.
2) Programmatically link the MSysObjects table from that selected database
(it's hidden and system, but it's there) into your database.
3) Create a query based on this linked table that displays the Name and Type
field. Filter the query for types: -32764 and -32768, like this:

SELECT DISTINCTROW Mid([Name],4) AS Expr1, MSysObjects.Type
FROM MSysObjectsRemote
WHERE Type=-32764 OR Type =-32768
ORDER BY MSysObjects.Name;

4) Use this query as the Row Source for a Multi-select Listbox on a form.
5) In code:
a) Step through the seleted values of the listbox
b) Use the "DoCmd.TransferDatabase acImport..." method to copy the
select reports and queries to your database.

On my website (www.rogersaccesslibrary.com), there are several small sample
database which illustrate portions of these:

ChooseReportFromList2-2k.mdb
MultiSelectListboxes2k.mdb
LinkTables2k.mdb

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L