Copying queries from one Access DB to another eliminating duplicates

[Pramodhegde]

Hi,

I am new to VB application development with Access. I have a task of copying queries from one DB to another, without duplicationg the ones that are already existing in the destination DB (the names of the queries are taken to be the duplicating factor).


i tried using the DoCmd object, with the method TransferDatabase. But I'm not able use the DoCmd object itself. I have referenced the app to MS DAO 3.6. Do I need to reference it to something eles or am I doing something inherently wrong.

Any feedback would be appreciated

[MMcCarthy]

DoCmd has a copy object action. The following is taken directly from Access Help. If there is anything you don't understand just let me know.
DoCmd.CopyObject destinationdatabase, newname, ObjectType, sourceobjectname

The CopyObject action has the following arguments.

Destination Database
A valid path and file name for the destination database. Enter the path and file name in the Destination Database box in the Action Arguments section of the Macro window. Leave this argument blank if you want to select the current database.
Note This argument is only available in the Microsoft Access database environment (.mdb). When using this action in an Access project environment (.adp), the Destination Database argument must be blank.

If you run a macro containing the CopyObject action in a library database and leave this argument blank, Microsoft Access will copy the object into the library database.

New Name
A new name for the object. When copying to a different database, leave this argument blank to keep the same name.

Source Object Type
The object type you want to copy. Click Table, Query, Form, Report, Macro, Module. Data Access Page, Server View, Diagram, Stored Procedure. To copy the object selected in the Database window, leave this argument blank.

Source Object Name
The name of the object to be copied. The Source Object Name box shows all objects in the database of the type selected by the Source Object Type argument. In the Source Object Name box, click the object to copy. If you leave the Source Object Type argument blank, leave this argument blank also. If you run a macro containing the CopyObject action in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.


Remarks

You must enter a value for either one or both of the Destination Database and New Name arguments for this action.

If you leave the Source Object Type and Source Object Name arguments blank, Microsoft Access copies the object selected in the Database window. To select an object in the Database window, you can use the SelectObject action with the In Database Window argument set to Yes.

The CopyObject action is similar to selecting an object in the Database window, clicking Copy on the Edit menu, and then clicking Paste on the Edit menu. The Paste As dialog box appears so you can give the object a new name. The CopyObject action performs all of these steps automatically.

The path and file name of the destination database must exist before the macro runs the CopyObject action. If they don't exist, Microsoft Access displays an error message.

[Pramodhegde]

DoCmd has a copy object action. The following is taken directly from Access Help. If there is anything you don't understand just let me know.
DoCmd.CopyObject destinationdatabase, newname, ObjectType, sourceobjectname

Well, my problem is, I'm not able to use DoCmd as an object. Sorry if I sound like a moron. I have a VB Windows application. Have referenced it to Microsoft DAO 3.6. I try to use DoCmd for calling the TransferDatabase method, but I can't as it says 'DoCmd' is not defined. Even the intellisense does not show it up.

Can you please help?

[Scott Price]

DoCmd is an Access specific command of VBA. That means it won't run in VB6, nor in any other of the many flavors of Visual Basic.

You say you are importing from one Access DB to another, so you should have no troubles using this INSIDE the Access DB you are transferring INTO... OR inside the Access DB you are transferring FROM.

Regards,
Scott

[FishVal]

Well, my problem is, I'm not able to use DoCmd as an object. Sorry if I sound like a moron. I have a VB Windows application. Have referenced it to Microsoft DAO 3.6. I try to use DoCmd for calling the TransferDatabase method, but I can't as it says 'DoCmd' is not defined. Even the intellisense does not show it up.

Can you please help?

Access.Application class has property DoCmd returning DoCmd object.
Library: Microsoft Access XX.X Object Library.
Did you try this?

[Pramodhegde]

Access.Application class has property DoCmd returning DoCmd object.
Library: Microsoft Access XX.X Object Library.
Did you try this?

Well.. I tried referencing MS Access Object Library. But did not try the Access.Application class. Can you give me an example with the usage?


Thanks for the help

[Pramodhegde]

DoCmd is an Access specific command of VBA. That means it won't run in VB6, nor in any other of the many flavors of Visual Basic.

You say you are importing from one Access DB to another, so you should have no troubles using this INSIDE the Access DB you are transferring INTO... OR inside the Access DB you are transferring FROM.

Regards,
Scott

As I said, I'm totally new to Access. I'm more of an SQL guy. Anyways, I need to have a standalone app on VB.NET.

Any chance I can do something about it?

[FishVal]

Well.. I tried referencing MS Access Object Library. But did not try the Access.Application class. Can you give me an example with the usage?


Thanks for the help

Something like this. Hope VBA syntax does not differ much from VB syntax.

 
Dim appAccess as Access.Application
 
Set appAccess = CreateObject("Access.Application")
 
With appAccess
    .Visible = True
    .OpenCurrentDatabase "X:\db1.mdb"
    .DoCmd.CopyObject .......................
End With
 
Set appAccess = Nothing
 
 

[Pramodhegde]

Something like this. Hope VBA syntax does not differ much from VB syntax.

 
Dim appAccess as Access.Application
 
Set appAccess = CreateObject("Access.Application")
 
With appAccess
    .Visible = True
    .OpenCurrentDatabase "X:\db1.mdb"
    .DoCmd.CopyObject .......................
End With
 
Set appAccess = Nothing
 
 

Well, I referenced my app to MS Access Object Library, but I do not get Access.Application!

[MMcCarthy]

Well, I referenced my app to MS Access Object Library, but I do not get Access.Application!

Did you get the DoCmd ?

[Pramodhegde]

Did you get the DoCmd ?

Thank you people. I managed to work it out. I used Access.Application to invoke the DoCmd.


Thanks again