Running an Access DB macro from within Windows Application

[loneranger]

Hi Everyone

Visual Studio 2005
VB 2005
Windows Application

I have a form with one combo box and a button

I want the user to make a selection from the combo box, click the button and have the application run an Access 2003 Macro, the database is embedded in my project.
I receive the following message when the button is clicked:


Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user

The problem must be with the filepath in my code here is the code under the button click event:

Select Case ComboBox1.SelectedItem
 
            Case "Clear Form"
 
                Dim oAccess As Access.ApplicationClass
 
                'Start Access and open the database.
                oAccess = CreateObject("Access.Application")
                oAccess.Visible = False
                oAccess.OpenCurrentDatabase("|DataDirectory|LTDatabase.mdb", False)
                'Run the macro
                'oAccess.Run("test2")
 
                'Clean-up: Quit Access without saving changes to the database.
                oAccess.DoCmd().Quit(Access.AcQuitOption.acQuitSaveNone)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
                oAccess = Nothing
 
        End Select
 
        GC.Collect()
 

If I change the path text to a copy of the Db on my C: drive "c:\LTDatabase.mdb" it works perfectly, so what is wrong with my path? ("|DataDirectory|LTDatabase.mdb", False)
I'm sure it is something very basic that I'm missing....any thoughts would be appreciated.

Lone

UPDATE
It's just dawned on me that Access has no chance of finding "|DataDirectory|LTDatabase.mdb so how am I going to have this functionality in a deployed application mmmmmm scratching head

[nukefusion]

I would say the problem is with the path string "|DataDirectory|LTDatabase.mdb".

You can use a database in a deployed application no problem. If the database is local to the application, upon deployment you can copy it to the ApplicationData folder and work from it there.

You can the open the database using code similar to the following:

Dim oAccess As Access.ApplicationClass
 
        'Start Access and open the database. 
        oAccess = CreateObject("Access.Application")
        oAccess.Visible = False
 
        Dim dbPath As String
        ' You can use Environment.SpecialFolder.ApplicationData or Environment.SpecialFolder.CommonApplicationData
        ' depending on whether the DB needs to be accessed by just the current user or all users of the computer
        dbPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\LTDatabase.mdb"
        oAccess.OpenCurrentDatabase(dbPath, False)

[loneranger]

Thanks very much for your reply nuke,

Have added the code but am still receiving the same error....As this is my first windows app deployment I'm going to go away and investigate the use of the ApplicationData folder and how it fits into the scheme of things, just very unclear to me at the moment.