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
> Create Partition/copy Of A Database, Access 2013    
 
   
littlemojopuppy
post Oct 6 2016, 03:13 PM
Post#1



Posts: 31
Joined: 23-May 16



Hi -

I was hoping for a little bit of help with something...I've been trying to figure this out for two days.

My requirement is to populate a "master version" with a complete set of data and then create duplicate copies of the database and its objects, each copy with a subset of the data (based on sales geography). Each of the copies would then be distributed to the customer who would then use the data pertaining to their sales region, area and/or division. There are about 125 possible partitions that could be created so doing this manually isn't an option. My plan for accomplishing this is to create an empty database and then copy in all the objects from the master. Once that's complete, run queries to delete all data from the database copy not relevant for that sales geography. My plan is working great except for one thing: forms that are open while the code to do this is running are not copying.

I came up with a plan for this too: create a duplicate form and then move it from the master db to the copy (and then delete from the master). Then rename the form in the database copy back to the original name. Except for the life of me I can't figure out how to rename a form in another database. Here's some code:

[/font]
Private Sub ExportDatabase(DatabaseFileName As String)
On Error GoTo ErrorHandler

Dim DatabaseCopy As DAO.Database
Dim CurrentTable As DAO.TableDef
Dim CurrentQuery As DAO.QueryDef
Dim CurrentForm As AccessObject
Dim CurrentReport As AccessObject
Dim CurrentModule As AccessObject

' Create New Database
Set DatabaseCopy = DBEngine.CreateDatabase(DatabaseFileName, dbLangGeneral)

' Export Tables
For Each CurrentTable In CurrentDb.TableDefs
If Left(CurrentTable.Name, 4) <> "MSys" Then
DoCmd.CopyObject DatabaseFileName, , acTable, CurrentTable.Name
End If
Next CurrentTable

' Export Queries
For Each CurrentQuery In CurrentDb.QueryDefs
DoCmd.CopyObject DatabaseFileName, , acQuery, CurrentQuery.Name
Next CurrentQuery

' Export Forms
For Each CurrentForm In CurrentProject.AllForms
DoCmd.CopyObject DatabaseFileName, , acForm, CurrentForm.Name
Next CurrentForm

' Export Reports
For Each CurrentReport In CurrentProject.AllReports
DoCmd.CopyObject DatabaseFileName, , acReport, CurrentReport.Name
Next CurrentReport

' Export Modules
For Each CurrentModule In CurrentProject.AllModules
DoCmd.CopyObject DatabaseFileName, , acModule, CurrentModule.Name
Next CurrentModule

' Close & Release Objects
DatabaseCopy.Close

Set DatabaseCopy = Nothing

Exit Sub

ErrorHandler:
Dim ErrorMessage As String
Select Case Err.Number
Case 2007
Dim TargetAccessDB As New Access.Application
Dim TargetForm As Form

' Copy, Export and Delete Copy
DoCmd.CopyObject CurrentDb.Name, "New " & CurrentForm.Name
DoCmd.CopyObject DatabaseFileName, , acForm, "New " & CurrentForm.Name
DoCmd.DeleteObject acForm, "New " & CurrentForm.Name

' Rename Exported Form
TargetAccessDB.OpenCurrentDatabase DatabaseFileName
Set TargetForm = TargetAccessDB.CurrentProject.AllForms("New " & CurrentForm.Name)

With TargetForm
.Name = CurrentForm.Name
End With

TargetAccessDB.Quit

' Close & Release Objects
Set TargetAccessDB = Nothing
Set TargetForm = Nothing

Resume Next

Case 3204
Dim Confirmation As Integer
Confirmation = MsgBox("Database already exists. Delete Existing Database?", vbQuestion + vbYesNo)
If Confirmation = vbYes Then
Kill DatabaseFileName
Resume
Else
MsgBox "Process Terminated!", vbExclamation + vbOKOnly
End If
Case Else
ErrorMessage = Err.Number & ": " & Err.Description
MsgBox ErrorMessage, vbCritical + vbOKOnly
End Select
End Sub
[font="Arial"]


Reasoning is that my looping through AllForms, those that are open will throw error 2007. Which is where I go through the process to create a duplicate form and move that. What I've pasted above doesn't work because in the error handling procedure I defined the form as a Form object and my misnamed form is not found in the collection of open forms in the copy database. I've also tried this defining my misnamed form as an AccessObject, but unfortunately the Name property for an AccessObject is read only. D'oh!

If anyone could provide some ideas on how to rename a form in another database (or alternatively: a simpler way to create a mirror image of a database and all objects in it!) I would certainly appreciate the help! Thank you!
Go to the top of the page
 
littlemojopuppy
post Oct 6 2016, 03:21 PM
Post#2



