Run query from different database

[MikeStuck]

Hello!

I am stumped... I have several make-table queries I run in different Access databases that I run VB code from a button on a form that executes the query, makes the table, and emails it to selected recipients. Is there a way to run and email the query/table from one datadase in another? Here is my attempt so far (failed). I am guessing I need to set the focus somehow to the second db but am not having any luck. Would I need to re-set focus to the main db before I close out of the database? Apologies for not being smarter- I am self taught and cannot get this one.

Private Sub Command1_Click()

Dim strLoc As String, strRun As String
Dim strQry_Name, strTbl_Name As String
Dim strTo As String
Dim strSubject As String
Dim strMessage As String
Dim strDB As String
strQry_Name = "QA Report MR11 2 MT"
strTbl_Name = "MR 2011"
strTo = "Mike"
strSubject = "QA Report: " & Now()
strDB = "Z:\__Mike\VSM_BAC.mdb"
DoCmd.SetWarnings False
Dim accapp As Access.Application
Set accapp = New Access.Application
accapp.OpenCurrentDatabase ("Z:\__Mike\VSM_BAC.mdb")
accapp.Visible = True

[strDB].SetFocus

DoCmd.OpenQuery "QA Report MR11 2 MT", acViewNormal, acEdit
DoCmd.Close acQuery, "QA Report MR11 2 MT"
strMessage = "Good morning! Here is the new QA report for " & Now() & "."
DoCmd.SendObject acSendTable, strTbl_Name, acFormatXLS, strTo, strSubject, strMessage, False
DoCmd.SetWarnings True
End Sub

 

[MikeStuck]

Hello!

I am stumped... I have several make-table queries I run in different Access databases that I run VB code from a button on a form that executes the query, makes the table, and emails it to selected recipients. Is there a way to run and email the query/table from one datadase in another? Here is my attempt so far (failed). I am guessing I need to set the focus somehow to the second db but am not having any luck. Would I need to re-set focus to the main db before I close out of the database? Apologies for not being smarter- I am self taught and cannot get this one.

Private Sub Command1_Click()

Dim strLoc As String, strRun As String
Dim strQry_Name, strTbl_Name As String
Dim strTo As String
Dim strSubject As String
Dim strMessage As String
Dim strDB As String
strQry_Name = "QA Report MR11 2 MT"
strTbl_Name = "MR 2011"
strTo = "Mike"
strSubject = "QA Report: " & Now()
strDB = "Z:\__Mike\VSM_BAC.mdb"
DoCmd.SetWarnings False
Dim accapp As Access.Application
Set accapp = New Access.Application
accapp.OpenCurrentDatabase ("Z:\__Mike\VSM_BAC.mdb")
accapp.Visible = True

[strDB].SetFocus

'*** Just going to the DoCmd.OpenQuery line gives runtime error 7874 Microsoft Access cannot find the object 'QA Report MR11 2 MT'.
'accapp.DoCmd.OpenQuery "QA Report MR11 2 MT", acViewNormal, acEdit '***Leads to runtime error 3734 The databasehas been placed in a state by user 'Admin'
'on machine MIKE VOS that prevents it from being opened or locked.

'srtDB.["Z:\__Mike\VSM_BAC.mdb"].SetFocus '***Leads to runtime error 424 Object Required


DoCmd.OpenQuery "QA Report MR11 2 MT", acViewNormal, acEdit
DoCmd.Close acQuery, "QA Report MR11 2 MT"
strMessage = "Good morning! Here is the new QA report for " & Now() & "."
DoCmd.SendObject acSendTable, strTbl_Name, acFormatXLS, strTo, strSubject, strMessage, False
DoCmd.SetWarnings True
End Sub

I have tried the above and noted their failures in case the info is useful... thanks!

 

[MikeStuck]

Remote queries are my friend! I changed the query in my "other" database from a make-table to select, then in my "regular" database I set up a remote query, queried the "other" db query as a make-table, and now can execute everything as needed. Not sure that is the best or most efficient way (though it may be for all I know!) but it works like a charm for me. Thought I'd share, as no one has posted anything different and this is a huge help to me. I got my info on remote queries from www.vb123.com, for what it is worth. Thanks to all who looked into this!