Change a Folder Name in VBA

[Benjamin Bolduc]

Hi Everyone,

I've recently set up a feature that creates a folder on our server for every customer we have in the DB. It is working great, but the problem I'm having is when we need to change the Account Name.

The Account Name is the primary key throughout the entire Customer Service DB. When the Account Name needs to be changed, a set of code asks for the new name and then runs about a dozen update queries to change all the records in all the tables.

The problem is that the folder, which was named after the Account Name, does not change, and I can't seem to find out how to change folder names using VBA, only make and delete. The button that opens the folder from the Customer Service form needs the folder to have exactly the same name as the Account Name, otherwise it just creates a new folder leaving all the files in the old folder.

I hope this is making sense! If anyone could point me towards the code to do this, I would be very much appreciative.

If you need any other information, please just ask.

Thanks!
Ben Bolduc

 

[pbaldy]

Look at the Name statement in VBA Help. By the way, if the account name can change, it is a poor candidate for a primary key. You should either use account number if it doesn't change, or perhaps an autonumber.

 

[Benjamin Bolduc]

Thanks for the tip! I looked up the Name Statement and it was actually very easy. I just didn't know where to look before.

As for the Primary Key, why is it not ideal when the value can change? Does it mess up the indexing or cause preformance degradation?

Thanks for your help!

Ben

 

[pbaldy]

When the Account Name needs to be changed, a set of code asks for the new name and then runs about a dozen update queries to change all the records in all the tables.

In a sense you've answered your own question. If you have to run a dozen update queries when a name changes, something is wrong. If you do some research on normalization, I think you'll find that the name field is not the best choice of keys.