Visual Basic 6.0 and Access

[tariq123]

Hi,

I seem to be having problems with this code below, I have a Form made using Visual Basic 6.0 and a command button, inside the command button is:

Dim AccessApp As New Access.Application 
Set AccessApp = CreateObject ("Access.Application") AccessApp.OpenCurrentDatabase ("C:\music.mdb") AccessApp.Visible = True AccessApp.DoCmd.OpenForm "frmmusic"

Now, what I'm trying to do is open a current database from this one command button, the database is stored on C:\ called music.mdb this seems to work but the problem i seem to be having is once Microsoft Access opens.. it closes straight away, any suggestions on how I can resolve this problem?

[RobDog888]

Is this the way you actual code is - all on one line? Your first
line has the dim and new keywords so it is creating a new access
application there. Then you set it to createobject to another
access application. You dont use both.

Private Sub Command1_Click()
 
    Dim AccessApp As Access.Application 
 
    Set AccessApp = New Access.Application   
    AccessApp.OpenCurrentDatabase ("C:\music.mdb")   
    AccessApp.Visible = True 
    AccessApp.DoCmd.OpenForm "frmmusic"
 
End Sub

Something like this show fix it.

[tariq123]

Hi,

No, The code isn't all on one line, and the fix you gave doesn't seem to fix the problem, it opens MS Access and closes instantly, are there any fixes to keep Access open for the user?

this is basically the same as what the code i posted above is doing, help in fixing this problem will be greatly appreciated. thx.

[DaveBo]

Are you doing either or both of these:
AccessApp.CloseCurrentDatabase
Set AccessApp = Nothing

I'm having a similar problem, but it seems to relate to whether or not my Access DB starts up with the switchboard form and/or the database window.

If it starts up with both of these already, and I'm opening a separate form (not the switchboard) from the VB code
I can still do the Set AccessApp = Nothing and everything works fine. When closing the form itself, then all of Access goes away.

It's all magic. I don't understand it.
In my case I wouldn't be surprised if something was corrupted.

[tariq123]

Hi,

I'm using none of them, what I am trying to do is open up a current database stored on C:\ just by using a command button. I have succeeded in doing so, but the problem is keeping it open, once the database is open it just closes instantly. if there is a fix for the code above then that would be great if someone could also point out what seems to be going wrong.

[DaveBo]

Try making a dummy form or switchboard and
in Tools - Startup... put that in Display Form/Page

[TalonSoftware]

Move your dim statement.
When the sub is done, the variable falls out of scope and releases. Since you then no longer have a valid handle on Access, it thinks you are done and closes.

It's as simple as that.

[RobDog888]

No, its not as simple as that. The code works on Access 2003 just
fine even though the variable goes out of scope. It is not invoking
the accApp.Quit or Set accApp = Nothing so it should stay open
depending it its not version specific.

Option Explicit
 
Private Sub Command1_Click()
 
    Dim AccessApp As Access.Application
 
    Set AccessApp = New Access.Application
    AccessApp.Visible = True
    'Make sure you set the macros warning to low just for testing.
    'Then you can open the db with no problems.
    AccessApp.OpenCurrentDatabase ("D:\development\music.mdb")
    AccessApp.DoCmd.OpenForm "frmMusic", acNormal, , , acFormEdit, acWindowNormal
 
End Sub

[DaveBo]

Below is code I'm working with right now.
With my switchboard form set as Startup it works fine.
When I set Startup form = none it flashes and dissappears.

Const Vision_Path = "E:\Vision.MDB"
Dim accApp As Access.Application
 
Private Sub cmdVisionOpenForm_Click()
    Set accApp = New Access.Application
 
    accApp.OpenCurrentDatabase (Vision_Path)
    accApp.Visible = True
    accApp.DoCmd.OpenForm "Vision_Lookup"
 
    Set accApp = Nothing
End Sub

Do you have a startup form set in your Access DB?

[RobDog888]

No just a blank db with one empty form. Do you do a step by
step execution of code (F8)? Also, are your macros set to what
level? If they are not enabled the db may not open and if you
have no error trapping then it will close.

[TalonSoftware]

Originally posted by RobDog888
No, its not as simple as that. The code works on Access 2003 just
fine even though the variable goes out of scope. It is not invoking
the accApp.Quit or Set accApp = Nothing so it should stay open
depending it its not version specific.

Option Explicit
 
Private Sub Command1_Click()
 
    Dim AccessApp As Access.Application
 
    Set AccessApp = New Access.Application
    AccessApp.Visible = True
    'Make sure you set the macros warning to low just for testing.
    'Then you can open the db with no problems.
    AccessApp.OpenCurrentDatabase ("D:\development\music.mdb")
    AccessApp.DoCmd.OpenForm "frmMusic", acNormal, , , acFormEdit, acWindowNormal
 
End Sub

Ok, given your code, I buy that, given the original code which used a CreateObject..... not so sure, still think the variable falling out of scope would kill it.

But anyways.....

Do you need to control the DB or access it? Or are you just trying to open the Access file? Would a ShellExecute on the mdb work in this case? If no automation is needed and all you want is the database open for the user to use, I don't see why that wouldn't work.