Remote queries (1 Viewer)

[Access9001]

What I have:

exporter mdb
source mdb

Relevant query is in source mdb

I want to, through exporter mdb, execute the relevant query in the source mdb and then either output it as an html file or place it in an html-formatted string variable in VBA.

Any help with this would be much appreciated. Thanks.

 

[mikebaldam]

Try having a look at the export report features, it can be exported as a HTML file and hold data from your query

 

[Access9001]

The real issue though is accessing the data executed from a remote query, though -- I can output things if I have my outputter in the same db as the queries/tables/etc, but I want to be able to "reach out" to a separate db containing a given query and then be able to access that output.

 

[mikebaldam]

Can you link the data..?
If you can link the table so you can have a local query is probably easiest

Or try an export /import

Export a Query

Dim db As Database
'Dim db As DAO.Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim sDate As String

On Error GoTo Command126_Click_Error

Set db = CurrentDb()
sDate = Format(Now, "yyyymmdd-hhnnss")


sExportLocation = "C:\Queries\" 'Do not forget the closing back slash! ie: C:\Temp\

'Create Folder
Dim FSO As New FileSystemObject
Dim strPath As String, strCopy As String

strPath = sExportLocation

'MsgBox strPath
If FSO.FolderExists(strPath) = True Then

Else
FSO.CreateFolder (strPath)
End If

Application.SaveAsText acQuery, Me.lstQryName.value, sExportLocation & Replace(Me.lstQryName.value, "/", "-") & ".txt"

Import a Query

'requires ref microsoft office 11.0 object library set
Dim dlg As FileDialog ' I get a Compile Error: User-defined type not defined
Dim strFileName, strFileNameOnly
Dim sImport
On Error GoTo cmdImport_Click_Error

Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.TITLE = "Select the Query you'd like to import"
.InitialView = msoFileDialogViewDetails
.InitialFileName = "C:\Queries\"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "txt Files", "*.txt", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
strFileName = .SelectedItems(1)
Else
Exit Sub
End If
End With

strFileNameOnly = Mid(strFileName, 39) 'remove first 39 characteres
strFileNameOnly = Replace(strFileNameOnly, ".txt", "") 'remove .txt
'IMPORT

Application.LoadFromText acQuery, strFileNameOnly, strFileName

You could auto export/import the queries then run a routine to do whatever you need on them..?