VBA to Import from Excel to end of Access existing table

[Asking4help]

I have an Existing database.mdb table with 24 different columns. I need to be able to Import 1 record and up to 500 records to this table from Excel file starting after the last record. I can't find a VBA that will help with finding the last record. This will need to be fully automated. This is the format of both Users.xls and Users.mdb

UserID ChsetIdx FirstName LastName MiddleName Street City Zip State

HomePhone Workphone LastEventLog ExpirationDate NeverExpiers Active Deleted

GotTransmitters GotCards GotEntryCodes GotPhoneEntryNumbers CustomType1

CustomType2 CustomType3 CustomType4

The first thing is to select Users.xls from a directory the user selects and import it to the proper Users.mdb (there are multiple in different directories) Note: Nothing can be modified to the newer format Users.accdb because it is also being read and written to by a legacy program.

UserID in Users.mdb is autonumbered and currently may end in 30 and up to 650 depending on the users.mdb selected. So I need to start importing where the original database ends.

Can someone please help with this?
Thank you

[Luuk]

There is no such thing as a 'last record' in Access, because all records can be ordered in different ways, which means the 'last record' always depends on the ordering.

If a field is autonumbered than you should never update this field. When a new record is created it will 'autmatically' get the last used value +1.

For the '+1' part: The 'Increment' must be set to 1, of course....

[Asking4help]

Luuk, Thank you for responding so quickly! I am importing my data manually from Access leaving the Autonumber field blank, It is working great. I just need some guidance to get a VBA to run from either Access or Excel so this can be done automatically. Thank you Again!

[twinnyfo]

Asking4Help,

Welcome to Bytes!

Why would you keep the Autonumber field blank? That defeats the purpose of having an Autonumber field.

As Luuk rightly describes, the “last” record in a database is simply the last one entered into the DB. But, because of ordering in the Table, it’s location can change. However, if you have an Autonumber field, USE IT, and then, the last record you enter will always have the highest value in the Autonumber field. You can still sort and order the table however you want.

Perhaps we are both missing something that isn’t clear from your description?

[Luuk]

@twinnyfo: An autonumber field is automatically updated with the next value based on it's settings, if you create a new record, or is it not?

In MS-SQL it works this way, so I guessed it works this way in Access True I should have tested this to know for sure, but … 😊

[twinnyfo]

@Luuk,
Absolutely. In a table (emphasis IN A TABLE) if you have an auto number field, it automatically increments by one, based upon the highest value currently in that field (Access doesn’t go back and fill in missing numbers). I don’t know if it is even possible, as suggested by OP, that you would have an auto number field that is blank. That is like saying you set a default value for a Yes/No field to True, but for every new record you create, I’ll just keep it Null for now. It simply doesn’t work that way.

Setting up your table with an auto number field (particularly as your primary key) is the easiest way to index your tables with each other and add new records without having to think about creating a unique index or identifier for each record. These things can be done, but it is much more work than simply allowing Access to do it for you.

I know you have a pretty good grasp of these things Luuk, but I’m not sure it is clear to OP. This is not meant as an insult to OP either. Some of these things are just more difficult to grasp initially for those who are new to database and table design.

Always good to interact with you!

[Asking4help]

Thank you all for your responses. The UserID is the auto number field and that is controlled by a legacy program. I have a space for the UserID (auto numbered) and the import is null in my Excel import. When manually importing the Excel info into Access from Access it does add to the end of the existing and fills in the next number as expected. I am not trying to insert data mid database, just to the end. I thought I needed to fill autonumber so that would continue adding to the end when running a VBA. Since it works great when I manually import from Access I just need some guidance on creating a VBA so this can be done automatically. I really hope I am explaining this correctly. Thank you all again.

[twinnyfo]

No you are not making things any more clear.

