Creating an Access database from within Excel

Question

I have the following code:

Sub CreateAccessDatabase()

    Dim accessApp As Object

    Dim dbs As Object

    On Error Resume Next

    Set accessApp = GetObject(, "Access.Application")

    If accessApp Is Nothing Then Set accessApp = CreateObject("Access.Application")

    If accessApp Is Nothing Then Exit Sub

    On Error GoTo 0

    accessApp.DBEngine.CreateDatabase "TempData.accdb", accessApp.dbLangGeneral

    Set dbs = accessApp.CurrentDb

    MsgBox dbs.Name ' Used to verify that the database was actually created

    accessApp.Quit

    Set dbs = Nothing

    Set accessApp = Nothing

End Sub

I receive the following error message when the accessApp.DBEngine.CreateDatabase... line is executed:

    Run-time error 438: Object doesn't support this property or method.

Can someone please tell me how to fix the aforementioned line so that it successfully executes?  Thanks in advance for any assistance.

Answer 1

You would have to create an Access.Application object the way you originally did.

Then open the database that you created, using

accessApp.OpenCurrentDatabase ...

You can then use Access commands, prefixing them all with accessApp:

accessApp.DoCmd.TransferSpreadsheet ...

Answer 2

CreateDatabase is a DAO method, not an Access method.

Try

Sub CreateAccessDatabase()

    Dim DAO As Object

    Dim dbs As Object

    Set DAO = CreateObject("DAO.DBEngine.120")

    Set dbs = DAO.CreateDatabase("TempData.accdb", ";LANGID=0x0409;CP=1252;COUNTRY=0")

    MsgBox dbs.Name

    Set DAO = Nothing

End Sub

The second argument of CreateDatabase is the value of dbLangGeneral; we can't use the symbolic constant since we're using late binding.

Answer 3

Thanks a million!

After you pointed out that CreateDatabase is a DAO method and not an Access method, I was unsure if I could still utilize the Access.Application object that I had created.

As always, I greatly appreciate your help.

Answer 4

CreateDatabase is a DAO method, not an Access method.

Ah, I didn't know that.  Your solution worked perfectly.  Thanks!

If I may ask one follow-up question:  Since your code does not create the accessApp object, how would I incorporate methods like DoCmd.TransferSpreadsheet and Execute in your code?

Thanks again.