Read MS Access Database to Get Last Record

[Vladamir]

I have an MS Access database which contains records on drawing files. Each record has a drawing number field in which a sequential number is manually entered. I have an Excel spreadsheet as well that is related to this database. What I wish to do is to have the user click a button in Excel to start the process by which the Excel VBA code will open the database, read in the last record to establish the most recent drawing number assigned, add 1 to it and then enter the new record about the drawing into the database.

My problem is that so far the testing I've done, which is no where near what I'm describing above, I notice that the records in the database are not necessarily in sequential order. That is the record which has the highest drawing number may not necessarily be the last record. So I'm assuming that I will need to sort the records on this drawing number field first before reading in the last record.

[Vladamir]

OK...I have got this to a point where inside of Excel VBA I can get the highest value of the dwgno field from the Access database. I created a new query in the database and told it to find the MAX of the field and then +1 to it. I recorded a macro in which I simply did a Data/Import External Data/New Database Query and then selected the new query in the database. A few other setting were set and without much trouble I got the value from the database query to show up in the proper cell in this Excel spread sheet. Once this is done, I need to allow the user a few seconds more to complete the entry of the rest of the data and then I need to take some of this data from the Excel spread sheet and export it to the database and then commit the new record. Easier said than done... I took the recording of the macro I did to get the next drawing number and stuck it into VB 2010 Express...and as usual with my experiences with VB 2010 Express....code from the old VBA will not work with VB 2010 Express... I hate when that happens.

I also need to concern this process with the rare times when two or more users may try to access the database at the same time. Can anyone help me with what to do next?