How rename database file after data has been entered in a form field

I have a database named "Database.accdb".  In that database I have a form with a field named "Client".  When that field has had data entered into it, for example, "Joe Smith", I would like the file named "Database.accdb" to be renamed "Joe Smith.accdb" when the database is closed.  Is this even possible?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EirmanChief Operations ManagerCommented:
This would only make sense if you are starting with an empty Database.accdb

Save your new Joe Smith record and Choose .....  File > Save As
and call it Joe Smith.accdb

You will probably have to close forms etc before saving as.
SteveL13Author Commented:
I don't want to have to do this "manually" as you suggest.  Would rather do it via VBA code from a command button.
EirmanChief Operations ManagerCommented:
Would rather do it via VBA code
Unfortunately, I can't help you this.
If you don't get a satisfactory reply by tomorrow, I suggest that you "Request Attention" above.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Sounds like you are using Access as if it were a spreadsheet.  In a relational database, we don't create separate database files for each Client.  Think of the nightmare you are creating for yourself.  Every time you have to make a change or an enhancement, you would have to make it for every client.  You are better off using Excel.

In a relational database, all the data for all clients coexists in the same tables.  We use the same forms/reports/code, etc.  If we want to work with only a specific client's data, we use queries with parameters.  So for example, you could have a start up form that had a combo box where you could choose a specific client.  Then all your queries could reference that form (you would need to leave it open for the duration).

Select ...
From ...
Where ClientID = Forms!frmClientSelect!cboClientID;
Gustav BrockCIOCommented:
That is not possible as the file has to be closed before it can be renamed.

You can have another app that opens the file, checks if a name exists and records this, closes the file, and renames it if a name was found.

Jeffrey CoachmanMIS LiasonCommented:
Also this request is a bit odd...
As always, ...can you explain the need for this?
(so every time the field changes, the name of the entire database changes...?
SteveL13Author Commented:
We want to have a separate database for each client's records.  I realize this is out of the ordinary but needs to be this way.  The reason is that there could be hundreds of picture files and we don't want the database to grow too large which I'm sure it will in time.

So now I'm trying to think of a way I can have a separate database file just for the purpose of locating the database file we want to copy via an Explorer window.  Have a command button on the only form this special database will have.  When the user clicks the command button, Explorer opens so the use can navigate to the file to be copied.  Also then on the same form have a text box where the user can enter the name of the new file minus the .accdb.

Then finally a command button to [Proceed].  The file they navigated to gets copied with the new file name.

I don't know where to begin.
Jeffrey CoachmanMIS LiasonCommented:
You listed a lot above,
I don't know where to begin.
...on what aspect, specifically?

Opening dialog boxes has been answered here literally thousands of times...:

And this is currently the most popular link on File commands in VBA.
The info in both links is fairly straightforward, there is not much I can add...

Based on our question history here, I am sure you can get started with the info in these links, ...then post back here with what you came up with.

Jeffrey CoachmanMIS LiasonCommented:
File commands in VBA:
(scroll down to the "File-Manipulation Functions" section.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
This helped me get to a solution.  Thanks.  Here is my final code for what its worth.

Private Sub cmdFileDialog_Click()

' This requires a reference to the Microsoft Office 11.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim varfile As Variant

   ' Clear the list box contents.
   Me.FileList.RowSource = ""

   ' Set up the File dialog box.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' Allow the user to make multiple selections in the dialog box.
      .AllowMultiSelect = False

      ' Set the title of the dialog box.
      .Title = "Select One or More Files"

      ' Clear out the current filters, and then add your own.
      .Filters.Add "Access Databases", "*.accdb"

      If .Show = True Then
         ' Loop through each file that is selected and then add it to the list box.
         For Each varfile In .SelectedItems
            Me.FileList.AddItem varfile
            'Me.txtSourceFile = varFile
         MsgBox "You clicked Cancel in the file dialog box."
      End If
End With

End Sub

Private Sub cmdImportAndClean_Click()

Dim FileList As Variant

    If MsgBox("This function will copy the database you have selected to the destination you have entered.  Do you wish to continue??", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    FileCopy Me.FileList.RowSource, "C:\Temp\" & Me.txtNewName & ".accdb"
    MsgBox "The file has been copied."

    Exit Sub
    End If

End Sub

Open in new window

Creating a separate database for each client is not the solution.
I realize this is out of the ordinary
There is out of the ordinary and there is insanity.  I'm trying very hard to not hand you the bullet that you are going to load into your gun so you can shoot yourself in the foot.  Creating a separate database for each client is a seriously bad idea.

Why do the pictures have to be in the database?  Couldn't they be in a folder by client and the application simply points to them?  The app can still open them and display them.

If you are that worried about space, use SQL Server to hold the data.  It can hold much more data than Access can.

The bullet for your gun is this (in case I wasn't clear, I feel very strongly that this is a bad choice):
You cannot change the name of the file you have open or any open file for that matter so you can't do this the way you requested.  A possible solution is to create a separate database (what's one more) that opens to a form where you enter the client information.  When you press a button, the app copies your template database into the production folder and gives it the name of the client.  It then runs an insert query to insert the data you just collected on the form.  At this point, the code could open the new database and close itself so control transfers to the new database and you can continue working.

Good luck.  You're going to need it.  This decision will come back to bite you.
Jeffrey CoachmanMIS LiasonCommented:
Looks good...

Managing multiple "files" seems a bit much for me, ...but if this works for you, then roll with it.

SteveL13Author Commented:
Am rolling.  Thanks.
Jeffrey CoachmanMIS LiasonCommented:
Oh, I posted when Pat was writing...

See her thoughts as well.
This might seem like a good idea at fist, ..but may quickly become a nightmare to manage...

Creating separate "Client Databases" just to avoid db image bloat seems like overkill.
If you truly have that many images to manage, then you need to think about "linking" the images, ...not storing them in an attachment field.

Finally note that with this many customers and images, really need to think about an upgrade to SQL server, where Attachment fields are *not* currently supported (another reason to avoid Attachment fields, and go with a linking strategy)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.