Solved

VBA code to create a separate B/E file with deleted records in linked tables

Posted on 2014-09-28
28
223 Views
Last Modified: 2014-10-03
I am using a split database in Access 2010.  I'd like to create a new B/E file, leaving the current B/E alone via VBA code.  But I want the new B/E file to have a new name (user generated), and have all data in the tables in the new B/E file deleted.  And then also have the new B/E file compacted and repaired.

Can this be done via VBA code?
0
Comment
Question by:SteveL13
  • 13
  • 8
  • 7
28 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
It can all be done via code. There are two approaches:

1. Create a new blank DB and export the table structures to it and any other objects you need.

2. Copy the current BE, empty all the tables, then do a compact and repair (what you were thinking).

  Either is totally doable with code.  

For #1, you'd use the CreateDatabase method, followed by using the transferdatabase method on each tabledef in the tabldefs collection to transfer the table structures.

For #2, you'd do a file copy, then for each tabledef in the tabledefs collection, execute a "DELETE * FROM <tableName>" for each table.

 If you have no relationships, I'd probably go with #1.  Let me know which way you'd like to go and I'll give you some examples.

Jim.
0
 

Author Comment

by:SteveL13
Comment Utility
I do have relationships.  So I think #2 would be the choice.
0
 

Author Comment

by:SteveL13
Comment Utility
Jim,

Sorry to be a bother.  I have attempted to figure out how to do the 2nd choice and can't even get close.  Any thoughts?

--Steve
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Is this a one-time exercise or does the user need to do it on demand?  

I have an application with an archive function.  The simplest solution for me was to create a "template" database that contained all the tables without any data plus the forms/reports to run the archive.  Then when a user asked to archive an audit, the app would copy the template database and give it the name the user specified.  It would then link to the new, empty database and run append queries to append the audit.  Once the archive was created, it would delete the audit from the primary database.

You never said why the user was creating a new empty BE so I can't really offer any thing more.  Simply that copying a template takes the least amount of work.  Or, you can simply export structure only which was Jim's first option.  I don't understand why you would want to copy data, then delete it and compact the database if you just wanted to end up with an empty database.  So, perhaps you should tell us more about what you really want to do.
0
 

Author Comment

by:SteveL13
Comment Utility
This will be an on-demand situation.  And here is a step-by-step listing of how I'd like it to work.

1) User opens a form which has an unbound text box.
2) User enters a file name in the text box.  This file name will be the name of the copied back-end file.
3) User clicks a command button on a form.
4) The command button executes a function that will copy the current back-end file to the user's C drive.  The copied file will be named whatever the user named it in step #2.
5) This copied back-end file will contain all of the tables in the current back-end file but the tables will have no data.
6) This copied backend file will be compacted and repaired so any auto-number fields start with "1".

Make sense?

--Steve
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Is there a reason you can't use the template method I suggested?
Is there a reason you can't simply create an empty database and copy the table structure as Jim's option 1?
Are you aware that the autonumber is meaningless (or should be) and is only used to provide a unique identifier for a particular record?

Access is a database application, NOT a spreadsheet.  It is extremely unusual to require the creating of a completely empty BE.  If you are doing this because you want a different BE for each year, client, whatever, that is a misuse of a relational database.  More than likely, it will be better in the long run for all the data to be in the same BE and separated as necessary with queries.
0
 

Author Comment

by:SteveL13
Comment Utility
The empty B/E database file will be used by several people other than the user that will be creating the file. They are all off site and need a B/E file of their own.  I just want to provide an easy way for him to create the database with the tables and no data and give it a specific name while he's doing it.

--Steve
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I asked three questions.  You didn't answer any of them.  Instead, you responded to my comment.
0
 

Author Comment

by:SteveL13
Comment Utility
Sorry... Answers:

Is there a reason you can't use the template method I suggested?  ==  No.  But I'm trying to make it easy for the user to name the new file.

Is there a reason you can't simply create an empty database and copy the table structure as Jim's option 1?  ==  Probably not but again, I'm trying to make it easy for the user to name the new file.

Are you aware that the autonumber is meaningless (or should be) and is only used to provide a unique identifier for a particular record?  ==  Yes.

--Steve
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Why do you think the first two choices make it more difficult for the user to name the file?  Your form should have a box to select a folder.  It can have another text box to type in a name.  Or, if the app can derive a name, the app can offer a suggested name and the user can override it.  Once the name is chosen, you do either 1 or 2.

From your list above steps 1-3 don't change.  The difference is that 4-6 are replaced with either 1 or 2 of my list above.  Option 1 (the template) is pretty much 1 line of code.  It doesn't get easier than that.  The only reason to not use option 1 is because the table list isn't static or changes too frequently for you to want to bother with a template.  Option 2 isn't much more difficult but it does involve a code loop.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Sorry I was MIA yesterday; what a day <g>

 Anyway, where are you at with this?

