Exporting user data to an Access Database

[Avatar]

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:

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...

 

[Avatar]

Anyone?

Or can someone point me to a good site detailing how to do this sort of thing?

 

[lozzablake]

This looks OK to me. your dim statement should be:

Dim rst As DAO.Recordset

You need to have a reference set to the DAO object library. On the VBE click on Tools->Reference and then tick the check box for the DAO library

Oh and where do you assign the variables within the loop (eg dtSelectedDate)? In fact I do not see where any of the variables get assigned.

 

[Avatar]

lozzablake:
The variables are currently assigned in the sub that reduces the data, and their defined globally so I can play with this sub without passing variables around. I'll tidy it all up once I get it working.

Basically I get the following error when it hits the following line:
Line:

!Employee_# = strEmployee_Number

Error:
Run Time Error '3265':
Application-defined or object-defined error

Searches on this error return:
"Runtime error 3265: Item not found in this collection."

So what am i missing?
strEmployee_Number is a string with a format of 1 letter followed by 5 numbers eg. A99999

EDIT:
Oh, and the reference I have used is "Microsoft DAO 2.5/3.5 compatibility library"

 

[lozzablake]

as a quick check can you comment out the two fields ending with "#". I'm wondering if this a reserved character and that is causing your problem (in SQL dates are normally surrounded by a #)

 

[Avatar]

D'oh! *slaps forehead* Yep your absolutely right lol

Thanks mate