Auto Import Excel Data into a Running Access Database

[novice1979]

Hello, I am hoping someone here can help me out. I am a total novice as it pertains to Excel and Access. I work for a company where I manage people who track their productivity using pre made sheets. To make this process better, I created an excel program where the user inputs all the data and logs it in a seperate tab(this is done using simple macros and basic VBA). However from what i have read, it is not a good idea to keep a running database in excel as that is not what it is made for (potential for corrupted data, slow running excel file, etc.). I want to create a macro that automatically moves the excel database to a database in access. I have tried figuring this out for a week, but had no luck. I want this to be an automated process (Say, at 5 PM each day, my macro automatically moves all data for that day out of excel into am access database). My question seems simple but maybe its not. If anyone needs to see my file I can send that.



I hope someone can help point me in the right direction.

Thanks in advance.

[June7]

No, it is not simple.

Why don't you develop a user interface in Access for data entry?

We started to develop a db that had Excel as user interface for data entry and output and Access merely stored the data. Lots of code was written to manage this. Then the project was handed over to me. After a few months of testing this Excel/Access collaboration, I decided to eliminate the Excel component. I built forms and reports in Access and tossed all the Excel code. Everyone is much happier.

[evander]

Hi Novice1979!

I agree with June7. I usually use Excel for advanced reporting while I use Access for data entry. But if you insist on importing Excel data into Access, perhaps you can assign a range name to the portion of the worksheet whose data you want to import in Access. Ask your users to place their Excel files on a shared network drive so Access can read these files. You can use the TransferSpreadsheet method to import data automatically. This can be a lot of work, though.

Good luck.


Evander

[novice1979]

Hi Novice1979!

I agree with June7. I usually use Excel for advanced reporting while I use Access for data entry. But if you insist on importing Excel data into Access, perhaps you can assign a range name to the portion of the worksheet whose data you want to import in Access. Ask your users to place their Excel files on a shared network drive so Access can read these files. You can use the TransferSpreadsheet method to import data automatically. This can be a lot of work, though.

Good luck.

Evander

I actually have no issue with using access for the data entry. Haha, only issue with that is I didn't even know you could do that with access. I have no training in excel/access so I am not really sure how to do that. I was able to sort of self teach myself basic macros/vba in excel but access looks to be a bit tougher. Would it be pretty tough to do what I did in excel in access for someone like me with no training?

Thanks

[June7]

That's the big advantage of Access over other relational database apps. It is self-contained - has functionality for data storage and user interface. Building forms (and reports) in Access that are bound to the storage tables is really quite easy. Much easier than developing code to transfer data back and forth between Excel and Access. So unless the Excel sheets are simple enough in structure to allow setting a link to them in Access, I recommend eliminating the Excel component.

[evander]

Hi again, Novice 1979!

I'm self-taught too. In fact, I'm not even an IT graduate. I'm an accountant by profession and started playing with Microsoft Access to make tools and solutions for my job. Cengage books are a great resource, particularly the Shelly Cashman book. You can also sign up for online courses, but you'll gain more knowledge by reading books. For building accounting systems, my first reference book was Building Accounting Systems by James Perry. However, this book isn't much useful for imitating the more complicated accounting systems like SAP, Sage, or even QB. But reading it was a good start for me.

Why don't you try uploading your Excel file so we can see if it is difficult to convert it into an Access application?