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

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?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
SteveL13Author Commented:
I do have relationships.  So I think #2 would be the choice.
0
SteveL13Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
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
SteveL13Author Commented:
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
PatHartmanCommented:
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
SteveL13Author Commented:
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
PatHartmanCommented:
I asked three questions.  You didn't answer any of them.  Instead, you responded to my comment.
0
SteveL13Author Commented:
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
PatHartmanCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry I was MIA yesterday; what a day <g>

 Anyway, where are you at with this?

Jim.
0
SteveL13Author Commented:
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
PatHartmanCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
SteveL13Author Commented:
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
SteveL13Author Commented:
And another compile error:  Loop without Do
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
SteveL13Author Commented:
I entered "Next tdf" and not get another compile error...

"Next without For"
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Make sure you have the end if.  If so, paste what you have.

Jim.
0
PatHartmanCommented:
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
SteveL13Author Commented:
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
SteveL13Author Commented:
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
SteveL13Author Commented:
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
PatHartmanCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Hi Steve,

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

Jim.
0
SteveL13Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.