Web Development

Ask something
Discussion Locked

Copy ACCESS Queries and Reports using VB

0 helpful
5 replies

by kelvinsco

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We can successfully copy TABLES from one ACCESS database to another, using VB6 (SQL, Recordsets, ALTER TABLE etc to create new fields.)

BUT how do we copy QUERIES and REPORTS from one ACCESS database to another ???

Thanks in anticipation

5 replies

by dsa

Use CopyObject function to copy queries, tables, etc. from one access database to another.

by kelvinsco

Thanks for the suggestion, dsa, however...
Firstly, apologies for the long wait in getting back to this. We temporarily adopted a work-around strategy, and I thought I had replied to your suggestions here as appropriate. We now must go beyond our work-around, hence I raise the question anew.

The real difficulty is that we are using databases which are secured by a Workgroup, together with a hierarchy of UserIDs and User passwords. The database also has a DATABASE password. Hence we must pass all of these BEFORE we can use .OpenCurrentDatabase (after opening a new instance of ACCESS by specifying New Access.Application.

So my questions are:
HOW do we specify
1. The specific workgroup .mdw file?
2. The UserID and User password
3. The DATABASE password.

(We CAN do this OK by combining them into a ConnectionString when opening a RECORDSET - but this does NOT allow us to manipulate QUERIES and REPORTS. )

i.e. We must be able to use .DoCmd - but on a series of SECURED databases.

In fact, all I want to do is COPY (using DoCmd.CopyObject OR DoCmd.TransferDatabase) some queries and reports from one SECURED database to another SECURED database in the BACKGROUND (unseen by the user) under control of our VB6 Application.
It should be SIMPLE, right???!! ;-)

ALL HELP IS MUCH APPRECIATED.

Kelvin

by bob sellman

Why not do it right in Access? Just use the Get External Data/Import function on the File menu.

You can import all of the Access objects from another Access database with ease.

by kelvinsco

Bob,
Thanks for your comment. Apologies to you too. I thought I had replied long ago to this.
Please see my response to dsa above.
We MUST do this under program control, because it is a task we have to perform as PART of our own App, written in VB6.
i.e. we do NOT want the USER involved in this.

Regards,
Kelvin

by trmcdougle

The forms/modules/reports seem to be stored in hidden/system tables in access, as such it might be possible to just access these tables and copy the records.
To see these tables use Tools/Options/View and tick view Hidden and System objects.

I seem to recall pages on the web/MSDN talking about the sort of thing you want to do, try searching on the names of these tables. e.g. MSysObjects

This discussion is closed to new replies.