Generate Temp Table in Access from ADO SQL query in VBA

[Ikon]

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:

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.

 

[DJkarl]

You could create a temp table like this.

currentproject.Connection.Execute "CREATE TABLE [TempTable] (Code CHAR, Job CHAR, Type CHAR,  User_ID CHAR, Dstamp DATE)"

Then just open a recordset object similar to what you did for the Oracle connection and loop though it populating the table with your recordset data.

 

[Ikon]

Thanks for the reply.

So would I then need to run through each record 1 by 1 using an INSERT query to add each record?

From what I've read this could end up being quite slow but given that I probably won't be copying thousands of records at a time this may not be the case.

Is this the only option or is there a way of copying a SELECT query straight into a table similar to using the query builder to build a SQL pass-through query where the results get displayed in a window?

 

[DJkarl]

Thanks for the reply.

So would I then need to run through each record 1 by 1 using an INSERT query to add each record?

From what I've read this could end up being quite slow but given that I probably won't be copying thousands of records at a time this may not be the case.

Is this the only option or is there a way of copying a SELECT query straight into a table similar to using the query builder to build a SQL pass-through query where the results get displayed in a window?

Not using the constraints you've placed on yourself. By opening an ADO connection to the Oracle server all your queries are running essentially on that server, a local Access table would not exist so therefore you cannot append data from your recordset to a local temp table. The fastest way in terms of performance would be to link the table, and run an append query into a local temp table. You will likely get much faster results than any method using recordset objects.

 

[Ikon]

Ok great

We have to use ADO as our IT department is not too keen on us linking table with the main database.

Thanks a lot for your help

- Mike