Importing Biometrics Data to MS Access

[wind20mph]

Done with the Equipment Management Systems. Thanks for the help.

I am now in the payroll system.

I would like to know how to automatically import biometrics data into VB-MSaccess procedure, if there are samples then it would be great help. right now, I designed the data to manually input in an entry form.

I am a slow learner so please go easy on me... thanks in advance.

 

[jdraw]

More info please. Describe the type of info to be imported; what is the link to payroll specifically; how many users are planned....

What tables do you have in your design?

 

[wind20mph]

More info please. Describe the type of info to be imported; what is the link to payroll specifically; how many users are planned....

What tables do you have in your design?

The typical Payroll System with 8 users 4 of them are maker, 2 of them are approver and 2 of them are admin. The link will be the data from biometrics which is composed of time and date (in and out) that will be collected for bimonthly and daily basis. computation and structure for payroll is already working... attendance from the biometrics are add-on. and i do not know how to import it. it is a text file with an extension of ".tbp " and my program supposed to automatically compute the late, absent and undertime, instead of manually entering them.

The payroll was used from Access95 (1996) now in Access 2010 and the attendance is manually entered instead of automated. so my problem is the automation on how to gather data from biometrics to automatically recorded in access table for number of days worked and time-in/time-out for recording of late and undertime. instead of reading the biometrics program manually and enter manually because that will be a redundant work instead of shortening the payroll creation.

Thanks.

 

[wind20mph]

This is an existing MS Access Database that is migrated from Access 97 to Access 2010. And trying to add a feature that will automatically record Biometrics data. And I have no Idea how to make it. I cant Read the biometrics data.

 

[jdraw]

You can't read the data???

Google .tbp extension
and see if that helps.

 

[wind20mph]

i can read in notepad. but not directly in ms access. no idea how to do it. i've searched from the forum for procedures but can't find any.

 

[namliam]

Well if it is a text file you can simply use one of two methods:
1) The easiest, but requires the file to be pre-formated nicely into some sort of columns:
docmd.transfertext
More details, see the help files.
2) A little harder, you open the file and find your infomation (semi) manually.
Using
Open Yourfile for Input as #1
Line Input #1, strLine
Do while not eof(1)
Debug.print strLine
'.... ***
Line Input #1, strLine
Loop
Close #1

On the line marked with *** you will need to add logic to make sence of the data and add it into table(s) This is way more flexible but harder to do.

Let me know what problems you run into using either method and we will see what we can do to help you.

 

[wind20mph]

Well if it is a text file you can simply use one of two methods:
1) The easiest, but requires the file to be pre-formated nicely into some sort of columns:
docmd.transfertext
More details, see the help files.
2) A little harder, you open the file and find your infomation (semi) manually.
Using
Open Yourfile for Input as #1
Line Input #1, strLine
Do while not eof(1)
Debug.print strLine
'.... ***
Line Input #1, strLine
Loop
Close #1

On the line marked with *** you will need to add logic to make sence of the data and add it into table(s) This is way more flexible but harder to do.

Let me know what problems you run into using either method and we will see what we can do to help you.

I tried the method 1 yesterday and it didn't give the data as required in the automation. Now trying the method 2. I'll keep you posted of my progress.

The only Procedure I was aiming to achieve was BIOMETRICS (Feb.tbp) - > MS ACCESS Attendance (frmATTMain) -> tblAttMain -> frmMakePayroll -> tblPayrollData ->PaySlip.


Thanks again for the enlightenment.

 

[wind20mph]

Still confused with the loop;

Sub OpenTBP(strFileName As String, strPath As String)
Dim hbkcn As ADODB.Connection
Dim hbkrs As ADODB.Recordset
Dim hbkfld As ADODB.Field
Dim hbkrecord As Integer
Dim strRecord As String
Dim strSQL As String
hbkrecord = 0

Set hbkcn = New ADODB.Connection


If Right(strFileName, 3) = "tbp" Then 'hbkcn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & "DBQ=" & strPath & "\" 'need month.tbp file
hbkcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "\;Extended Properties='text;HDR=No;FMT=Delimited(|)'" 'need month.tbp file
End If

Set hbkrs = New ADODB.Recordset
hbkrs.Open "SELECT * INTO monthTBP FROM [" & strFileName & "]", hbkcn, adOpenStatic, adLockOptimistic, adCmdText


'Do Until hbkrs.EOF
'For Each hbkfld In hbkrs.Fields
'strRecord = strRecord & "|" & hbkfld.Value
' Next hbkfld
' strRecord = strRecord & vbCr
' hbkrecord = hbkrecord + 1
' hbkrs.MoveNext 'Loop
'Debug.Print strRecord
'hbkrecord = hbkrs.RecordCount

hbkrs.Close

Set hbkrs = Nothing

MsgBox "Text was opened and there are " & hbkrecord & " records in the table."
hbkcn.Close
Set hbkcn = Nothing

End Sub

Please help me clear things up. Thank you in advance.

 

