Control MS Access database form from excel

I have the following code that is used in an excel spreadsheet to send cell information to some variables.  I now need the code to check for an already open MS Access database (if the database is not open I need a msgbox that advises the database must be open to edit items) and use the variables to open a form, filter for a specific record and display the requested record.  I have searched up and down and can't seem to find what I need, everything seems to open the database first, but in this case I want the user to already have the database open.

Variable Clickrange is an integer set on open of the excel file and corresponds to a max row range that is included in the clickable range.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call openitem(Target.Row, Target.Column)
End Sub


Public Function openitem(myRow As Integer, myColumn As Integer)

If myRow <= myClickrange And myRow > 2 And Left(Format(ThisWorkbook.Sheets("Skyline").Range("B" & myRow).Value, "mmm-yy"), 4) <> "Jan-" Then
    myInfo = ThisWorkbook.Sheets("Skyline").Cells(myRow, myColumn).Value
    myFirstbreak = InStr(1, myInfo, "(")
    mySecondbreak = InStr(1, myInfo, ")")
    Projectnum = Left(myInfo, myFirstbreak - 2)
    Customer = Mid(myInfo, myFirstbreak + 1, (mySecondbreak - myFirstbreak) - 1)
    Programnum = Right(myInfo, (Len(myInfo) - mySecondbreak) - 1)
   
    'I need the code to go here that will open frmMyform if that form is not already open, will enter the 3 above variables into text boxes A, B, and C and then will refresh the form

End If

End Function
redrpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It's quite difficult to connect to a running instance of Access (or any other program, for that matter). You'd have to interate through all open "windows", and find the right one (generally by the Title, which can be tricky if you change the Title of the app based on user actions).

If you know that your Access app is the ONLY Access application that will be running, you can use GetObject to connect to it. See this KB article for information on using GetObject:

http://support.microsoft.com/kb/288902

That article states the following:

"You can attach to a specific instance if you know the name of an open document in that instance."

I'm not sure what an "open document" would be in terms of Access, but you could try using the name of a Form that you know will be open.

Otherwise, it's back to the Windows API to search through all open windows in hopes of getting the right instance.

What is the role of Access in this process, and why can't you do this entirely in Excel? If we understand more about the interaction between your two apps, we might be able to suggest a better route.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
...or move it all to Access...?
0
NatchiketCommented:
You could use vba to check if there is an locking file associated with the database file.  If there is  then the database is in use.

As for opening a specific form etc ... a clunky but workable solution would be to have an invisible timer form in access which starts when the database loads maybe checks every five minutes or so  for a file which the excel vba creates and then drives the opening of the required form  etc...
0
redrpAuthor Commented:
I would love to move everything to either one or the other however I think I am pretty stuck in the setup that I have right now.  The basic design is such that all the raw data is housed in access and it is all edited, added to and deleted from access using different forms.  The database at a certain point sends data to an excel file to be graphically displayed in the form of a skyline or stacked boxes that represent different items.  The skyline needs to be in excel because many users that don't have access loaded need to interact with it.  The user would like to be able to produce a skyline and then, while auditing the skyline they would like the ability to double click on one of the cells in excel that represents an item and have the database open the edit form and bring up the record for the item that is double clicked on, thereby allowing them to edit the item in the database.  I may just have to instruct them that they can only have the specific database open to use this functionality.  LSMConsulting, you mentioned that I may be able to look for the name of the open windows and interact with one, can you please post some info on this?  I don't believe the database window name would change so I may have a chance.  Thanks for all the help thus far.
0
redrpAuthor Commented:
No solution was found, user was asked to modify entry behaviour.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.