Export query results into another db (1 Viewer)

[Amileaux]

Private Sub Command0_Click()
Dim objAccess As Object
Dim strdoc

strdoc = C:\\2003 Deployment Data\"
strdoc = strdoc & "2003 DA Data_be_v2.mdb"
Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase strdoc
.Visible = True
.DoCmd.OpenQuery "Build Jobs Query"
'.Quit
End With
End Sub

I am trying to open a query in another access database (db1), have the results of that query import into a separate access database (db2). I've gotten db1 to open and the query to run using the above code - however, db1 closes (even though I've commented out the .quit statement) and I'm wondering how I get the query results into db2. Any thoughts would be appreciated. Thank you. Marie

 

[charityg]

Try linking the table from db1 into db2 and design the query to update the linked table.

 

[Amileaux]

The query is actually maintained by another "group" and I would need to be aware of any "changes" they make. So I do not want to have the query in my database since I would not always know if changes were made or not. I'm playing with the transfertext method - but haven't gotten far yet. Thanks for the suggestion though. Marie

 

[pdx_man]

Are you trying to import it into a table? If so, have the query in db1 be a Make Table query where the destination is db2.

If you are needing to work with the recordset, then do just that. Open a Recordset object. What version of Access are you using?

 

[charityg]

I think she is saying that she can't modify the query. What type of query is Build Jobs Query? Make table? Select? If it is a select query, you can create a new query def in code to duplicate the query from the other db on the fly (so it would always be that same no matter how the other group changes it). If the query is a make table query, you should the link the table that the query makes to your db and then run the query from your db. If you can give me a little more detail, I'm sure I can help you through this.

 

[Amileaux]

Build Jobs Query is a select query used by many - so changing this is not an option. I would like to save the results of the query as a table in db2. So I am intrigued by being able to make this same query on the fly, which would incorporate any changes to the query made by the "other" group. Thank you! Marie

 

[charityg]

I'll work on a sample.

 

[Amileaux]

Thank you! Meanwhile I'm looking up querydefs to see what I can learn. Marie

 

[pdx_man]

Well, again, I'm just thinking of keeping it real simple.

In the DB1 database, have a Make-Table query that has the other query that could change as its source.
SELECT Qry_Base.* INTO Tbl_TestB IN 'c:\db2.mdb'
FROM Qry_Base;

Call this query from your other db. If they change the Qry_Base, it will flow through ...

 

[Amileaux]

AAAAH... I see now. Bear with me a little bit longer. In DB1 (the "other" database) create a make table query based on the Build Jobs Query. Got it. I can do that. Here's my "fuzzy" part.

1. I need to tell it to run from DB2 and load as a table into DB2, which I can accomplish with: docmd.openquery? or use SQL?

A little more hand holding and I can move forward. Here's all the farther I've gone. Thank you. Marie

Private Sub Command0_Click()
Dim objAccess As Object
Dim strdoc

strdoc = C:\\2003 Deployment Data\"
strdoc = strdoc & "2003 DA Data_be_v2.mdb"
Set objAccess = CreateObject("Access.Application")
With objAccess
.OpenCurrentDatabase strdoc
.Visible = True
.DoCmd.OpenQuery "Build Jobs Query"
'.Quit
End With
End Sub

 

[charityg]

Kind of.

But a little backwards (I think).

The poster is working in db1. The query is in db2. The poster can't modify db2.

I think she needs to create a query in db1 that inserts the records from db2 into a table in db1.

 

[charityg]

I don't think you need to create another instance Access to accomplish your goal.

I was working on a solution, but I think I'm going to back away because my idea is in a bit of a different direction than the way your code is currently written.

 

[Amileaux]

Sorry for the confusion. I wouldn't take my existing code as anything to go by - I just started looking at examples and fumbled my way to where I am. Here's what I need to do - just ignore what I've done and let me know the best way to get this accomplished:

I need to "somehow" get the results of a query in another database (db1) into my database (db2). I would like these results to come into my database as a table. From there I can do what I need to do with the data (in theory). So, please don't give up on me - I'm listening. Marie

 

[charityg]

Public Sub RunMakeTableQuery()
Dim dbs As Database
Dim qdf As QueryDef

DoCmd.SetWarnings False

Set dbs = OpenDatabase("c:\db1.mdb")
' Create QueryDef object.
Set qdf = dbs.QueryDefs("qryMakeMyTable")

' Run QueryDef.
qdf.Execute

DoCmd.SetWarnings True

Set qdf = Nothing
Set dbs = Nothing



End Sub

db1 is the database with the Build Jobs Query and qryMakeMyTable is the Make Table query in db1 that creates the table from Build Jobs Query in your database

 

[charityg]

The SQL statement for the MakeTable query in the other db looks like this.

SELECT [Build Jobs Query].* INTO myTable IN 'C:\Path and Name of your DB.mdb'
FROM [Build Jobs Query];

 

[Amileaux]

okay - I've got the jist - let me play around with this. Thank you sooooo much for your patience. Marie