By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

VBA Code to Export all visible objects to a new ".mdb" database

P: 9
Please I need a VBA code to export all visible database objects (queries and table links) into a new “.mdb“ database, save with a different name.
Please help me with complete code as I am just a beginner,
Apr 3 '10 #1
Share this Question
Share on Google+
6 Replies


P: 83
Henri,

Why not create a new empty database, then import (get external data-> import)
the objects from your current database; then delete any specific items you absolutely don't want?
Apr 3 '10 #2

P: 9
OrangeCat,
Thanks for reply, that’s right, I can do it that way, but I just want it to automate and make it simple for other users, as I am setting up this database to perform as a intermediate query builder utility, where user can link the tables from several databases, create the queries based on these, and then both queries and their source table links stored in separate database, then users will use these queries in another program (GIS), sure I need it to be done from my query builder utility, so please help,
Best regards,
Heri.
Apr 3 '10 #3

P: 9
No any response? is it impossible task?
Apr 5 '10 #4

P: 9
This has been sorted.
Apr 6 '10 #5

P: 83
Have you solved your issue?
What did you do?
Apr 6 '10 #6

P: 9
Cat, See below.

Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateNewDB()
  2. Dim strTargetDB As String
  3. Dim qdf As QueryDef
  4. Dim tbl As TableDef
  5. Dim strNewDB As String
  6.  
  7. strNewDB = InputBox("Please Enter New Database Name", "Database Name", "New_database")
  8. On Error Resume Next
  9.   strTargetDB = CurrentProject.Path & "\" & strNewDB & ".mdb"
  10.     DBEngine.CreateDatabase strTargetDB, dbLangGeneral
  11. For Each qdf In CurrentDb.QueryDefs
  12.  If Left$(qdf.Name, 3) = "qry" Then
  13.     DoCmd.TransferDatabase acExport, "Microsoft Access", _
  14.             strTargetDB, acQuery, qdf.Name, qdf.Name
  15.     End If
  16. Next
  17.  
  18. For Each tbl In CurrentDb.TableDefs
  19.  If (tbl.Connect) <> "" Then
  20.   If (tbl.Name) <> "MySys*" Then
  21.     DoCmd.TransferDatabase acExport, "Microsoft Access", _
  22.             strTargetDB, acTable, tbl.Name, tbl.Name
  23.     End If
  24.   End If
  25. Next
  26.    On Error GoTo 0
  27.    Exit Sub
  28. End Sub
Apr 6 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.