[namliam]

1) Format your code
2) you are using an ADO connection, which isnt what I suggested at all... it overcomplicates things IMHO.
3) You cant open a "Select ... Into" query as a recordset, this is an action query which wont return any records

 

[wind20mph]

I have no problem with the query. Just the extracting tbp content. And ADO connection is just fine. I am Using MSAccess 365. accdb connection.

 

[Mihail]

Post a .tbp file and say us how is this organized.

 

[wind20mph]

uploaded here. extracted from biometrics: http://www.mediafire.com/view/u7zas0tryx6pcux/February01.tbp

 

[Mihail]

Post a .tbp file and say us how is this organized.

I can understand that are some dates and some data under this dates.
But I have no idea what mean this, what is a biometric file, what fields should be exctracted from here etc.

 

[namliam]

This seems to be a fairly straightforward tab seperated text file?

I can see dates as columns, 26 Jan 2014, 27 Jan, 28 Jan up to 13 Feb
I can see different times, assuming per your description here in the post, time in and time out in sequence, also given they always seem to be in even pairs.
I can see some kind of identifier, 00000085 and 00000004, which I assume to be key IDs or something like a badge id.

Importing this with a recordset/queries like you are trying to do is going to be a NIGHTMARE.
I can strongly advice using the open method as I mentioned earlier.

I made an empty database, created a table called tblBiobetricData with 4 columns:
BadgeID, Workdate, TimeIn, TimeOut

I saved your file to my H drive called it test.txt for now.... and ran this code ... seems to work just fine

Sub ImportFile()
    Dim Dates() As String
    Dim TimesIn() As String
    Dim TimesOut() As String
    Dim MyID As Double
    Dim strLine As String
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tblBiometricData")
    Open "H:\Test.txt" For Input As #1
    Line Input #1, strLine
    Do While Not EOF(1)
        Debug.Print strLine
        If InStr(1, strLine, Chr(9)) = 11 Or InStr(1, strLine, Chr(9)) = 12 Then ' ID
            MyID = Left(strLine, InStr(1, strLine, Chr(9)))
        ElseIf InStr(1, strLine, Chr(9)) = 9 Then ' Dates
            Dates = Split(strLine, Chr(9), -1)
        Else ' Times
            TimesIn = Split(strLine, Chr(9), -1)
            Line Input #1, strLine
            TimesOut = Split(strLine, Chr(9), -1)
            For i = LBound(Dates) To UBound(Dates)
                Debug.Print MyID, Dates(i), TimesIn(i), TimesOut(i)
                If TimesIn(i) <> "" Or TimesOut(i) <> "" Then
                    rst.AddNew
                    rst!BadgeID = MyID
                    rst!WorkDate = DateSerial(Right(Dates(i), 4), Left(Dates(i), 2), Mid(Dates(i), 3, 2))
                    rst!TimeIn = TimeValue(TimesIn(i))
                    rst!TimeOut = TimeValue(TimesOut(i))
                    rst.Update
                End If
                    
            Next i
        End If
        If Not EOF(1) Then Line Input #1, strLine
    Loop
    Close #1
    rst.Close
    Set rst = Nothing
    
End Sub

I hope that gets you going.
Sample data from the table:

BadgeID	Workdate		TimeIn		TimeOut
85	1/26/2014	8:00:00 AM	9:07:00 AM
85	1/27/2014	7:51:00 AM	12:08:00 PM
85	1/28/2014	7:55:00 AM	12:18:00 PM
85	1/29/2014	7:59:00 AM	10:02:00 AM
85	1/30/2014	8:02:00 AM	12:00:00 PM
85	1/31/2014	7:05:00 AM	7:08:00 PM
4	1/26/2014	7:16:00 AM	12:05:00 PM
4	1/27/2014	8:00:00 AM	1:01:00 PM
4	1/28/2014	7:05:00 AM	7:08:00 PM
4	1/29/2014	8:00:00 AM	10:25:00 AM
4	1/30/2014	12:58:00 PM	7:02:00 PM
4	1/31/2014	7:51:00 AM	12:08:00 PM

 

[wind20mph]

Thank you very much mailman!!

You save my headache from the last 3 weeks.

Yeas the numbers 0000000085 and 0000000004 refers to Employee ID and the Dates refers to the working days. And the sequence for time-in and time out. That I would like to import into table and filter the 8:00 to 19:00 on Tuesday and Thursday and 8:00 to 18:30 on Monday and Wednesday and 8:00 to 18:00 on Fridays and overtime pay on Saturdays and Sundays.

Again thank you very much.

I will use DAO instead of ADODC... as per your example.

 

[namliam]

Dont quite think you are grasping the difference here.

It has nothing to do with ADO or DAO, though I do prefer DAO myself.
You were using a completely different method, trying to use a query.... where obviously in this case that will cause problems.

 

[wind20mph]

Thank you very much for the information and for sharing your knowledge ... My problem has been solved. Again Thank you very much.