Opendatabase with querydef

[Stoss]

I have a form, in which you can select a variety of option of where to output the data. Option 1 is to a spreadsheet, 2 is to an access report, and 3 it just to open a datasheet view. This is all based off of a crosstab query using a querydef (temporary query that gets deleted at the end of the sub).

Everything works fine and just how I would like when using CurrentDB. However, I wanted to make this a little better and add in options for other Databases.

So, before was

Set dbs = CurrentDB

And the new of what I would LIKE to do is the following. I can't seem to get it to work right. At best, it gives me an error saying there is no "Query". I thought this would work....

Select Case Me.FrameLocation.Value
        Case 1 'ORL
            Set dbs = CurrentDb
            strSelectedLocation = "ORL"
        Case 2 'PKS
            Set dbs = OpenDatabase("\\ps-s002\Maintenance\Database\PKS.mdb")
            strSelectedLocation = "PKS"
        Case 3 'SDL
            Set dbs = OpenDatabase("C:\SDL\SDL.mdb")
            strSelectedLocation = "SDL"
        Case 4 'DFW
            Set dbs = OpenDatabase("C:\DFW\DFW.mdb")
            strSelectedLocation = "DFW"
    End Select
    
    Set qdf = dbs.CreateQueryDef(strQName, strSQL)
    qdf.Close

So, what I want is when a person has their database open, they click on this form and choose which Location option (and any other option on the form). Then, it would either send the spreadsheet, open a report, or open a datasheet.

Is this possible?
-Stoss

 

[jdraw]

Here is some code I use to open a second database.
I'm sure you could adapt it as necessary.

'---------------------------------------------------------------------------------------
' Procedure : testdb
' Author    : Jack
' Created   : 12/8/2009
' Purpose   : Test opening second A2003 database.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testdb()

Dim dbsCurrent As DAO.Database
Dim dbsSecond As DAO.Database
Dim tbl As DAO.TableDef
   On Error GoTo testdb_Error

Set dbsCurrent = CurrentDb
Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase("d:\a2k\db1.mdb")
Debug.Print dbsCurrent.name
Debug.Print dbsSecond.name

For Each tbl In dbsSecond.TableDefs
Debug.Print "  " & tbl.name
Next
Debug.Print Now()

   On Error GoTo 0
   Exit Sub

testdb_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testdb of Module Module5"

End Sub

 

[Stoss]

Thanks for the reply, I will try that out and see if I can tailor it to my code.

Two questions on that:

1. What is a workspace? And, what is workspace(0) mean? I have seen this around in looking for a solution but never have I found anywhere where they explain what it is.

2. Because I am getting an error saying that the object (my querydef) cannot be found an indication that the database is opening but then not running the querydef, etc.?

Thanks,
-Stoss

 

[Stoss]

Follow up to my previous post....

Thanks for the code. I did try it but I get roughly the same results. Basically what happens is I execute the code and then I get an error saying the object can't be found (error 7874), which in a way makes sense because it has to be created. I did notice though that when if I leave the message up for a few seconds that the database is being open (i.e. I see the .ldb file open up in the directory) so I guess that is somewhat encouraging.

Not sure if that helps in what I am trying to do.

-Stoss

 

[Stoss]

Hoping someone can help with this, this is driving me bonkers. Doing a lot of searching on this and nothing seems to help or work.