Jim.
0
 

Author Comment

by:SteveL13
Comment Utility
Am getting nowhere because I don't know how to write the code to do the following:  (thanks to both of you for offering advise)

This will be an on-demand situation.  And here is a step-by-step listing of how I'd like it to work.

 1) User opens a form which has an unbound text box.
 2) User enters a file name in the text box.  This file name will be the name of the copied back-end file.
 3) User clicks a command button on a form.
 4) The command button executes a function that will copy the current back-end file to the user's C drive.  The copied file will be named whatever the user named it in step #2.
 5) This copied back-end file will contain all of the tables in the current back-end file but the tables will have no data.
 6) This copied backend file will be compacted and repaired so any auto-number fields start with "1".
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Here's code that will create the empty database and copy in only the table structure.  It ignores the MSys tables and any deleted tables.

You can call it from your form and pass in the name of the database including the full path so the value of dbname should be something like "C:\Pat\test.accdb"   And the call in the click event of a button will look like

Call fCreateNewDB(Me.txtPath)


Function fCreateNewDB(dbName As String) As Boolean
    Dim db As DAO.Database
    Dim NewDB As DAO.Database
    Dim ws As Workspace
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb()
    
    'Get default Workspace
    Set ws = DBEngine.Workspaces(0)
    
    'Make sure there isn't already a file with the name of the new database
    If Dir(dbName) <> "" Then Kill dbName
   
    'Create a new mdb file
    Set NewDB = ws.CreateDatabase(dbName, dbLangGeneral)

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) = "MSys" Or Left(tdf.Name, 1) = "~" Then
        Else
            DoCmd.TransferDatabase acExport, "Microsoft Access", dbName, acTable, tdf.Name, tdf.Name, True
        End If
    Next tdf
    Set db = Nothing
    Set NewDB = Nothing
    fCreateNewDB = True
End Function

Open in new window

0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
Steve,

  OK, did a little bit and attached is a sample DB that will get us started.   This has a simple form that calls the Windows API open dialog and gets the file name from the user.

 So take this, add a button to create the DB copy and do that by:

1. Use the CreateDatabase() method:

        Dim dbsNew as DAO.Database
       Set dbsNew = DBEngine.Workspaces(0).CreateDatabase(Me.txtPathAndFileName, dbLangGeneral)

2. Construct a loop for the tabledefs collection:

  Dim db as DAO.Database
  Dim tdf as DAO.TableDef

  Set db = CurrentDB()
  For each tdf in  db.Tabledefs()
        ' Is this a table we want?
        If tdf.Name = <condition> then
             
          ' Create table in new DB
          DoCmd.TransferDatabase acExport, "Microsoft Access", Me.txtPathAndFileName, acTable, tdf.Name, tdf.Name, True

       End If
  Loop

  That will get you all the tables blank.  Work with that for now and I'll get back to you a little later with getting the relationships duplicated.

Jim.
DialogClass.mdb
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:SteveL13
Comment Utility
Jim,

Ok.  I took your file and changed one thing...

I changed...

clsDialog.Filter = "MDB (*.MDB)" & Chr$(0) & "*.MDB" & Chr$(0)

to

clsDialog.Filter = "ACCDB (*.ACCDB)" & Chr$(0) & "*.ACCDB" & Chr$(0)

Then I added a command button with the following code in the onclick event:

Private Sub Command3_Click()

Dim dbsNew As DAO.Database
        Set dbsNew = DBEngine.Workspaces(0).CreateDatabase(Me.txtFilePathAndName, dbLangGeneral)
        
        
Dim db As DAO.Database
   Dim tdf As DAO.TableDef

   Set db = CurrentDb()
   For Each tdf In db.TableDefs()
         ' Is this a table we want?
         If tdf.Name = <condition> then
              
           ' Create table in new DB
           DoCmd.TransferDatabase acExport, "Microsoft Access", Me.txtPathAndFileName, acTable, tdf.Name, tdf.Name, True

        End If
   Loop
        

End Sub

Open in new window


But two things...

1) The line that reads  "If tdf.Name = <condition> then" turns red.  Am I supposed to change <condition> to True
?

2) When I execute the code via the command button I get a compile error -  "Method or data member not found" and the code has txtPathAndFileName highlighted in the line...

DoCmd.TransferDatabase acExport, "Microsoft Access", Me.txtPathAndFileName, acTable, tdf.Name, tdf.Name, True
0
 

Author Comment

