Copy DB / Close Form

[colette]

Hi,
I have written ADO VBA code that cycles through an open database and copies all the objects to a new database. Even though, I do a docmd.close on the form that launched the code, it still hiccups like it is open. Any thoughts on how to remedy this so that object gets copied as well. Also, any thoughts on a simple way to copy a database while inside it such that the copy has imported versions of the tables and not linked ones (original db has linked oracle tables).

my loop for my forms is like this...
For Each obj In dbs.AllForms
DoCmd.CopyObject sName, obj.Name, acForm, obj.Name
Next obj



Thank you,
Colette

 

[ghudson]

Searching the forum is a great way to discover and learn the answers to your Access programming questions. Back up On exit

 

[colette]

Partial Solution

Hi,
Thank you for leading me to this partial solution to my issue, but next time don't be so quick (and rude) about assuming people haven't spent time searching the web. I spent over an hour and was seeking two specific things. Backup on Exit implied "Exit" and I didn't want that. I wanted the code to run from a button while the system stayed open (upon completing a process). Secondly, I don't want linked tables to remain linked tables, I want the copy to have imported tables.

I do like your code, especially since it is nice and condensed. It did successfully run even when forms were open where copyobject complained.

I will now write code that will copy the tables rather than link them.

I am experienced database developer/programmer with 10 years using MS Access. I am astonished how rude people can be on this site. The idea is to help one another when what we have tried has failed and we are searching to see if others have found a solution. The idea is to share, each time I visit the site for a solution, I also help others.

Thank you,
Colette

 

[ghudson]

You get what you pay for when you visit this site. Sharing information is what makes this a great site. I enjoy helping others which is why I gave you a link to a solution for your problem.

Your question has been asked and answered [in different variations] in numerous threads within this site. Maybe you need to learn "how" to use the search function? Searching for specific key words instead of asking a question will help you return better results.

Maybe this will help you in the future... How to Ask Questions the Smart Way

Merry Christmas!

 

[colette]

Copy Database / Rename Tables / Alter Table

Does anyone have a suggestion on how I could further improve the code below? The function is behind a button on the form. It makes a copy of a database and then in an awkward way attempts to copy the linked tables making them local tables and dropping linked tables.

The code below works. Some of the tables are large so it takes about 5 minutes to run. I tried to use .Execute "ALTER TABLE..." to basically do a rename, but couldn't get it to work. Because I am trying to manipulate the tables of one database while inside the other, this has proved to be challenging. Any thoughts on how I could speed it up? My ultimate goal is simply to make a copy of a database that has linked tables and have the resulting copy using local tables instead of linked tables.

Public Function BackupDB()
On Error GoTo Err_BackupDB

Dim obj As AccessObject
Dim curTable As Object
Dim sName, sDir, sDate As String
Dim sBatchType, sBatchName As Variant

sDate = month(Now) & "_" & Day(Now) & "_" & Year(Now)
sDir = DLookup("DirForBackups", "DBNames")
sName = sDir & "SFDCEIM_BKP_" & gblBatch_Type & "_" & gblBatch_Name & "_" & sDate & ".mdb"

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile Application.CurrentDb.Name, sName, True
Set fso = Nothing


Dim ConnCopy As ADODB.Connection
Set ConnCopy = New ADODB.Connection
ConnCopy.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sName

objName = "table"
For Each curTable In CurrentData.AllTables
If Not Left(curTable.Name, 4) = "MSys" Then
CurrentProject.Connection.Execute "SELECT * INTO " & curTable.Name & "_Copy" & " IN
ConnCopy.Execute "DROP TABLE " & curTable.Name & ";"
ConnCopy.Execute "SELECT * INTO " & curTable.Name & " IN '" & sName & "' FROM " & curTable.Name & "_Copy" & " IN '" & sName & "'"
ConnCopy.Execute "DROP TABLE " & curTable.Name & "_Copy" & ";"

End If
Next

Set ConnCopy = Nothing
MsgBox "Backup was successful and saved. " & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sName, vbInformation, "Backup Completed"

ODBC_OpenConnection
DoCmd.OpenForm "frmmainmenu", acNormal
SetAttr sName, vbReadOnly

Exit Function

Err_BackupDB:
MsgBox Error
Exit Function

End Function

 

[ghudson]

Why not use a make table query [SQL] and set it to output to your new db?

DoCmd.RunSQL ("SELECT YourTable.* INTO YourNewTable IN '\\Server\Partition\Directory\YourNewDB.mdb' FROM YourTable;")

This is how I do it to create archive tables with the current date added to the table name...

DoCmd.RunSQL ("SELECT YourTable.* INTO [YourNewTable" & "_" & Format(Date, "yyyy-mm-dd") & "]IN '\\Server\Partition\Directoryt\YourNewDB.mdb' FROM YourTable;")

That has to be a lot easier to use than what you are attempting.

 

[colette]

I was doing that originally. I also use it to copy my linked tables within the external db to local tables. When I sent each type of object over one at a time, the system hiccuped on the open forms. Then, an individual from this site suggested a way to copy the whole db much faster, only copied the links. I have yet to really find a way to do this easily.