?
Solved

Using Recordset to run action Query in another database

Posted on 2005-03-15
10
Medium Priority
?
338 Views
Last Modified: 2008-03-10
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
Comment
Question by:mingfattt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 

Expert Comment

by:DomThePom
ID: 13543210
Why not just make attachments to the tables that you want to work on and then run the query from this database?
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13543242
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
 
LVL 17

Accepted Solution

by:
JezWalters earned 150 total points
ID: 13543621
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 2

Author Comment

by:mingfattt
ID: 13550720
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
 
LVL 2

Author Comment

by:mingfattt
ID: 13550974
hai

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

Yee
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13553066
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 13553595
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 13553597
By the way, if the answer you accepted 'really works' as you say, a grade A is more appropriate!  ;-)
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13561350
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 13563068
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question