By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,376 Members | 3,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,376 IT Pros & Developers. It's quick & easy.

Copying queries from one Access DB to another eliminating duplicates

P: 8
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
Sep 9 '07 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Sep 9 '07 #2

P: 8
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?
Sep 9 '07 #3

Scott Price
Expert 100+
P: 1,384
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
Sep 9 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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?
Sep 9 '07 #5

P: 8
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
Sep 9 '07 #6

P: 8
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?
Sep 9 '07 #7

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim appAccess as Access.Application
  3.  
  4. Set appAccess = CreateObject("Access.Application")
  5.  
  6. With appAccess
  7.     .Visible = True
  8.     .OpenCurrentDatabase "X:\db1.mdb"
  9.     .DoCmd.CopyObject .......................
  10. End With
  11.  
  12. Set appAccess = Nothing
  13.  
  14.  
Sep 9 '07 #8

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim appAccess as Access.Application
  3.  
  4. Set appAccess = CreateObject("Access.Application")
  5.  
  6. With appAccess
  7.     .Visible = True
  8.     .OpenCurrentDatabase "X:\db1.mdb"
  9.     .DoCmd.CopyObject .......................
  10. End With
  11.  
  12. Set appAccess = Nothing
  13.  
  14.  
Well, I referenced my app to MS Access Object Library, but I do not get Access.Application!
Sep 11 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Well, I referenced my app to MS Access Object Library, but I do not get Access.Application!
Did you get the DoCmd ?
Sep 11 '07 #10

P: 8
Did you get the DoCmd ?

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


Thanks again
Sep 11 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.