Adding Unique Incremental Number to a String in Database Field

[webzavi]

I'm working on an old CMS for a client. The CMS is software based. All the website content is on the customers Computer and gets published to web as static pages on upload.




The CMS is using MS Access 2000 database. There's a table called Products and there's a field called product-page that stores the names of all the product pages for all the products.


The issue I have is not all product pages are unique and there are over 15,000 products stored in the Database.


I'm looking to make all the product pages unique by adding a unique number to each product pages. Is there a simple away to do this using SQL in MS Access 2016?


i.e. product-page-name.html becomes product-page-name1.html, product-page-name2.html etc


I could easily go into Excel and do this, but due to the volume of products I can't past the column back into the Database so I need SQL to do this for me.

[ranman256]

just add a new field PRODID as AUTONUMBER. Save table. It will create unique id for every record.

[webzavi]

Sadly because of how the Database is connected to the CMS Software I can only adjust the one field. It's a one off process. I just need to tidy up field with the data in it in one go

[ssanfu]

It sounds like you just want to rename each of the html pages.

Is this before the html page is created? Or while the page is being generated?

You should be able to do this in VBA......