How can a blank MS Access database be created using VBA?

Question

I'm a total noob trying to create a blank MS Access database using VBA in Excel. I want to name the new database "tblImport". This is the code I´m using:

   Sub makedb()
   Dim accessApp As Access.Application
   Set accessApp = New Access.Application
   accessApp.DBEngine.CreateDatabase "C:\tblImport.accdb", dbLangGenera
   accessApp.Quit
   Set accessApp = Nothing
   End Sub

I get the following error message:

"Run Time Error 3001: Application Defined or Object Defined Error"

What can I do?

jpw · Accepted Answer · 2013-08-04 09:42:57Z

11

The name of the locale constant in the CreateDatabase method is wrong:

This:
accessApp.DBEngine.CreateDatabase "C:\tblImport.accdb", dbLangGenera

Should be:
accessApp.DBEngine.CreateDatabase "D:\tblImport.accdb", DB_LANG_GENERAL

Change that and your code should work. (It does for me at least).

answered Aug 4, 2013 at 9:42

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Siddharth Rout Over a year ago

++ Yup that works but that is not the reason for the error message :) See my answer.

MT1 Over a year ago

This works in Access365.

Siddharth Rout · Accepted Answer · 2017-03-15 06:38:09Z

5

Old Question. Here are my two cents. You have a typo...

dbLangGenera should be dbLangGeneral

More about it in Workspace.CreateDatabase Method (DAO)

Voting to close this question as per Dealing with questions with obvious replies

Try this. This works.

Sub makedb()
    Dim accessApp As Access.Application
    Set accessApp = New Access.Application

    accessApp.DBEngine.CreateDatabase "C:\tblImport.accdb", dbLangGeneral
    accessApp.Quit

    Set accessApp = Nothing
End Sub

EDIT: Will delete this answer and post it as a comment once the post is closed.

edited Mar 15, 2017 at 6:38

answered Mar 15, 2017 at 6:11

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Comments

brainac · Accepted Answer · 2014-01-02 16:35:29Z

3

Old question, but it was useful for me. Seems like you don't even need Access object.

Access.DBEngine.CreateDatabase "D:\tblImport.accdb", DB_LANG_GENERAL

works fine for me.

answered Jan 2, 2014 at 16:35

brainac

4104 silver badges12 bronze badges

1 Comment

CWilson Over a year ago

I don't oppose this particular line, but I suggest strongly against the commented philosophy: not having error catching is also less code, leaving screen updating on is less code, etc. It is rare, in vba, where fewer lines of code translates to both a more robust implementation and higher efficiency, and often leads to neither.

ForceMagic · Accepted Answer · 2014-02-03 21:30:06Z

2

You should use the first method if you intend to use the automation object you've created. You can open forms, use DoCmd statements, etc., simply by prefacing each statement with "accessApp." For example, I use TempVars to hold user data. I can open a related database using automation, then do

accessApp.TempVars.Add "CurrentUser", TempVars.CurrentUser

to pass the CurrentUser value from the active database to the new database.

When all you want to do is create a new database to do a function such as DoCmd.TransferDatabase on, you can just use

Access.DBEngine.CreateDatabase "D:\tblImport.accdb", DB_LANG_GENERAL

edited Feb 3, 2014 at 21:30

ForceMagic

6,42712 gold badges71 silver badges93 bronze badges

answered Feb 3, 2014 at 21:08

topher23

412 bronze badges

1 Comment

ForceMagic Over a year ago

Welcome on SO. Unfortunately, it is not every new user which can write good post like you did. Although, you might be interested in those posts on Meta : meta.stackexchange.com/a/114538/181303 and meta.stackexchange.com/questions/12119

Collectives™ on Stack Overflow

How can a blank MS Access database be created using VBA?

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related