• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Using Recordset to run action Query in another database

Hai all

Greetings, i am currently working regards to to two database in one time, i found out that i cannot request the action query in my second db to run by using my first db (in VBA) fyi, previously i am using the recordset open to run it...and my code is as following,

rs.Open "RefreshInventory1", cn, adOpenDynamic, adLockOptimistic

RefreshInventory1 is the append query, and this code i put under the first database which i call it main db, but it seems like harming the result and showing the error expected.... SQL script. any ideas? Urgent, thanx in advance.

Yee
0
mingfattt
Asked:
mingfattt
  • 5
  • 4
1 Solution
 
DomThePomCommented:
Why not just make attachments to the tables that you want to work on and then run the query from this database?
0
 
mingfatttAuthor Commented:
Hai Dom

Thanx for your quick reply, since i am new in this field i cannot get clear what do u mean by that (attachment), its really making me headache whenever i want to ask the action query in the second database to run. please describe further please.. maybe a sample code ur help is very much appreciated.

Yee
0
 
JezWaltersCommented:
You can execute a query in different database like this:

  Dim wrkWorkspace As DAO.Workspace
  Dim dbsDatabase As DAO.Database
  Dim qdfQueryDef As DAO.QueryDef

  Set wrkWorkspace = DBEngine.Workspaces(0)
  Set dbsDatabase = wrkWorkspace.OpenDatabase("C:\YourDatabase.mdb")
  Set qdfQueryDef = dbsDatabase.QueryDefs("YourQuery")
  qdfQueryDef.Execute dbFailOnError

Note that this operates on the data in the other database too!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mingfatttAuthor Commented:
Hai

Thanx for u guys (DOM and Jez), by the way i can only granted the credit to one of u only... jez ur code really works, eventhough i am not really familiar with DAO, can i know another thing regarding the DAO, why do i cannot declare the DIM wrkWorkspace As DAO.Workspace and so on in my db but i can still use the code below... this is really making me headache and this is another new thing for me... really appreciate on ur guys help especially in this hard moment... million thanx

Yee
0
 
mingfatttAuthor Commented:
hai

and sometimes i also got the invalid arguement error because of this coding, what is this all about? thanx again

Yee
0
 
mingfatttAuthor Commented:
Hai,

Sorry for bother u guys again actually the code is work, but then when i deal with the ado and dao at the same time, because i need to update the table in another database as well.... its all run find in the debugging stage i mean by tracing it one line by one line, but when i try to make it in one go by clicking the button it seems like my update cannot get into the table... please help... is it because of the code above cannot deal with the ADO or any posiblity or solution? this is very urgent... thanx very much


Yee
0
 
JezWaltersCommented:
I'm not sure if I understand exactly what you're after, but it looks like you're getting confused between ADO and DAO (not surpising, when the acronynms are so simillar!).

The ActiveX Data Objects (ADO) and Data Access Objects (DAO) libraries both allow you to manipulate data.  However, because the libraries define objects with the same name you have to tell Access which library you want, if both libraries are in your Tools->References list.

That means you have to do this:

  Dim rstADORecordset As ADODB.Recordset  ' ADO library Recordset object
  Dim rstDAORecordset As DAO.Recordset  ' DAO library Recordset object

If you don't put in the 'ADO.' or 'DAO.' prefix, Access uses the first library that defines the object that it can find in your References list (usually ADO - although you can change the order).  Unfortunately, the ADO Recordset object methods and properties aren't the same as the DAO Recordset object methods and properties, so if the wrong one is used you get an error.

If your References list only contains one of the libraries, you can declare your variables like this:

  Dim rstRecordset As Recordset

This is what I normally do, but you have to change your References to only refer to the 'Microsoft DAO Object Library' first!

I hope this explains what you're seeing!
0
 
JezWaltersCommented:
By the way, if the answer you accepted 'really works' as you say, a grade A is more appropriate!  ;-)
0
 
mingfatttAuthor Commented:
Thanx for your answer...can i change the grade sign* well until this spot without any help from the message box i still cannot update the column in my table.. following is my code well thanx very much for helping me this far...

Dim wrkWorkspace As DAO.Workspace
Dim dbsDatabase As DAO.Database
Dim qdfQueryDef As DAO.QueryDef


Set wrkWorkspace = DBEngine.Workspaces(0)                    
Set dbsDatabase = wrkWorkspace.OpenDatabase("C:\DataLocation.mdb")
Set qdfQueryDef = dbsDatabase.QueryDefs("RefreshInventory1")
qdfQueryDef.Execute dbFailOnError


Call OpenCon
 
MsgBox ("Start Appending Year and Month of Inventory")  'without this part i cannot do the steps below

Rs.Open "UPDATE Inventory1 SET Month1 = '" & Cmb5 & "'", cn, adOpenDynamic, adLockOptimistic        Rs.Open "Update Inventory1 SET Week1 = '" & strPath4 & "'", cn, adOpenDynamic, adLockOptimistic
Call CloseCon

Set qdfQueryDef = dbsDatabase.QueryDefs(inventoryappd)        
qdfQueryDef.Execute dbFailOnError


just dont understand what this is happening it looks find on my debug.... sorry for bothering and tx again.

Yee
0
 
JezWaltersCommented:
Looks like you're now mixing ADO and DAO to me, which is not very maintainable.  It would probably be best to stick to ADO only and I'm afraid I haven't used it much.

Given the points you've allocated, this question is supposed to be 'easy'!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now