How to create database dynamically with VBA?

I have an application that I am planning to distribute as an Access Runtime application.  For archiving and data storage, I need to dynamically create ANOTHER database (or multiple databases) from within my application.  This is because the volume of data can become quite large, exceeding the 1GB limitation.  So I have a separate database for each year, then just select and merge in my application.  It works pretty well, but the outside databases must be set up first.

I can create new tables (with CREATE TABLE) but not a new database.  (using ADODB)

When I use CREATE DATABASE it says Operation not allowed when object is closed.  But if I say .OPEN, it says object not found.
MGothelfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pique_techCommented:
I don't think you can do it with "straight" ADO.  You need ADOX also.  Here is the code I used to do just what you're describing.  However, my code was meant to create a temporary database, so it looks for the target and if it finds it, deletes it.  REM out the line that starts If Dir(MakeLocalDBPathName.....) to inhibit this behavior.

Public Sub CreateLocalDataFile(Optional DBLocation As String = "C:\", Optional DBName As String = "LocalAccessData.mdb")
    'creates an Access database on the machine's harddrive
    'at the root of C: named according to the passed string value
   
    Dim AccApp As Access.Application:  Set AccApp = New Access.Application
    Dim cat As ADOX.Catalog:  Set cat = New ADOX.Catalog
    Dim cmd As ADODB.Command:  Set cmd = New ADODB.Command
   
    If Dir(MakeLocalDBPathName(DBLocation, DBName)) <> "" Then Kill (MakeLocalDBPathName(DBLocation, DBName))
   
    AccApp.NewCurrentDatabase MakeLocalDBPathName(DBLocation, DBName)
    AccApp.CloseCurrentDatabase
    Set AccApp = Nothing
   
End Sub
0
pique_techCommented:
Actually, I just realized:  you don't need either ADODB or ADOX.  I had that code in there from development:  it doesn't actually do anything.  So it should be:  

Public Sub CreateLocalDataFile(Optional DBLocation As String = "C:\", Optional DBName As String = "LocalAccessData.mdb")
    'creates an Access database on the machine's harddrive
    'at the root of C: named according to the passed string value
   
    If Dir(MakeLocalDBPathName(DBLocation, DBName)) <> "" Then Kill (MakeLocalDBPathName(DBLocation, DBName))
   
    AccApp.NewCurrentDatabase MakeLocalDBPathName(DBLocation, DBName)
    AccApp.CloseCurrentDatabase
    Set AccApp = Nothing
   
End Sub
0
pique_techCommented:
Ok...Third time's the charm, eh?  I took out too much.

Public Sub CreateLocalDataFile(Optional DBLocation As String = "C:\", Optional DBName As String = "LocalAccessData.mdb")
    'creates an Access database on the machine's harddrive
    'at the root of C: named according to the passed string value
   
    Dim AccApp As Access.Application:  Set AccApp = New Access.Application
   
    If Dir(MakeLocalDBPathName(DBLocation, DBName)) <> "" Then Kill (MakeLocalDBPathName(DBLocation, DBName))
   
    AccApp.NewCurrentDatabase MakeLocalDBPathName(DBLocation, DBName)
    AccApp.CloseCurrentDatabase
    Set AccApp = Nothing
   
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stevbeCommented:
What I have done in a similar situation is to leave an empty template of an mdb and then copy and rename it and then archive into it.

FileCopy   .... this is a built in command that will take any fiel and makea copy taking Source and Destination as parameters. From there it is only a matter of runnning your append queries to the new database and delete queries from your production database.

You cold create a new database with DAO (this will work in a runtime environm,ent) but I found that using a template is a whole lot easier.

DAO.DBEngine.CreateDatabase ...

Steve
0
pique_techCommented:
steveb:  That's a good idea.  I create the database as described above, then run all the DDL commands to create tables/queries/links, etc.   If you're certain that your databases will always look the same, though, (mine won't:  as I stated earlier, they're meant to be temporary *highly functional* local data cache) yours may be the better suggestion.
0
Steve BinkCommented:
I'm with stevbe.  If you are using the same database for each year, you don't need to create it each time.  Create the 'blank', and every time you need a new copy, copy the file using VBA, rename it, then populate it with what you need.  Overall MUCH easier.
0
stevbeCommented:
"So I have a separate database for each year"
I have an application that does exactly that ... my application is very simple in nature but hte volume is large.

I would imagine that your structure is the same and it is only a matter of *temporarily* populating some of the structure(s) then a full template would still work fine. If the issue is how to modify your queries to run against archived data you can actually call from a different mdb without it first being linked by including the full path to the mdb in the FROM clause.

CreateDatabase returns a DAO.Datbase object so you should be able to use .Execute against that object

Dim dbs As DAO.Database
Set dbs = DAO.DBEngine.CreateDatabase
dbs.Execute "CREATE TABLE ...", dbFailOnError

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.