Populating an MS Access Database

[Lou_Reed]

I am completing the design of a database and I am worried about populating it. It seems an arduous, thankless, painstaking task.



Then again it may not be. I believe that my database will contain up to 600 records: one for each employee of the company for which
the database is being written. I do not look forward to entering all of this with the amount of information for each employee starting
with name, social number, date of birth etc.

Think of the possibility of errors! It makes one feel insignificant!

I have an idea.

I know that MS Word files and MS Excel files can be ported to a 2010 MS Access database.

So if I let each employee enter the critical info then I do not have to enter it. It is not my responsibility; it theirs!

In the end I just upload what they wrote into MS Access.

Is this a good idea? Remember, I am trying to avoid entering the data myself. That is the goal here.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[orange]

Lou,
Are you designing and building this database for someone (eg your boss, or HR or ???)?
Do you have instructions or specifications?
Has anyone identified what specifically was to be automated/supported by this database?
Has anyone reviewed you design approach against your business processes and rules?

[rpeare]

Why not create an excel template file, have them open it up and put in the data. Then when they save the file make sure they're all saving it to the same network location, then you can pretty easily have vba cycle through the files in the folder and pull the information out.

[bcmarshall]

I've got a better idea. Why not just have people come into the office one at a time and fill in the Access screen directly. No imports. Nothing. It's there.

[Lou_Reed]

I like these last two ideas best. I guess that I must use MS Access 2010. I have used visual basic before, but not in MS Access 2010. Considering what I want to do is there some internet
site (reference) that makes or demonstrates this using VBA? If so please let me know where it is.

I want each individual to enter in their own info (as they would know it better than anyone else) then take that one info piece (I know that piece is a vague term) for each employee and add it into the Personnel table.

Then requires to some get each information unit (or piece) into the table. That strongly suggests to me to use VBA.

Respectfully,

Lou_Reed

[Lou_Reed]

I need some sort of answer to this. I am working on a tutorial

Introduction to Microsoft Access and VBA

It seems to have many lessons involved in it. I do not want to go through another tutorial series just to learn some basic steps
for using VBA in MS Access 2010.

Any help appreciated. Thanks in advance.


Respectfully,


Lou_Reed

[bcmarshall]

I don't understand your issue or why you see the need for any code. If you create a form based on tables with linked primary keys and you then allow the employees to enter their own data into the corresponding fields, the data will automatically be inserted into the tables that host those fields. There is no code required.

[Lou_Reed]

I have a lot of data that will need to entered a table. Each record in the personnel table will have represent one employee.

The place where I work has over 800 employees.

I do not wish to enter all of that data in for each employee. That is simply too monotonous.

The idea of having each employee enter in his or her personnel data is much more appealing.

I also do not want that many people to trek to my office to enter in their own data on one form; think of the damage to the carpet.

However, if each employee got a form that only required them to enter their info (or data) into that form at their location or office, not mine,
that would be a better solution.

Of course that would mean I would have all of this information given to me. Say each employee created a piece of info.

Then I would have one piece for each employee or over 800 pieces of info. Now that info could be in the form of a Excel, Word or even Access.

All of them Microsoft products and all of them compatible with MS Access 2010.

Then the question is how to get all of that into a personnel table. The table will be huge.

That is why I thought of VBA. Whenever, I come across a repetitive task such as this I think I
using a computer program. MS Access 2010 has VBA so that seemed logically what to use.

I hope this answers your question.


Respectfully,


Lou_Reed

[Lou_Reed]

I have explained in detail my situation so I guess I am looking for a way to get all of that data into one MS Access 2010 table.

I thought VBA was the way to go, but maybe I am wrong. There could be other ways.

If I can avoid using code then all the better. I still have a lot of data to get into my Access 2010 tables.



Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[redbull]

Make a little xlsm with either a form or input boxes, append query it to where ever you want? Email the sheet to who needs it?

Sub Emp_RawData_Gather()
Loops:
empfirstname = InputBox("Provide your First name" & vbCr & vbCr & "Step 1 of 9001")
EmpMiddleI = InputBox("Middle Initial" & vbCr & vbCr & "Step 2 of 9001")
EmpLastName = InputBox("Last Name" & vbCr & vbCr & "Step 3 of 9001")
SSN = InputBox("Social Security Number" & vbCr & vbCr & "Step 4 of 9001")
 
ReviewResponse = MsgBox("Lets review your information" & vbCrLf & _
"First Name : " & empfirstname & vbCr _
& "Middle Initial : " & EmpMiddleI & vbCr _
& "Last Name : " & EmpLastName & vbCr _
& "Social Security Number : " & SSN & vbCrLf & _
"If any information is not correct, click Cancel.", vbOKCancel, "Message from me")
If ReviewResponse <> vbOK Then
 GoTo Loops:
End If
'Since the user confirmed the information is correct, fire the information to access here.
End Sub



Then just have an append query below the input boxes, you could even email them their responses to confirm they are in fact correct. Or, if you care nothing about the sensitive data, use google forms or a carrier pigeon.

[Lou_Reed]

So this would not use any VBA code. However, each person getting a query must have MS Access 2010 in order to carry out the assignment?

Am I correct on this?

Thanks in advance.


Respectfully,


Lou_Reed

[redbull]

See the edit above first.

It would use vba in excel. You save it as a xlsm file and could email it to people directly.

The users would need at least access runtime in order for the query part of the code to run. Is that a problem? If so, there are a lot of solutions to that as well.

[redbull]

But honestly, I would just make a quick down and dirty form like bcmarshall suggested.

[bcmarshall]

Forms are directly tied to tables, so data entered in them goes straight to the corresponding field in the table. However, I know nothing about your network. Clearly if you have 800 employees you must have some type of network in place. Your description of all those folks walking through your office belies the reality of networking. They don't have to come to you.

Maybe one of the users here who's up on networks may have a suggestion on how you can provide instructions to the employees on how to remotely log into the Access form. Maybe you can set it up on a dedicated computer. They log in from where ever they are, fill out the form, and they're done. Later you can generate a list of those who have not filled it in and tickle them with an email reminder or something.

It's simple to create a form, and simple to tie the data right to the database tables.

[Lou_Reed]

I think that I might have found an ideal solution for my task of populating a database. I found this

https://support.office.com/en-US/art...C-A4E41A14FA98

It talks about a unique way to get data into a database and I think it would be appropriate for my needs. However, it refers in this site to MS Access 20007, I have MS Access 2010. Will this work for my upgraded MS Access?

I hope so because it looks like the solution to my data entry/collection problems.

Respectfully,


Lou_Reed