Access Automation

Can anyone help with some Access automation? Here is my code...

' Initialize string to database path.
Const strPath = "C:\My Documents\"

strDB = strPath & "Test.mdb"

' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")

' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB

appAccess.Visible = True

appAccess.DoCmd.OpenForm "FormMain"

Now, it opens the database fine, but the db immediately closes for some reason. I have read somewhere that this code should be in a module (and you should open a form), otherwise, the Access object will fall out of scope and be closed. I placed this in a module, but it still closes.

Any takers?

Try Something Like This...

I pretty new at this but it works for me...


Private Sub Command1_Click()
Dim X As Object
Set X = CreateObject("Access.Application.8")

With X
.OpenCurrentDatabase DBpath 'Opens the Current Database file.
.Visible = True
.DoCmd.OpenQuery ("delete Final")
.DoCmd.TransferText , "textIMP", "Final", TFpath
End With

ErrHandler:
' Error handler displays the error message
' and then quits the program.
If Err <> 0 Then
MsgBox Err.Description, Title:="Automation"
X.Quit ' Close Microsoft Access.
End If

Set X = Nothing


End Sub

Thanks, surjer.

But, I figured it out.

I had to declare a GLOBAL object appAccess.

When I "dimmed" Access.Application within the procedure, when the procedure ended, the Access object that I had instantiated fell out of scope and was terminated by default.