Is it possible to add data/new record to an Access database table from Excel?

[pharmacologist]

I have an Excel template file that is used for a specific project but many projects that I want to track in Access. I have a table of records in my excel template and a unique ID that is generated based on the date and the user, and would like to be able to update the Access database from the Excel file and create a new record with the unique ID and corresponding data if it hasn't been added yet. Is this possible and if so can someone point me in the right direction to learn how to accomplish this? I have been programming in VBA for my Excel template and would be comfortable doing it in VBA if necessary.

[CK76]

It's possible, but will require VBA (typically using ADO).

I'd recommend uploading sample workbook of how your data is structured.

But personally, I prefer to set up linked table using Excel sheet as source. Then use query to update the table in Access.
Ex: Assuming both have same columns and linked table is named "Import"...

Please Login or Register  to view this content.

[pharmacologist]

Thanks for the reply. I'd prefer not to have to update from Access as there will be multiple users with individual Excel files for each project and I don't want to have to repeatedly update from each of them. I'd also like the Access database to be the central location that can be updated from the individual Excel files using the uniquely generated keys, that way when as user updates data in their project it will update the entry in the database. I'm also hoping this can serve as redundancy in case someone deletes/loses the excel file, as I'm planning to be able to pull records whose unique ID contains the project identifier and regenerate the Excel file.

The structure of the Table isn't that complicated. Each row just contains the unique identifier that is projectcode-date-user-autonumber e.g. P0001-20190926-BLT-01 and a bunch of values in different columns.

I will look into ADO, thanks.

[CK76]

If each user is updating individual sheet. Why not just create master file where PQ is used to collect data from individual files?

If you need it in Access, then you can load it from the master workbook. Much safer than multiple users pushing data to Access from Excel using VBA.

[pharmacologist]

I would have to update the master file to include new sheets as they're created though right? I wouldn't necessarily know when to do it and I don't want users messing with the database/master file on their own. I'm hoping to not have to update that manually but instead have it updated when users create a new entry. What could happen with multiple users adding data to the Access database? If it would rarely happen at around the same time, i.e. half an hour, what problems could arise?

[mc84excel]

What could happen with multiple users adding data to the Access database? If it would rarely happen at around the same time, i.e. half an hour, what problems could arise?

From past experience, it is possible to have multiple users - each using separate files as frontends - carrying out read/write operations (via ADO) to/from a separate Access database.
It worked fine in the short-term. Over time it became unfeasible due to the increasing number of users. (At that point we moved the access DB to a SQL server)

TL;DR = It will work but performance depends on the number of end users.

[pharmacologist]

From past experience, it is possible to have multiple users - each using separate files as frontends - carrying out read/write operations (via ADO) to/from a separate Access database.
It worked fine in the short-term. Over time it became unfeasible due to the increasing number of users. (At that point we moved the access DB to a SQL server)

TL;DR = It will work but performance depends on the number of end users.

Thanks for the reply. How many users would you say it could handle safely and is that simultaneous users? I don't imagine it ever going beyond 5 to 10 users.

[mc84excel]

Thanks for the reply. How many users would you say it could handle safely and is that simultaneous users? I don't imagine it ever going beyond 5 to 10 users.

Unfortunately I can not be precise as I wasn't directly involved with that project. As I recall ~8 to 10 users was acceptable. Above that - Performance suffered and global disconnections began to occur without warning as the number of end users headed closer to 20.

Your mileage may vary (e.g. network speed, read/write speed of Access file, size of database, frequency of read/writes)

Yes users could use simultaneous but I couldn't say how many of the total user base would have been actively reading/writing to the Access database at the same instant.

[CK76]

Hmm, why not just use Access then? You can create form within and have people use it to enter data/retrieve data.

I.E. Split database to front-end components (forms, query etc) and back-end for data storage.

https://support.office.com/en-us/art...rs=en-US&ad=US