locked
Need to get an Autonumber from an Access database and use it in Excel RRS feed

  • Question

  • Is there a way to go out to Access and generate an Autonumber from a table and then carry that number back to Excel?

    My problem is that I have 5 or 6 users on a quoting spreadsheet that need to create a unique identifier for each record that they create.


    Steve Pollere

    Tuesday, May 5, 2015 1:25 PM

Answers

All replies

  • Not sure I follow but why not get the id of the last record on the assumption the next new record will be incremented by 1. But if you're giving 5 users 5 different ids how do you know in advance the order in which users will add new records, or perhaps abort.
    Tuesday, May 5, 2015 9:34 PM
  • Thanks for the reply.

    That's sort of the way we do it now. I'll try to explain in more detail. Our spreadsheet is stored on a server and there are copies on each of the 5 workstations. Each workstation is unique in that there is a finite list of numbers that they can use (i.e. User 1  1000 - 1999, User 2  2000 - 2999, etc.). Each user will increment the number by one every time they create a new quote then the info is saved back to the main sheet on the server.

    What I would like to do is, through vba, when a new quote is created, that it go out to the Access database, open the table and create a record. This record will be unique in the sense that there could be no duplicates. Then I want to take that number back to Excel and make this my quote number. In the end this will simplify my quote number generation and back me away from (what I feel) are some limitations in Excel.


    Steve Pollere

    Tuesday, May 5, 2015 10:06 PM
  • Hi Steve,

    For this requirement, you could add a new record to the access database by using DAO, then move the record to last and retrieve its ID value.

    # Microsoft Data Access Objects reference

    https://msdn.microsoft.com/en-us/library/office/dn124645.aspx?f=255&MSPPError=-2147217396

    # Add a Record to a DAO Recordset

    https://msdn.microsoft.com/en-us/library/bb243801(v=office.12).aspx

    # Recordset.MoveLast Method (DAO)

    https://msdn.microsoft.com/en-us/library/office/ff837192.aspx

    You need to add corresponding reference first if you accomplish it in excel. (e.g. Microsoft DAO 3.6 Object Libaray)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 6, 2015 6:32 AM