Copy, Close and Open New Database

[laman]

Seems like it should be easy but I can't seem to figure it out. I have a database I would like to use as a master to create new databases with new names. With the master open I have the code to make a copy of the master with the user entering the new name. What I would then like to do is close the master and open the new named database. I don't think I want to use replication since these databases are stand alone with no further relationship to the master.

Any Thoughts,
Thanks for Your Time!

 

[KyleS]

You could do something like this:

Dim dblNew As Double, strMacro as String, strAccDB as string
Dim strAccLoc as String

strMacro = "/x MACRONAME"
strAccDB = """" & "YOURDATABASEPATH" & """"
strAccLoc = SysCmd(acSysCmdAccessDir) & "MSACCESS.exe"
dblNew = Shell(strAccLoc & " " & strAccDB & " " & strMacro, vbNormalFocus)


This will open up your new database and run a macro that right now is called "MACRONAME" (replace this with your own name)

Set this macro up in your master DB (since we need it in your new DB) and have it "RunCode". The code it runs will simply be this: (Paste this into a new function)

Function CloseMaster()
Dim obj as Access.Application
Set obj = GetObject("MASTERDBPATH&quot
obj.Close
Setobj = Nothing
End Function

Hope this helps...
Kyle :

 

[laman]

Thank you for your response. I think we are close. It opens db2 but then gets hung up in the macro with an error,method or data member not found. Highlights close in obj.close. Here is what I have. db1 is master, db2 is new database.

Function CloseMaster()
Dim obj As Access.Application
Set obj = GetObject("c:\my documents\db1.mdb&quot
obj.Close
Setobj = Nothing
End Function

Dim dblNew As Double, strMacro As String, strAccDB As String
Dim strAccLoc As String

strMacro = "/x masterclose"
strAccDB = """" & "c:\my documents\db2.mdb" & """"
strAccLoc = SysCmd(acSysCmdAccessDir) & "MSACCESS.exe"
dblNew = Shell(strAccLoc & " " & strAccDB & " " & strMacro, vbNormalFocus)

Thanks

 

[KyleS]

Hi,
OK, I'm an idiot, you don't need the macro and second set of code (I took that from a function I have here which performs some other updates and what not, so it's performing functions you don't need)

All you need for what you're looking for is this (Add the RED text to your original function):

Dim dblNew As Double, strMacro As String, strAccDB As String
Dim strAccLoc As String

strMacro = "/x masterclose"
strAccDB = """" & "c:\my documents\db2.mdb" & """"
strAccLoc = SysCmd(acSysCmdAccessDir) & "MSACCESS.exe"
dblNew = Shell(strAccLoc & " " & strAccDB & " " & strMacro, vbNormalFocus)
application.Quit

This will open the second DB and then close itself (Master)

Sorry for the confusion,
Kyle

 

[laman]

One more question. What you gave me worked fine but when I added it to the code I already had to copy the master an error message comes up "can not find file". When look in explorer the file is there. I'm new at VB.

Dim oFiles As FileSystemObject
Dim strSource As String, STRDEST As String
Dim dblNew As Double, strAccDB As String
Dim strAccLoc As String

strSource = "C:\MY DOCUMENTS\DB1.MDB"
STRDEST = [Forms]![COPY]![Text2] & "\" & [Forms]![COPY]![PATH] & ".MDB"
Set oFiles = New FileSystemObject
oFiles.CopyFile strSource, STRDEST
Set oFiles = Nothing

strAccDB = """" & "STRDEST" & """"
strAccLoc = SysCmd(acSysCmdAccessDir) & "MSACCESS.exe"
dblNew = Shell(strAccLoc & " " & strAccDB, vbNormalFocus)
Application.Quit

Thanks Again

 

[KyleS]

Hi,
Your problem lies in the code in RED (I took the Quotes off your STRDEST variable, that was the problem):

Dim oFiles As FileSystemObject
Dim strSource As String, STRDEST As String
Dim dblNew As Double, strAccDB As String
Dim strAccLoc As String

strSource = "C:\MY DOCUMENTS\DB1.MDB"
STRDEST = [Forms]![COPY]![Text2] & "\" & [Forms]![COPY]![PATH] & ".MDB"
Set oFiles = New FileSystemObject
oFiles.CopyFile strSource, STRDEST
Set oFiles = Nothing

strAccDB = """" & STRDEST & """"
strAccLoc = SysCmd(acSysCmdAccessDir) & "MSACCESS.exe"
dblNew = Shell(strAccLoc & " " & strAccDB, vbNormalFocus)
Application.Quit


Although I would recomend another change. You don't need to create a FileSystemObject in order to do the copy.

Try this code:

Dim strSource As String, STRDEST As String
Dim dblNew As Double, strAccDB As String
Dim strAccLoc As String

strSource = "C:\MY DOCUMENTS\DB1.MDB"
STRDEST = [Forms]![COPY]![Text2] & "\" & [Forms]![COPY]![PATH] & ".MDB"

FileCopy "strSource", "strDest"

strAccDB = """" & STRDEST & """"
strAccLoc = SysCmd(acSysCmdAccessDir) & "MSACCESS.exe"
dblNew = Shell(strAccLoc & " " & strAccDB, vbNormalFocus)
Application.Quit

This will do the same thing but save your DB some overhead as it doesn't have to create the variable...

Let me know if I can be of further assistance...
Kyle :