?
Solved

How to create a vba procedure in Access 2010 to create a new database and export tables to the new Db

Posted on 2014-11-26
4
Medium Priority
?
395 Views
Last Modified: 2014-12-08
Hi Experts

In Access 2010 I need a vba procedure to create a new database and export all the tables to this new database.
0
Comment
Question by:simsima_7876
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40466776
try this codes
Sub CreateNewDB()
Dim ws As Workspace
Dim db As Database
Dim strPathName As String

'Get default Workspace
Set ws = DBEngine.Workspaces(0)

'Path and file name for new db file
strPathName = CurrentProject.Path & "\NewDB.accdb"

'Make sure there isn't already a file with the name of the new database
If Dir(strPathName) <> "" Then Kill strPathName

'Create a new db file
Set db = ws.CreateDatabase(strPathName, dbLangGeneral)
db.Close
Set db = Nothing

End Sub
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40466814
here is the code to export the tables

Sub exportT()
Dim td As DAO.TableDef, db As DAO.Database, sql As String, strPathName As String
strPathName = CurrentProject.Path & "\NewDB.accdb"
Set db = CurrentDb
For Each td In db.TableDefs
    If Not td.Name Like "Msys*" Then
        sql = "SELECT [" & td.Name & "].* INTO [" & td.Name & "] IN '" & strPathName & "' FROM [" & td.Name & "]"
        db.Execute sql
    End If
Next
End Sub
0
 

Author Comment

by:simsima_7876
ID: 40486495
Thanks Ray.
That does it.
0
 

Author Closing Comment

by:simsima_7876
ID: 40486496
Thanks Ray
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question