UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Clone Database With Blank Tables, Access 2016    
 
   
pduffer
post Feb 20 2018, 01:38 PM
Post#1



Posts: 425
Joined: 15-April 03
From: Kansas


I've tried to search for this, but not even sure how to search in a way that returns results.

I have annual databases with tables that are populated with data for reporting. This ensures data doesn't change and reports can always be replicated. There are 79 separate tables for each year. I'm thinking there surely is a way to automate creating a new year with blank copies of all tables, but my mind (and Google) can't seem to function together well enough today to find the solution. If someone could point me in the right direction I would greatly appreciate it. Thanks!
Go to the top of the page
 
theDBguy
post Feb 20 2018, 01:41 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If each year's database has exactly the same table structure as the years previous, then you should be able to simply set up a master database with empty table and simply make a copy of it every year. Of course, you'll have to start with something first to create the master copy. So, you may have to do a one-time clean up of your existing database first. But then every year after that, you won't need to worry about copying the current database and deleting its data for the following year. In other words, there's really nothing to automate.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pduffer
post Feb 20 2018, 01:53 PM
Post#3



Posts: 425
Joined: 15-April 03
From: Kansas


Thanks. That's what I've been doing but if any modifications are made to tables, such as additional fields in newer years, I have to remember to update the one containing the blank tables. I was hoping there was a way that I could add a function to my front end database so that while linked to the latest back end database I could specify the name for the new database year and export the structure only of each linked table. Since a person can export a table to a different database and is prompted to indicate if they want to export definition and data or definition only, I'm hoping there's a way that I could use VBA to do that and simply loop through a list of tables.
Go to the top of the page
 
theDBguy
post Feb 20 2018, 01:58 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Yes, you should be able to use the TransferDatabase method to copy just the table structures.

However, when you "copy" a linked table, then I think you end up with a linked table as well, which means, you'll probably get the data too.

Instead, you would probably put the TransferDatabase code in the backend to copy the local tables.

Another approach is to put code in the frontend to "make tables" into an empty database file based on the linked tables.

Just thinking out loud...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pduffer
post Feb 20 2018, 02:00 PM
Post#5



Posts: 425
Joined: 15-April 03
From: Kansas


"Another approach is to put code in the frontend to "make tables" into an empty database file based on the linked tables."

That is a much clearer way of describing exactly what I'm wanting to do.
Go to the top of the page
 
theDBguy
post Feb 20 2018, 02:07 PM
Post#6


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


You should be able to use a Make-Table query for that. Give it a shot and let us know if you get stuck.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pduffer
post Feb 20 2018, 03:36 PM
Post#7



Posts: 425
Joined: 15-April 03
From: Kansas


I found an example for earlier mdb databases online and used it as an example for populating a new database with blank tables. Instead, it simply builds a new database with the existing data in the tables. Can you see anything that keeps the True statement in the "structure only" portion of the command from working?

CODE
Private Sub cmdCreateNewYear_Click()
    If IsNull(Me.txtNewAYFile) Then
        MsgBox "You must specify the name for the new academic year file to create."
        Me.txtNewAYFile.SetFocus
        Exit Sub
    End If
    
    Dim ws As Workspace
    Dim db As Database
    Dim strNewFile As String
    Dim dbCurrent As Object
    Dim rs As Recordset
    Dim strSQL As String
    
    Set ws = DBEngine.Workspaces(0)
    strNewFile = Me.DataTablesFolder & Me.txtNewAYFile
    
    If Dir(strNewFile) <> "" Then
        MsgBox "File already exists."
        Exit Sub
    End If
    
    Set db = ws.CreateDatabase(strNewFile, dbLangGeneral)
    
    Set dbCurrent = CurrentDb()
    strSQL = "Select TableName from DataSyslinks "
    
    Set rs = CurrentDb.OpenRecordset(strSQL)

    With rs
        .MoveFirst
        While Not .EOF
            DoCmd.TransferDatabase acExport, "Microsoft Access", strNewFile, acTable, rs!tablename, rs!tablename, True
            .MoveNext
        Wend
    End With 'rs
    
    db.Close
    Set db = Nothing
    
    MsgBox "New database created."

End Sub
Go to the top of the page
 
theDBguy
post Feb 20 2018, 03:41 PM
Post#8


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Did you try using this code on "linked" tables? If so, try using it on local tables first to see if you get a different result.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pduffer
post Feb 20 2018, 03:44 PM
Post#9



Posts: 425
Joined: 15-April 03
From: Kansas


Yes, I was using on linked tables. Will try on local tables and see if that is the answer.

Yes, that was the problem. It was simply putting tables still linked to the back end database. Works as desired with local tables.

Thanks for your help!
This post has been edited by pduffer: Feb 20 2018, 03:52 PM
Go to the top of the page
 
theDBguy
post Feb 20 2018, 03:56 PM
Post#10


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


There you go. That's what I was saying earlier about exporting/copying linked tables - you also get a linked table as a result.

Glad to hear you got it sorted out. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 12:30 AM