Solved

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

Posted on 2014-09-28
28
230 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
[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
  • 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
ID: 40348597
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
ID: 40348599
I do have relationships.  So I think #2 would be the choice.
0
 

Author Comment

by:SteveL13
ID: 40348901
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40350466
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
ID: 40350558
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 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40350586
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
ID: 40350785
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 37

Expert Comment

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

Author Comment

by:SteveL13
ID: 40350990
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 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40351081
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
ID: 40351910
Sorry I was MIA yesterday; what a day <g>

 Anyway, where are you at with this?

Jim.
0
 

Author Comment

by:SteveL13
ID: 40352013
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 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40352174
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
ID: 40352195
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
 

Author Comment

by:SteveL13
ID: 40352280
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
ID: 40352294
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
ID: 40352458
<<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
ID: 40352496
I entered "Next tdf" and not get another compile error...

"Next without For"
0
 
LVL 57
ID: 40352507
Make sure you have the end if.  If so, paste what you have.

Jim.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40352511
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
ID: 40352521
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
ID: 40352533
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
ID: 40352537
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 37

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40352541
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
ID: 40352572
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
ID: 40352590
<<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
ID: 40356716
Hi Steve,

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

Jim.
0
 

Author Comment

by:SteveL13
ID: 40360172
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

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!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 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