Avatar
Board Regular
- Joined
- Sep 20, 2005
- Messages
- 193
Greeting all,
Quick Explanation:
250 users complete a form built in excel stating what they've done each day. I want this information to be passed to a central Access '97 database to be stored.
Upon opening, the worksheet queries the system via a .dll returning the logon (Employee ID). It then queries the database and returns their team name, process list (list of available processes allocated to their team). The user selects the day they wish to complete, and fills in how many of each process they have completed and the time spent on each process.
This information then needs to be sent back to the database and stored.
This information is then stored in a table called "tbl_Process_Log" setup as below:
Date | Short Date
Employee_# | Text
Team | Text
Start_Time | Short Time
Finish_Time | Short Time
Lunch_Taken | Number
Process_# | Number
Number_Completed | Number
Time_Taken | Number - stored in minutes
Comments | Text (255)
The reducing of the data to only what needs to be stored is easily done, but how do I send the information to the database? Unfortunately I have very little experience with this.
I've been trying to do it using "Microsoft DAO 2.5/3.5 compatibility library", but to be honest, I have no idea how to make this work... this is what I have currently:
Some of the above was taken from a post by “acw”
Could someone explain what a workspace is please? I’m having trouble locating information on it...
Quick Explanation:
250 users complete a form built in excel stating what they've done each day. I want this information to be passed to a central Access '97 database to be stored.
Upon opening, the worksheet queries the system via a .dll returning the logon (Employee ID). It then queries the database and returns their team name, process list (list of available processes allocated to their team). The user selects the day they wish to complete, and fills in how many of each process they have completed and the time spent on each process.
This information then needs to be sent back to the database and stored.
This information is then stored in a table called "tbl_Process_Log" setup as below:
Date | Short Date
Employee_# | Text
Team | Text
Start_Time | Short Time
Finish_Time | Short Time
Lunch_Taken | Number
Process_# | Number
Number_Completed | Number
Time_Taken | Number - stored in minutes
Comments | Text (255)
The reducing of the data to only what needs to be stored is easily done, but how do I send the information to the database? Unfortunately I have very little experience with this.
I've been trying to do it using "Microsoft DAO 2.5/3.5 compatibility library", but to be honest, I have no idea how to make this work... this is what I have currently:
Code:
Sub test()
'Reference Microsoft DAO 2.5/3.5 compatibility library
Dim wrkjet As Workspace
Dim db As database
Dim rst As DAO.Recordset 'Edit based on lozzablake's post. thanks.
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet) 'I have no idea what this does..?? (Copied directly from a post by acw)
Set db = wrkjet.OpenDatabase("N:\Server Apps\Electra Productivity\Productivity Server.mdb")
Set rst = db.OpenRecordset("User_Access_List", dbOpenDynaset)
For intRowCount = 1 To Cells(Rows.Count, "A").End(xlUp).Row
With rst
.AddNew
!Employee_# = strEmployee_Number
!Date = dtSelectedDate
!Team = strEmployee_Team
!Start_Time = varStart_Time
!Finish_Time = varFinish_Time
!Lunch_Taken = intLunch_Taken
!Process_# = Range("A" & intRowCount).Value
!Number_Completed = Range("B" & intRowCount).Value
!Time_Taken = Range("C" & intRowCount).Value
!Comments = Range("D" & intRowCount).Value
.Update
End With
Next intRowCount
rst.Close
db.Close
End Sub
Some of the above was taken from a post by “acw”
Could someone explain what a workspace is please? I’m having trouble locating information on it...