Open 2nd instance of database; vars get wires crossed 1

[smandoli9]

Access 2002, split server/client, DAO. I want the user to be able to invoke, from a form, a second instance of the database; the user can then compare records between the two. I have tried

Private Sub btnLaunch_Click()
    Dim q As String:    q = """"
    Shell "MSAccess.exe " & q & "C:\path\MyApp.mdb" & q, _
                vbNormalFocus
End Sub

But my VBA variables don't distinguish between the two instances. Havoc.

How can I do it? (And, would the New keyword be helpful?)

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[Remou]

Hi
Maybe:

Dim appAccess As Access.Application
    ' Initialize string to database path.
    strDB = "C:\Tek-Tips.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB
    appAccess.Visible = True
    ' Open Orders form.
    With appAccess
        .DoCmd.OpenForm "Orders"
        'Update a field
        .Forms!Orders.txtID = 123
    End With

 

[smandoli9]

This would seem to make the #2 instance a var of #1, which I don't want. I want free-standing instances that can be (for example) closed in any order. Am I looking at this right?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[Remou]

Sorry, I did not read that properly. You want two copies of the same database? Two different databases seemed a little odd, two the same seems odder still. I don't quite get what you are trying to do.

 

[smandoli9]

My database allows a user to work with one project at a time. I want the user to be able keep one project without interruption, but have full reference to other projects for brief ad-hoc needs. That way the user can work intensively on the project at hand but field other needs too.

I don't want another copy of the database; I want (what I would call, maybe through ignorance) another instance of the same database. Or even a third instance.

Can that be done?

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[Remou]

... Hopefully someone will step in to say ...

 

[rubbernilly]

Why can't those brief ad-hoc needs be built out of the current database?

What about an unbound form that would retrieve information from another project without the ability to change it and without the lock down of having the record open (at least not after the form is populated)?

It sounds like you want to get across the parking lot, but to do it you want to get on a plane, land in the next city, and then take the bus back.

Can you explain why any of the more conventional solutions would not work for you? (So that we can - good naturedly - tell you why you're wrong... ;-) )

I always say not to question the database layout until you understand the data inside and out, so maybe there really is a reason you would need to do this... I just can't see what that would be.

 

[smandoli9]

To clarify one point, the 'ad-hoc" needs are not limited to read-only.

Projects have several layers of drill-down. Projects have multiple components lists (BOMs); the lists drill down to component details. To achieve this I have forms daisy-chained. Global vars hold the sequencing together (though I am making a transition to OpenArgs, along with use of a nifty FormTransfer sub). I don't the users' access to forms to be totally linear, but setting up variable paths takes a lot of work.

Web browser analogy: I can log on to Tek-Tips and review the site using two open browsers. So I could monitor two different threads, both visible on my desktop at once, and dialog through whichever one I have as the active window.

Excel analogy: Open a multi-tab worksheet, create a New Window, set them to different tabs and enjoy symultaneous review and edit.

This just seemed how it would work with Access. But, my global vars keep the same instance, so when I adjust a record in Instance 1, the results may be written to the project in instance 2.

Had I KNOWN I was in La-La Land, courting the good-natured astonishment and correction of those less ignorant than myself ... ah well, I'm past petty personal 'image-management' at this point.

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[rubbernilly]

Easy, man. "good-naturedly" was the operative word.

It sounds like what you want to be able to do would be accomplished with a Class.

You would have a MyProject Class (to get by the possible reserved-word-problem of "Project"), and that form would have a Form (which is basically the form you are already using). Then you can instantiate a new MyProject everytime the user's want to, and each class would have its own form and properties and tied record.

You would have to manage record locks, but this way the users could have any number of project windows open within their database.

Sound like a possibility?

 

[smandoli9]

Thanks R-man. Your good humor was appreciated, I was just reciprocating.

That does sound promising. Unknown territory. I've used snippets including classes but not built my own -- "Know the code you borrow" not always fully obeyed by me

But looks like my trusty Wrox text will help me get there.

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]