I am writing an application in MS Access.
I need to extract data from an Oracle database and load the results into a temporary table in MS Access so that it can be processed in VBA code.
The data being extracted takes the form of:
Code Job Type User_ID Dstamp
Job Start PICK STWMITCHELLB 2011-08-05 13:08:01
Job End PICK STWMITCHELLB 2011-08-05 13:08:36
Job Start PICK STWMITCHELLB 2011-08-05 13:08:17
Job End PICK STWMITCHELLB 2011-08-05 13:08:02
Job Start BREAK STWMITCHELLB 2011-08-05 13:08:47
Job End BREAK STWMITCHELLB 2011-08-05 13:08:46
Job Start PICK STWMITCHELLB 2011-08-05 13:08:56
Job End PICK STWMITCHELLB 2011-08-05 13:08:04
I need to get the data into a temporary table because the data is saved with starting times and ending times in different records and I need to go through each record to match a Job Start with the next Job End for a Job Type for a User.
So for instance in the table above I would start at record 1 and get Job Start for Job Type = Pick for User ID STWMITCHELLB, then go to record 2 and because it was Job End and matched the Job Type and User ID from record 1 I would take record 1’s timestamp away from record 2 and that would give me a duration for that User on that Job.
This table exists in out Oracle database. I need to create temporary table in MS Access so I can work on the results.
The VBA I currently have is this:
This successfully grabs me the information that I need but I now need to get it into the new table.
I need to do all of this programmatically in VBA preferably using ADO and not DAO.
Any assistance would be much appreciated.
I need to extract data from an Oracle database and load the results into a temporary table in MS Access so that it can be processed in VBA code.
The data being extracted takes the form of:
Code Job Type User_ID Dstamp
Job Start PICK STWMITCHELLB 2011-08-05 13:08:01
Job End PICK STWMITCHELLB 2011-08-05 13:08:36
Job Start PICK STWMITCHELLB 2011-08-05 13:08:17
Job End PICK STWMITCHELLB 2011-08-05 13:08:02
Job Start BREAK STWMITCHELLB 2011-08-05 13:08:47
Job End BREAK STWMITCHELLB 2011-08-05 13:08:46
Job Start PICK STWMITCHELLB 2011-08-05 13:08:56
Job End PICK STWMITCHELLB 2011-08-05 13:08:04
I need to get the data into a temporary table because the data is saved with starting times and ending times in different records and I need to go through each record to match a Job Start with the next Job End for a Job Type for a User.
So for instance in the table above I would start at record 1 and get Job Start for Job Type = Pick for User ID STWMITCHELLB, then go to record 2 and because it was Job End and matched the Job Type and User ID from record 1 I would take record 1’s timestamp away from record 2 and that would give me a duration for that User on that Job.
This table exists in out Oracle database. I need to create temporary table in MS Access so I can work on the results.
The VBA I currently have is this:
Code:
Private Sub bt_Go_Click()
Dim msg As Variant
Dim sSQL As String
Dim sConn As String, sServer As String
Dim rst As ADODB.Recordset, cnn As ADODB.Connection
Dim fld As ADODB.Field, iCol As Integer, lRow As Long
Dim qDate As Date
Dim sDateFrom, sDateTo As String
On Error GoTo Err_bt_Go_Click
' Connect to Database
Set cnn = New ADODB.Connection
sServer = "some server ID"
cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=" & sServer & ";" & "Uid=Username;" & "Pwd=Password"
Set rst = New ADODB.Recordset
qDate = Me.cal_Shift.Value ' Get Date from Shift Calendar
' Convert Dates to SQL String
sDateFrom = Format(Day(qDate), "00") & "-" & _
Format(Month(qDate), "00") & "-" & _
Format(Year(qDate), "0000")
sDateTo = Format(Day(qDate), "00") & "-" & _
Format(Month(qDate), "00") & "-" & _
Format(Year(qDate), "0000")
msg = MsgBox(sDateFrom & " to " & sDateTo, vbOKOnly, "Msg")
sSQL = " SELECT it.CODE, it.JOB_ID, it.USER_ID, TO_CHAR(it.DSTAMP, 'YYYY-MM-DD HH24:MM:SS')"
sSQL = sSQL & " FROM INVENTORY_TRANSACTION it"
sSQL = sSQL & " WHERE it.CODE = 'Job Start' OR it.CODE = 'Job End' AND it.DSTAMP >= TO_DATE('" & _
sDateFrom & " 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND it.DSTAMP <= TO_DATE('" & _
sDateTo & " 23:59:59', 'DD-MM-YYYY HH24:MI:SS')"
sSQL = sSQL & " ORDER BY it.DSTAMP"
' Run SQL Query
Debug.Print sSQL
msg = MsgBox(sSQL, vbCritical, "sSQL")
rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
' On Error Resume Next
Debug.Print rst.RecordCount
rst.MoveFirst
' Paste results onto worksheet
If Err.Number = 0 Then
' Execute code here on results from SQL
msg = MsgBox(rst.RecordCount, vbOKOnly, "Msg")
rst.Close
cnn.Close
End If
Exit_bt_Go_Click:
' Close and remove object variables from memory
Close_Subroutine:
Set rst = Nothing
Set cnn = Nothing
Exit Sub
Err_bt_Go_Click:
MsgBox Err.Description
Resume Exit_bt_Go_Click
End Sub
This successfully grabs me the information that I need but I now need to get it into the new table.
I need to do all of this programmatically in VBA preferably using ADO and not DAO.
Any assistance would be much appreciated.