Exactly what is it that you are asking for in this post? You keep talking about two things: 1) an Autonumber field, which is really moot, because if it is an Autonumber field, the numbers take care of themselves and 2) “adding records to the end of a database”, which, again, is moot, because there is no “end of a table”; there are only records. But if the records are Autonumbered, the highest numbers are, for lack of a better term, “at the end of the table.”

All of this seems to have its own, built in solution to it all, as Luuk and I have described.

However, I get the impression that you are not really asking about this, but that you are looking for code so the user can select an Excel file and import that file into the DB. Is this the real nature of your question?

If so, there are several ways to do this, but generically, you would want to allow the user to browse for a particular Excel file, then you would either programmatically link that file as a table or open it as an Excel Object, and then simply import the data from the file. Unfortunately, we probably won’t build that procedure for you. We will walk through the steps and help you troubleshoot.

[Luuk]

twinnyfo is right, I just looked that the list at the end of this page, under Similar topics, and did see How To Import Excel Into Access, which also des not include some code.

@Asking4help: The object you are facing that you should not be asking for help, but you should try to do it yourself, and after you are really stuck half-way, post the partial solution you have, and the error-message which you cannot get around.

Than someone in het might give a tip on how to solve that error. (Or start whining about that you are doing it 'al-wrong' 😂😂😂)

[Asking4help]

Luuk, Thank you again for your response! Yes I am asking for guidance to search, open, and then import an excel file into access. What I am doing manually now works perfectly every time. Not knowing exactly what ask for help has been frustrating, I have spent countless hours using search engines. I did find a sample and modified it, yet I do not know how to make it do anything. It is saved in the "Microsoft Visual Basic For Applications" However I do not know how to Run It per se. In excel I just open the developer tab and open my macro then run the macro, Apparently it is not so simple in access.
I added the following Code (probably incorrect), how do I get it to do something?

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

Thank you again.

[Asking4help]

However, I get the impression that you are not really asking about this, but that you are looking for code so the user can select an Excel file and import that file into the DB. Is this the real nature of your question?

Yes this is exactly what I am asking for, yet I don't know HOW to do any of this in Access 2010.

[twinnyfo]

What you are asking for is for us to do several things for you:

Using VBA,

• How do I browse for a specific file?
• How do I programmatically link a spreadsheet as a Table In MS Access? - OR -
• How do I Import a spreadsheet into a MS Access Table?

I will be honest with you that we will not do these things for you. Nearly all the folks on this Forum have real jobs and offer assistance (free) in our spare time. Each of these topics can be searched for and there are plenty of examples, both here on Bytes, MS Reference pages etc. We are glad to troubleshoot problematic areas that your re stuck with, but we cannot build this project for you.

Because you are a beginner in Access, this may seem terribly daunting for you—and we fully understand. We have all been there. However, in our experience (I have 20+ years experience doing this), the best way to learn how to do something is to simply do it. You will make mistakes—and that is the purpose of Bytes, to look at hurdles that you encounter and offer solutions.

What I have just offered above in this list, is, in fact, the solution to your question. It is not the end result. However, as you search for answers on “How do I browse for a file?”, and each time you browse for a file, you encounter a specific error or don’t get the results you desire, post those questions here on Bytes, and we are more than glad enough to help you work through a solution. It may take several threads until you encounter a perfectly working file browser (that you will then be able to incorporate into many other useful applications in your future DB projects—because you have now learned how to do something). Then, you attack the next steps.

We have all had to do this in our careers. It is the best way to learn, and it is the most reasonable expectation that you may have from the users on this Forum.

I am not pushing you away. I am asking you to approach the problems you are facing with a different perspective.

Looking forward to helping you through these issues.

[Luuk]

Access is not a programming tool to accomplish complex tasks.
If someone build a complex thing in Access it's because of Access, and not because of the problem.

I cannot help you in specifying a way to start your macro. The main reason for this is that I have a Dutch version of Access, which means I have to do all things in Dutch, and find some way to explain to you what the proper English names are for these commands...