Posts: 31
Joined: 23-May 16



Alternative and possibly simpler solution: my original thought was to repeatedly save the database in .ACCDE format. But I couldn't figure out how to save the file as ACCDE so the plan outlined above was my backup plan. If anyone could help with this solution, I'd appreciate it!
Go to the top of the page
 
theDBguy
post Oct 6 2016, 03:24 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,222
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Just a quick guess but have you tried maybe using the DoCmd.Rename method. It might work since you're opening the other database as the "current" database.

Just a thought...
Go to the top of the page
 
theDBguy
post Oct 6 2016, 03:26 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,222
Joined: 19-June 07
From: SunnySandyEggo


Hi,

How will saving the file as ACCDE help with the problem? Just curious...
Go to the top of the page
 
littlemojopuppy
post Oct 6 2016, 03:30 PM
Post#5



Posts: 31
Joined: 23-May 16



Actually...I didn't try the DoCmd.Rename because I was interpreting "current database" to mean the one running the code. Think it'll work?

Saving as an ACCDE file will do two things: copy all the database objects and also compile the forms, reports and modules so my users don't mess with them (which they have been known to do).
Go to the top of the page
 
littlemojopuppy
post Oct 6 2016, 03:35 PM
Post#6



Posts: 31
Joined: 23-May 16



I tried the DoCmd.Rename and got run-time error 7874: Microsoft Access can't find the object "New Management" (which is the name of one of the open forms that was renamed and then copied into my target database)
Go to the top of the page
 
theDBguy
post Oct 6 2016, 03:54 PM
Post#7


Access Wiki and Forums Moderator
Posts: 71,222
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I did a quick test using the following code:

CODE
Public Function RenameExternalDbObject() As Byte
'10/06/2016
'http://accessmvp.com/thedbguy

Dim app As Object

Set app = CreateObject("Access.Application")

With app
    .OpenCurrentDatabase CurrentProject.Path & "\db1.accdb", True
    .Application.DoCmd.Rename "NewForm", acForm, "Form1"
End With

Set app = Nothing

End Function

I created a blank db called "db1.accdb" and created a blank form within it called "Form1." I then opened a different database file and created the above function in it. After I executed it in the Immediate Window, I closed the db and when I opened DB1.accdb and checked the form, the name was changed to "NewForm."

Hope it helps...
Go to the top of the page
 
littlemojopuppy
post Oct 6 2016, 04:18 PM
Post#8



Posts: 31
Joined: 23-May 16



Thank you! I'll try this in the morning!!! :-D
Go to the top of the page
 
littlemojopuppy
post Oct 6 2016, 04:28 PM
Post#9



Posts: 31
Joined: 23-May 16



I tried it tonight. Different error this time at the line in red - 3045: Could not use DESTINATION.accdb; file already in use. Means it's trying to, which is better than before, right?

Here's the code it's running:
Case 2007
Dim TargetAccessDB As New Access.Application

' Copy, Export and Delete Copy
DoCmd.CopyObject CurrentDb.Name, "New " & CurrentForm.Name
DoCmd.CopyObject DatabaseFileName, , acForm, "New " & CurrentForm.Name
DoCmd.DeleteObject acForm, "New " & CurrentForm.Name

' Rename Exported Form
TargetAccessDB.OpenCurrentDatabase DatabaseFileName
TargetAccessDB.DoCmd.Rename CurrentForm.Name, acForm, "New " & CurrentForm.Name
TargetAccessDB.CloseCurrentDatabase

' Close & Release Objects
Set TargetAccessDB = Nothing

Resume Next

I'm sure the reason why will hit me on the drive home...
Go to the top of the page
 
theDBguy
post Oct 7 2016, 06:40 PM
Post#10


Access Wiki and Forums Moderator
Posts: 71,222
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Since I don't have a copy of your database for testing, I just want to mention there are other ways to move/migrate objects around. For example, you might try looking into the TransferDatabase method.

Just my 2 cents...
Go to the top of the page
 
gemmathehusky
post Oct 15 2016, 09:39 AM
Post#11


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


actually, you do not necessarily need to delete any data.

just make sure that the forms and reports for each branch only give them access to data for that branch.


the biggest issue comes when you want to merge everything back together to produce an overall company position.
The problem is that you have to take steps to ensure that PK's etc issued at each branch are unique to the whole company, where appropriate - to avoid clashes when you try to recombine the data.

This may or may not be an issue.
Go to the top of the page
 
dmhzx
post Oct 15 2016, 10:04 AM
Post#12



Posts: 7,033
Joined: 22-December 10
From: England


Just a thought:
Would it not be easier, quicker to copy the entire database, and then delete what you don't want from the tables?
You could specify in a table what to keep for each branch, and use that as a driver for the code?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 09:52 AM