mingfattt
asked on
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
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
Why not just make attachments to the tables that you want to work on and then run the query from this database?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
hai
and sometimes i also got the invalid arguement error because of this coding, what is this all about? thanx again
Yee
and sometimes i also got the invalid arguement error because of this coding, what is this all about? thanx again
Yee
ASKER
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
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
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!
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!
By the way, if the answer you accepted 'really works' as you say, a grade A is more appropriate! ;-)
ASKER
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(
Set qdfQueryDef = dbsDatabase.QueryDefs("Ref
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(inve
qdfQueryDef.Execute dbFailOnError
just dont understand what this is happening it looks find on my debug.... sorry for bothering and tx again.
Yee
Dim wrkWorkspace As DAO.Workspace
Dim dbsDatabase As DAO.Database
Dim qdfQueryDef As DAO.QueryDef
Set wrkWorkspace = DBEngine.Workspaces(0)
Set dbsDatabase = wrkWorkspace.OpenDatabase(
Set qdfQueryDef = dbsDatabase.QueryDefs("Ref
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(inve
qdfQueryDef.Execute dbFailOnError
just dont understand what this is happening it looks find on my debug.... sorry for bothering and tx again.
Yee
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'!
Given the points you've allocated, this question is supposed to be 'easy'!