by:SteveL13
Comment Utility
And another compile error:  Loop without Do
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
<<1) The line that reads  "If tdf.Name = <condition> then" turns red.  Am I supposed to change <condition> to True
?>>

 Your supposed to change it to whatever you want.   Anytime you see some in < > in documentation, it denotes a value that you need to replace it with.

 At the very least, you'll want to exclude all the MSYS* tables, which are Accesses own internal tables for keeping track of everything.  

so:

  ' Skip only the system and temp tables and do everything else.
  If Left(tdf.name,4) <> "msys" and Left(tdf.name,1)<>"~" then

 Which if you look what Pat had in her procedure.   Also note she had a Kill of the DB if it already existed, which you might want.

<<
2) When I execute the code via the command button I get a compile error -  "Method or data member not found" and the code has txtPathAndFileName highlighted in the line..>>

 Check the name of the control on the form; I pounded all that out rather quickly (another fun day here) and I may have it wrong.

<<And another compile error:  Loop without Do >>

 Should be "next tdf", not loop.

Jim.
0
 

Author Comment

by:SteveL13
Comment Utility
I entered "Next tdf" and not get another compile error...

"Next without For"
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Make sure you have the end if.  If so, paste what you have.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Why don't you start in your own database using the procedure I posted.  It doesn't require modification.  You can copy and paste it into the class module of a form and call it from a button.  You need to pass in the full path name of the database you want to create.
0
 

Author Comment

by:SteveL13
Comment Utility
Here's what I have now:  (getting close)

But now I get a message that says the database already exists.

Private Sub Command3_Click()

Dim dbsNew As DAO.Database
    Set dbsNew = DBEngine.Workspaces(0).CreateDatabase(Me.txtFilePathAndName, dbLangGeneral)
        
Dim db As DAO.Database
    Dim tdf As DAO.TableDef

   Set db = CurrentDb()
   For Each tdf In db.TableDefs()
         ' Is this a table we want?

          If Left(tdf.Name, 4) <> "msys" And Left(tdf.Name, 1) <> "~" Then

           ' Create table in new DB
           'DoCmd.TransferDatabase acExport, "Microsoft Access", Me.txtFileName, acTable, tdf.Name, tdf.Name, True
           DoCmd.TransferDatabase acExport, "Microsoft Access", "NEW BackEndFile_be", acTable, tdf.Name, tdf.Name, True
        End If
   Next tdf

End Sub

Open in new window

0
 

Author Comment

by:SteveL13
Comment Utility
Pat.. I just copy/pasted your code into the form VBA.  And I am trying to call it with "Call fCreateNewDB" in the onclick event of the command button but it fails on Call fCreateNewDB...

Compile Error: Argument not optional
0
 

Author Comment

by:SteveL13
Comment Utility
And I don't understand " You need to pass in the full path name of the database you want to create. " How do I do that?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
Once you start working with VBA it is important that you understand the code.  If you go back and look at the procedure  I posted, it has the answer.  Also, Jim even mentioned the potential for this error in his last post.

I included a working sample.
Sample-CreateTemplateDB-PatHartman.zip
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
Steve,

<<And I don't understand " You need to pass in the full path name of the database you want to create. " How do I do that? >>

  You could marry up what I gave you in terms of the from and browse button with the procedure that Pat gave, which is a little more complete then what I pounded out.  You would take that procedure code, place it in the OnClick event of a new button (i.e. "Start").  You could also place it in a standard module on its own, then call it from the OnClick event of the button:


  If fCreateNewDB(Me.txtPathAndFileName)  = True then
     Msgbox "DB created"
 Else
     Msgbox "Failed to create DB"
 End If

  This would be taking the value of the control "txtPathAndFileName" and passing it to the procedure.  Since this procedure is a function, that also means it passes a value back.    That's why you can use the if check.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
<<Pat.. I just copy/pasted your code into the form VBA.  And I am trying to call it with "Call fCreateNewDB" in the onclick event of the command button but it fails on Call fCreateNewDB...

Compile Error: Argument not optional >>

 Just to add a bit; there are two basic types of procedures:

1. Subs, which you "Call".  They no not return a value.

2. Functions which you use and they do return a value.

 You tell which is which by the first line of the procedure, which declares it:

Function fCreateNewDB(dbName As String) As Boolean

 So this is a function and will return a Boolean (True/False) value.

 What appears in the ( ) of that statement are the arguments the procedure expects to receive.  There may be none, one, more than one, and if there, might be marked optional.

 You really would help yourself out if you looked at some of Allison Balter's (TechMommy) articles and video's in the library:

http://www.experts-exchange.com/Database/MS_Access/A_9490-Introduction-to-VBA-Part-1.html

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Hi Steve,

 Made any progress with this?  Do you need anything else or have more questions?

Jim.
0
 

Author Comment

by:SteveL13
Comment Utility
I'm going to award this to all who helped even though I've decided to go a different direction.  All of the feedback was extremely helpful.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now