Pass Control to Access From VB

[markryan57]

I have a VB program that acts as an interface for different functions - I am trying to run an Access program, where (for now) control is passed to Access from VB. When Access is done and the db is closed - the VB app cannot open another instance of Access (which is necessary).

How do I launch Access from VB and pass control to Access?

[Killer42]

I have a VB program that acts as an interface for different functions - I am trying to run an Access program, where (for now) control is passed to Access from VB. When Access is done and the db is closed - the VB app cannot open another instance of Access (which is necessary).

How do I launch Access from VB and pass control to Access?

What version of VB? And what happens when you try to open Access again?

[markryan57]

What version of VB? And what happens when you try to open Access again?

Sorry it is VB6 - and the second instance does not open when launched from the VB6 application. When I look at currently running apps (doing Ctrl - Alt - Del) I see the first instance open. (this is while the VB app is still running).

Thank you for any help you might offer.

[markryan57]

What version of VB? And what happens when you try to open Access again?

One more thing Killer I am using Opencurrentdata base to open the Access DB. I am also trying to not use access but do the many queries they have from within the VB app.

[MMcCarthy]

One more thing Killer I am using Opencurrentdata base to open the Access DB. I am also trying to not use access but do the many queries they have from within the VB app.

Mark

Can you post the code your are using to open and to close the application.

Automation is tricky and if an instance of the application is still open it will have problems opening another one. In other words you could be closing the database but leaving the MSAccess.exe process open in the background without being aware of it. If you wanted to check you could look at the processes list (using Ctrl Alt Del) and see if the MSAccess process is still running.

Alternatively if you are not setting you database variable to Nothing when you are finished with the first database it will have problems assigning the second database to the variable.

I'll be able to tell more once I see the code.

Mary

[NeoPa]

It may be interesting for soemone with the experience to put a short tutorial together on how to link to, and use, (Office) applications from VB (or VBA).
I haven't done it since a course I did many years back. I'll see if I can dig up some notes.
It does sound, though, as Mary says, that you've closed the database but not the application. This is good in a way as it means you can just open the other database rather than re-initialising the whole application.

[NeoPa]

Try this Tutorial in the Access forum (Application Automation).

[markryan57]

Mary, et. el.,

Here is the code I use for the first instance.

=============== =============== ============

Private Sub cmdStep1_Click()
    If cboSelectName.Text <> "" Then
    FolderName = cboSelectName.Text
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")
    OpenCurrentDatabase "L:\GROUPS\PROJECTS\PODS - Data Quality\csiCharlotte\BackEndAnalysis\" & FolderName & "\" & FolderName & "'sMasterFolder\Access Database Version 1.C -- Test Template.mdb"
    objAccess.Visible = True
    lblMessageArea.ForeColor = vbRed
    lblMessageArea.Caption = "Database Run Complete - Go to Step 2."
    Else
        MsgBox "Problem - Please Select Name - before continuing...", vbOKOnly, "TA Inforce Error"
    End If
 Set objAccess = Nothing
End Sub

=============== =============== ============

The second instance is opened the same way as the first. And yes doing a Ctrl-Alt-Del does show the instance open. If I exit the application and re-open the instance goes away, and the second one is able to run okay.

I thought there was a way of opening Access from VB and release control from VB. If I could do that, I would be able to do a closecurrentdat abase when done.

Thanks so much for your help folks.

Kindest regards,

mark

[MMcCarthy]

Hi Mark,

Try this ...

Private Sub cmdStep1_Click()
Dim objAccess As Object

	If cboSelectName.Text <> "" Then
		FolderName = cboSelectName.Text

		Set objAccess = GetObject(, "Access.Application")
		objAccess.OpenCurrentDatabase "L:\GROUPS\PROJECTS\PODS - Data Quality\csiCharlotte\BackEndAnalysis\" & FolderName & "\" & FolderName & "'sMasterFolder\Access Database Version 1.C -- Test Template.mdb"
		objAccess.Visible = True
		lblMessageArea.ForeColor = vbRed
		lblMessageArea.Caption = "Database Run Complete - Go to Step 2."
	Else
		MsgBox "Problem - Please Select Name - before continuing...", vbOKOnly, "TA Inforce Error"
		Exit Sub
	End If

	objAccess.Quit
	Set objAccess = Nothing

End Sub

[NeoPa]

Mary, et. el.,

Here is the code I use for the first instance.

=============== =============== ============

Private Sub cmdStep1_Click()
    If cboSelectName.Text <> "" Then
    FolderName = cboSelectName.Text
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")
    OpenCurrentDatabase "L:\GROUPS\PROJECTS\PODS - Data Quality\csiCharlotte\BackEndAnalysis\" & FolderName & "\" & FolderName & "'sMasterFolder\Access Database Version 1.C -- Test Template.mdb"
    objAccess.Visible = True
    lblMessageArea.ForeColor = vbRed
    lblMessageArea.Caption = "Database Run Complete - Go to Step 2."
    Else
        MsgBox "Problem - Please Select Name - before continuing...", vbOKOnly, "TA Inforce Error"
    End If
 Set objAccess = Nothing
End Sub

=============== =============== ============

The second instance is opened the same way as the first. And yes doing a Ctrl-Alt-Del does show the instance open. If I exit the application and re-open the instance goes away, and the second one is able to run okay.

I thought there was a way of opening Access from VB and release control from VB. If I could do that, I would be able to do a closecurrentdat abase when done.

Thanks so much for your help folks.

Kindest regards,

mark

Mark,
Please remember to use the code tags.
I'm not able to moderate this forum so I can't fix it for you :(
Anyway, your OpenCurrentData base line is not a reference of the objAccess object, so all that went before seems unrelated. I'm afraid I know nothing about OpenCurrentData base, but I do know that you need to use something within the Access application for that code to work as indicated in the tutorial.

[NeoPa]

I've just seen Mary's post which seems to be along the same lines.
I also checked out OpenCurrentData base which seems to be a valid Access procedure. It references an Application object (as suggested) but I also noticed in your call you use FolderName twice.

[markryan57]

Yes Neo, thank you for your reply also. I use foldername twice because the folder names are L:\?????\????\m ark\mark'sfolde r\???? I use foldername twice to get to the exact folder name. If you notice the second foldername variable has an 's next to it.

Neo you also asked me to use code tags when I post. I am not sure what that means.. (sorry) new to this. Can you explain further?

Thank you so much for your help.

regards,
mark

[markryan57]

Mary,

Thank you so much for the response. I think I may have tried that approach also, but it closed the access db connection before I was able to use it. I will try it tomorrow morning when I get to the office.

If it works I will owe you one.

thank you once again,

Kindest Regards,

mark

[NeoPa]

Neo you also asked me to use code tags when I post. I am not sure what that means.. (sorry) new to this. Can you explain further?

In the 'Post Reply' page, there are a number of buttons that you can use to help formulate your message. The code button (#) will surround the selected text in [code]...[ /code] tags that cause the contents to be displayed in a fixed width font. They do not word wrap though so care should be taken to restrict the length of the lines used that way.

[MMcCarthy]

Mark

Check out post #9 again before using it as I made a couple of small amendments. The problem is if you open the database using vb code and then don't close it in the vb code. You are depending on the user closing it correctly. If they use the windows close button ('x') this doesn't always close the ldb file and can leave the process of access open in the background. You need to find some way to trigger the code to close the file. This is the quit statement I added to the code (replacing the close statement).

Mary