[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 820
  • Last Modified:

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.
0
MGothelf
Asked:
MGothelf
  • 4
  • 2
2 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now