Here is the complete code (except all the code that I did to make the SQL string)

    'Query Creation Variables
    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database
    Dim strSQL As String


   'Complete SQL string
    strSQL = sqlTransform & sqlSelect & sqlFrom & sqlGroup & sqlPivot

    Const strQName As String = "TC Breakdowns"
    
    'Set the desired location for database data
    Select Case Me.FrameLocation.Value
        Case 1 'ORL
            Set dbs = CurrentDb
            strSelectedLocation = "ORL"
        Case 2 'PKS
            Set dbs = DBEngine.Workspaces(0).OpenDatabase("\\ps-s002\Maintenance\Database\PKS.mdb")
            strSelectedLocation = "PKS"
        Case 3 'SDL
            Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\SDL\SDL.mdb")
            strSelectedLocation = "SDL"
        Case 4 'DFW
            Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\DFW\DFW.mdb")
            strSelectedLocation = "DFW"
    End Select
   
    Set qdf = dbs.CreateQueryDef(strQName, strSQL)
    qdf.Close
   
    Dim strPathName As String
    strPathName = "\\s001\records\Records and Info\Reports\TCStats\" & strSelectedLocation
   
    If Me.FrameSendDataTo.Value = 1 Then 'Sends to Spreadsheet
        If Me.FrameBreakdownBy.Value = 1 Then 'Month layout
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQName, strPathName & " - TCMonthStats.xls", False
        Else
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQName, strPathName & " - TCCategoryStats.xls", False
        End If
    ElseIf Me.FrameSendDataTo.Value = 2 Then 'Sends to Report
        Dim stDocName As String
        strXFilter = CStr(dStartDate)
        stDocName = "rptTCTotalsByMonth"
        DoCmd.OpenReport stDocName, acPreview, , , , strSQL
    ElseIf Me.FrameSendDataTo.Value = 3 Then 'Sends to Datasheet View
        DoCmd.OpenQuery (strQName)
    End If

    'Cleanup
    dbs.QueryDefs.Delete strQName
    Set qdf = Nothing
    dbs.Close
    Set dbs = Nothing
    
    Exit Sub

Any help would be greatly appreciated!
-Stoss

 

[Stoss]

As I have been playing around with this for countless hours, it seems that the query gets created and also it can be deleted but I still can't utilize it. I am still getting an error saying the object doesn't exist.

Hoping someone can help!
-Stoss

 

[LPurvis]

I think there's a discrepency between what you're attempting and what you want.
But that involves knowing what you want. ;-)

What my guess is that you want:
You have a running application and you want an Access Form, Report or Datasheet (yek) to open to display results of a crosstab query. That is based on a querydef which is created at runtime. You may want that querydef to source tables which are in another database than those in the currently running application (or at least linked to in the currently running appplication).

Obviously the simplest course of action would be to add linked tables to the other databases. You can then query any of them as required - using a local query definition.
You could add those links at runtime if need be - using paths to those files determined at runtime.

The code you've been using so far has created query defintions inthose external db files. You're then completely unable to make your local Access objects use those external queries.

The alternative to linked tables would be to form your queries including the external path information.
For example instead of
SELECT FieldA, FieldB, FieldC
FROM TableName
WHERE FieldName = 'SomeValue'

construct

SELECT FieldA, FieldB, FieldC
FROM [\\ps-s002\Maintenance\Database\PKS.mdb].TableName
WHERE FieldName = 'SomeValue'

Though in your code it looks like you're building the entire crosstab in your specified database. If that's what you're doing then there is no intermediate query (though that would probably offer the simplest solution to use).

Cheers.

 

[Stoss]

I must say, you guys on this website are unbelievable! Your knowledge is 2nd to none!

Thanks you so much!!!!!

I put the path to the different DB's into the SQL string (From section) and it worked perfectly. That was exactly what I wanted! I had no idea that was even possible. Now, I run that SQL, it pulls the data from the other sources and then treats it like it was on my own copy of the DB. PERFECT!

1 small question. In the SQL statement...

FROM [\\Sdl-d005\sdl\SDL.mdb].[Maint Trouble Calls] RIGHT JOIN [\\Sdl-d005\sdl\SDL.mdb].CarData ON ...........

Do all the references to those tables in the rest of the FROM statement know that it uses the external DB's? Meaning, if I use later in the FROM statement something like [Maint Trouble Calls].Whatever, does this know that the [Maint Trouble Calls] is from "\\Sdl-d005...."?

Seriously, keep up the good work! You guys are awesome!

-Stoss

 

[LPurvis]

Glad you're sorted.

Yes, they really don't have a choice - the tables sourced in the FROM clause are the only ones available for selection and you can reference them by just the table name.
However with more than one table I prefer to alias them anyway (both for brevity and precision).

My earlier example could then be:

SELECT T.FieldA, T.FieldB, T.FieldC
FROM [\\ps-s002\Maintenance\Database\PKS.mdb].TableName T
WHERE T.FieldName = 'SomeValue'

Yours might be:

FROM [\\Sdl-d005\sdl\SDL.mdb].[Maint Trouble Calls] M RIGHT JOIN [\\Sdl-d005\sdl\SDL.mdb].CarData C ON M.FieldName = C.OtherFieldName

Bear in mind you could determine those paths at runtime and construct your SQL strings in VBA, rather than hardcode those paths into your queries permanently. Things change.

Cheers