Change name to current project and export all objects

[diegomarino]

Hi, i have my database called "manage".



I'd like - using vba - to

1) change the name of current project to "manage-(max number in a folder in which i have many file called "manage-1", "manage-2" etc etc)
2) create another database called "manage" (now the old "manage" is "manage-120" for example
3) export every object to the new database, even ddl and settings (like floating form and compact the database on exit)

is it possibile to do that? i'm able just to get the name of the current project

thanks

[CJ_London]

from the name of the current project, to get the next number

newname=left(currentdb.name, instr(currentdb.name,"-")) & val(mid(currentdb.name, instr(currentdb.name,"-")+1))+1 & ".accdb"

but rather than copying tables etc, just copy the db to the new name, then compact repair

filecopy currentdb.name, newname

[diegomarino]

thanks, actually the name of the current project is always "manage", in the same folder of my current project i have many "manage" with "-" and a progressive number, so to get the highest number i should search in the folder every databases for the correct number

i'd like to change the name of the current project form "manage" to "manage"& the highest number found in the name of the dbs in that folder, then make a new db called "manage" (i have some linked excel and i want to keep the name "manage" for the project i use)

ps, copy and then compact and repair is the same that exporting? i thought exporting has some "healing" feature

[CJ_London]

i thought exporting has some "healing" feature

not so far as I am aware.

decompiling before compacting then recompiling has benefits and is worth doing on a regular basis during development and before final release to production

to find the highest number perhaps some code along these lines

dim f as string
dim m as integer

f=dir(replace(currentdb.name,".accdb","*")
while f<>""
  if m<val(mid(currentdb.name, instr(currentdb.name,"-")+1)) then m=val(mid(currentdb.name, instr(currentdb.name,"-")+1))
  f=dir()
wend
msgbox "maxnum= " & m

[moke123]

What is your ultimate goal for this exercise?

Is your database split, as it should be?

Are you getting errors or some type of corruption?

[diegomarino]

once i had a corruption problem and now i'm really scared; then, since i make almost every day a lot of programming and attempts, i like to have a backup every pair of weeks, more or less.
This saved my butt sometimes, expecially cause i can see what i did before if something goes wrong

edit: "a lot of programming" without having the necessary skills to do that. mandatory clarification

[moke123]

I thought there was more to the story here.

I use the following code to do back ups of my BACK END.
When I open my database I use fso to find the last created copy and if it is more than a week old make a new copy with the below code.

For the FRONT END I just periodically copy and paste a copy to a subfolder.

Public Sub BackUpAndCompactBE()
'Courtesy of Brent Spaulding (datAdrenaline), MVP
'Modified by theDBguy on 5/27/2019
'Source: http://www.accessmvp.com/thedbguy


    On Error GoTo errHandler


    Dim strDestination As String
    Dim strSource As String
    Dim strTableName As String
    Dim strFileName As String
    Dim BUFolder As String
 
    'Create a file scripting object that will backup the db
    Dim oFSO As New FileSystemObject


    strTableName = "tblCases"    'name of your linked table


    'Get the source of your back end
    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _
                            "Database=")(1), ";")(0)


    ' strFileName = Format(Now, "mmddyyyy") & oFSO.GetFileName(strSource)  'append to beginning of file name
    strFileName = oFSO.GetBaseName(strSource) & Format(Now, "mmddyyyy") & "." & oFSO.GetExtensionName(strSource)  'append to end of file name


    BUFolder = CurrentProject.Path & "\DataBackUps"


    'Determine your destination
    strDestination = oFSO.BuildPath(BUFolder, strFileName)


    'Flush the cache of the current database
    DBEngine.Idle


    'execute the backup
    oFSO.CopyFile strSource, strDestination
    Set oFSO = Nothing


    'Compact the new file, ...
    Name strDestination As strDestination & ".cpk"
    DBEngine.CompactDatabase strDestination & ".cpk", strDestination
    'Uncomment the following line and comment the previous line
    'if your backend file is password protected or if you want the backup to have a password
    'DBEngine.CompactDatabase strDestination & ".cpk", strDestination, , , ";pwd=YourPassword"
    Kill strDestination & ".cpk"


    'Notify users
    '    MsgBox "Backup file '" & strDestination & "' has been created.", _
         '           vbInformation, "Backup Completed!"


errExit:
    Exit Sub


errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit


End Sub

[moke123]

If you want to set a reminder for yourself to make a backup you could do something like this.

Add a folder "BackUps" in your application folder to copy and paste your backups into.
When you start your database run this code.

Sub FindlastCreated()


    Dim fso As New FileSystemObject


    Dim fil As File
    Dim fol As Folder
    Dim LastDte As Date


    LastDte = #1/1/2000#


    Set fol = fso.GetFolder(CurrentProject.Path & "\BackUps")


    For Each fil In fol.Files


        If fil.DateCreated > LastDte Then
            LastDte = fil.DateCreated
        End If
        
    Next


    If DateDiff("d", LastDte, Date) > 2 Then  'set number of days
        MsgBox "You last Backup was " & LastDte & "." & vbNewLine &  & vbNewLine & "Please back up before proceeding"
    End If


End Sub

This will find the last back up and if it is older than 2 days old, a messagebox will remind you to do a back up.

[diegomarino]

this is really interesting, i didn't dig in yet but i should have no problem.
Something went in my mind, since i use two different pc with very different resolution, i looked for a code to adapt the database to the resolution of the machine i use. I found some stuff that is not so new and is a bit complicated, is there an easy way to achieve that?

[CJ_London]

plenty of examples on this and other access forums.

Here's one https://www.accessforums.net/showthr...een+resolution

[Minty]

I have a database of database projects. I use that to open all my databases.
It automatically creates a time stamped back up before opening the database I want to work on.

I also use the excellent Mz-Tools to auto save the code while I'm working as my Ctrl-S reflex isn't what it used to be.

[diegomarino]

thanks very much

[diegomarino]

hi, sorry for necroposting but at this line

    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _                            "Database=")(1), ";")(0)

i got 3265 runtime error, item not found in the collection

[Welshgasman]

hi, sorry for necroposting but at this line

    strSource = Split(Split(CurrentDb.TableDefs(strTableName).Connect, _                            "Database=")(1), ";")(0)

i got 3265 runtime error, item not found in the collection

You have changed strTableName to a table you have in your BE I hope?

As per 'name of your linked table'

[diegomarino]

you're right, i had a corruption problem and i freaked out....