Excel VBA to add excel data to end of Access database with existing autonumber

[rickadams]

I have excel.xls spreadsheets that I would like to automatically select and append to existing Users.mdb databases. Sometimes there may only be 1 addition to one of the Users.mdb and it could add up to 500 more records. The Excel data must be imported because the program that originally accessess Users.mdb will not recognise them if they are linked. The company that wrote the original program accessing Users.mdb is now defunct and operates door controllers. The data type cannot be changed to the newer .accdb format.

Users.mdb has UserID which is an Autonumber field. How can I look for the last record in Users.mdb then append to the Users.mdb with a VBA and add to the end continuing from the last number? I am Manually doing this now through MS Access using append to file doing a lot of back and forth to check last record and change the UserID in excel before I do the transfers. I would like to automate this with a VBA. I am already using VBA in excel to rearrange the excel to the proper format for Access. Should this be done from Access 2010 or Excel 2010? For some reason I can't get the Developer tab to work no matter what I do in Access 2010.
Also If I want to select a Users.mdb database to import to, and the Excel file to export from is there a way to select which one to update to and from a windows subdirectory?

I hope I am making sense with my questions.

Thank you in advance.

 

[rickadams]

Wow I can't imagine someone else isn't trying to automate an export from excel into access, or is this not possible? (yet search after search has provided nothing) I found the developer tab in access, and posted some code. My code just sits there and I do not know how to make it do something. In Excel 2010 I go to VBA type a code (for other automation) and it does what I want it to. Access seems to make running any code a mystery or (I just can't see the forest through the trees)... My excel Users.xls document is formatted exactly the same as the Access.mdb and it imports great manually, It needs to be automated. (I copied and pasted the code below another existing sub and named it bringinusers)

Sub bringinusers()DoCmd.TransferSpreadsheet acImport, , "Users.mdb", " C:\Users\STUDIO\Documents\Users.xls", ", True"


End Sub

Thank you!