Putting values into temporary table (1 Viewer)

ECEstudent

Registered User.
Local time
Yesterday, 23:03
Joined
Jun 12, 2013
Messages
153
Hi,

I'm trying to put values into a temporary table for the user to view the work behind a returned calculated value.

Here is my code:

Code:
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
Dim i As Integer
Dim n As Integer
Dim Arr() As String
Dim Arry() As String
Dim x As Variant
Dim y As Variant
Dim varCode As Variant
Dim varCod As Variant
Dim Count As Integer
Dim Count2 As Integer
Dim VldOrdrNbrDestination As Integer
Dim LResponse As Integer
Dim sum As Integer
txtPartNumber = InputBox("Enter Part Number:")
If Not txtPartNumber = "" Then
        VldOrdrNbrDestination = 0
        If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") > 0 Then
            MsgBox "Part Number Found!"
    
            Count = DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'")
            MsgBox Count
            Set rst = CurrentDb.OpenRecordset( _
            "Select * from dbo_ProductStructure where ChildProductNbr= '" & txtPartNumber & "'")
    
            While rst.EOF = False
                ReDim Preserve Arr(i)
                Arr(i) = rst.Fields("ParentProductNbr")
                i = i + 1
                rst.MoveNext
            Wend
            x = Arr
    
                    Dim strSQL As String
                    Dim strTable As String
                    strTable = "tblTest2"
    
            DoCmd.DeleteObject acTable, strTable
            
            For Each varCode In x

                sum = sum + 1
                
                varCode = Replace(varCode, "-", "*")
                varCode = Trim(varCode) 'trim extra spaces from varCode
                MsgBox "'*" & varCode & "*'"
                u = varCode
                MsgBox "Look for structure"
                If DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") > 0 Then 'If what the user inputs exists in database, enter if statement
                  '  Count2 = DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'")
                  '  MsgBox "Number of Structures Found = " & Count2 & ""
                  '  Dim strSQL As String
                  '  Dim strTable As String
                  '  strTable = "tblTest2"
                    
                   ' DoCmd.DeleteObject acTable, strTable 'Delete the table if it exists
 
                    Set rstt = CurrentDb.OpenRecordset( _
                    "Select OrderNumber, Item, RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
                    While rstt.EOF = False
                        ReDim Preserve Arry(n)
                        Arry(n) = rstt.Fields("RepId")
                        n = n + 1
                        rstt.MoveNext
                    Wend
                    y = Arry

                    
                    For Each varCod In y
                        If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] = '" & varCod & "'") > 0 Then
    
                            If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
                                VldOrdrNbrDestination = VldOrdrNbrDestination + 1
                            End If
                        End If
                    Next varCod
    
              strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
              
              If sum = 1 Then
              
              CurrentDb.Execute strSQL
    
            End If
            
                Else
    
                    MsgBox "Structure Not Found"
    
                End If
    
            Next varCode
    
            MsgBox "Number of orders: '" & VldOrdrNbrDestination & "'"
    
            
            
            LResponse = MsgBox("Do you wish to view table?", vbYesNo, "")
            
            If LResponse = vbYes Then
            
                MsgBox "Chose to view table"
               
               
          '    strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
          '    CurrentDb.Execute strSQL
  
                
                
                
            Else
            
                MsgBox "Cancel table preview"
                
            End If
    
        Else
    
            MsgBox "Part Number Not Found"
        End If
        
Else
        MsgBox "Enter Part Number"
End If
End Sub



How do I put all values under all 'u' 's into one table? Thank you in advance.
 

ECEstudent

Registered User.
Local time
Yesterday, 23:03
Joined
Jun 12, 2013
Messages
153
Figured it out! Here's a great source for anyone that ever gets stuck on this like I did! :) Hope it helps!

http://stackoverflow.com/questions/4101739/sql-server-select-into-existing-table


And here is my code (after the correction):

Code:
Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
Dim i As Integer
Dim n As Integer
Dim Arr() As String
Dim Arry() As String
Dim x As Variant
Dim y As Variant
Dim varCode As Variant
Dim varCod As Variant
Dim Count As Integer
Dim Count2 As Integer
Dim VldOrdrNbrDestination As Integer
Dim LResponse As Integer
Dim sum As Integer
txtPartNumber = InputBox("Enter Part Number:")
If Not txtPartNumber = "" Then
        VldOrdrNbrDestination = 0
        If DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'") > 0 Then
            MsgBox "Part Number Found!"
    
            Count = DCount("ChildProductNbr", "dbo_ProductStructure", "[ChildProductNbr] = '" & txtPartNumber & "'")
            MsgBox Count
            Set rst = CurrentDb.OpenRecordset( _
            "Select * from dbo_ProductStructure where ChildProductNbr= '" & txtPartNumber & "'")
    
            While rst.EOF = False
                ReDim Preserve Arr(i)
                Arr(i) = rst.Fields("ParentProductNbr")
                i = i + 1
                rst.MoveNext
            Wend
            x = Arr
    
                    Dim strSQL As String
                    Dim strTable As String
                    strTable = "tblTest2"
    
            DoCmd.DeleteObject acTable, strTable
            
            For Each varCode In x
                sum = sum + 1
                
                varCode = Replace(varCode, "-", "*")
                varCode = Trim(varCode) 'trim extra spaces from varCode
                MsgBox "'*" & varCode & "*'"
                u = varCode
                MsgBox "Look for structure"
                If DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'") > 0 Then 'If what the user inputs exists in database, enter if statement
                  '  Count2 = DCount("Structure", "CalculateTotal", "[Structure] like '*" & u & "*'")
                  '  MsgBox "Number of Structures Found = " & Count2 & ""
                  '  Dim strSQL As String
                  '  Dim strTable As String
                  '  strTable = "tblTest2"
                    
                   ' DoCmd.DeleteObject acTable, strTable 'Delete the table if it exists
 
                    Set rstt = CurrentDb.OpenRecordset( _
                    "Select OrderNumber, Item, RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId", dbOpenDynaset, dbSeeChanges)
                    While rstt.EOF = False
                        ReDim Preserve Arry(n)
                        Arry(n) = rstt.Fields("RepId")
                        n = n + 1
                        rstt.MoveNext
                    Wend
                    y = Arry
                    
                    For Each varCod In y
                        If DCount("[Location ID]", "[tbl_LBP_Sales Location Num]", "[Location ID] = '" & varCod & "'") > 0 Then
    
                            If Not "[Rep Region Code]" = "INT" And Not "[Rep Region Code]" = "inte" Then
                                VldOrdrNbrDestination = VldOrdrNbrDestination + 1
                            End If
                        End If
                    Next varCod
    
              strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
              
              If sum = 1 Then
              
                strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
                CurrentDb.Execute strSQL
    
              Else
              
                strSQL = ("INSERT INTO " & strTable & " Select OrderNumber, Item, RepId from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
                CurrentDb.Execute strSQL
                
            End If
            
                Else
    
                    MsgBox "Structure Not Found"
    
                End If
    
            Next varCode
    
            MsgBox "Number of orders: '" & VldOrdrNbrDestination & "'"
    
            
            
            LResponse = MsgBox("Do you wish to view table?", vbYesNo, "")
            
            If LResponse = vbYes Then
            
                MsgBox "Chose to view table"
               
               
          '    strSQL = ("Select OrderNumber, Item, RepId INTO " & strTable & " from CalculateTotal where ([Structure] like '*" & u & "*') Group By OrderNumber, Item, RepId")
          '    CurrentDb.Execute strSQL
  
                
                
                
            Else
            
                MsgBox "Cancel table preview"
                
            End If
    
        Else
    
            MsgBox "Part Number Not Found"
        End If
        
Else
        MsgBox "Enter Part Number"
End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:03
Joined
Feb 19, 2002
Messages
30,539
I didn't examine your code in detail since you have a solution but I see that you are using multiple domain functions inside code loops. Each DCount() runs a separate query so I'm sure that there is a more efficient way to do this. Probably a totals query joined to the main query. This lets the database engine do the heavy lifting in the most optimal fashion.

In general, if I have to allow the user to build a "transaction" in pieces or over time, I bind my forms to the holding table. Then when the user tells me the transaction is complete, I validate the data which might be several dependent tables and then copy it into the main table when everything is valid.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Jan 20, 2009
Messages
12,159
For those who are seeking to extend their skills, consider substituting fabricated (AKA shaped) ADO recordsets for holding temporary data. It is a far more elegant solution than temporary tables.

ADO recordsets are held entirely in RAM (and of course the Swap file if they exceed the available RAM) so they need no cleanup or compaction.

The downside is they can't be queried like a table so any action must be coded to read, add, delete or edit individual records. One might presume this would make them slow compared to a temporary table managed by the engine but that is not the case at all. They are blazingly fast because the disk doesn't get involved.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:03
Joined
Feb 19, 2002
Messages
30,539
Except that fabricated ADO recordsets are not persistant and they require lots of code.

The Temp table I was referring to isn't actually temporary at all. Only the data is temporary. The table is permanent and rows are added and held there until an entire transaction is assembled and validated before being added to the regular tables. This technique is frequently used for processing batches of data that have some control information such as a bank deposit or for splitting a transaction into pieces as is frequently done in general ledger systems.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Jan 20, 2009
Messages
12,159
Except that fabricated ADO recordsets are not persistant and they require lots of code.

Lack of persistence is one of their key advantages. Nothing is left behind to require compaction or accidentally leave sensitive data where it shouldn't be.

Temp tables in the Front End (a bad practice in my opinion) need to be compacted. If they are linked they need to be connected.

If they are held on the backend server they add to network traffic and records from different users need to be kept apart. If they are a separate disposable local database (AKA SideEnd) they could get left behind if the application crashes. Permissions are required whereever the table is created. No permissions are required for recordsets.

The code to create the recordset is minimal. CreateObject, Append fields, Open. There is no connection object required.

Recordsets can be managed in a Collection (or Dictionary which I prefer because the index is searchable) keeping them isolated from the tabledefs collection so no code is required to pick them out from the other tables if the developer wants to manage them in loops.

New recordsets can easily be added to the collection. Nothing is is required outside the front end so new functionality is very easily added with recordsets. A significant amount of code is required to add another table and connect it.

Recordsets can be bound to a form so there is no code required for the data entry itself.

Validation code is much the same as the Temp table. The only other code is a small loop to write the records to the permanent tables.

Unfamiliarity is the main issue that stops most developers using fabricated ADO recordsets. There are minimal examples and assistance available because so few developers use them. Developers who perpetrate myths about them requiring "lots of code" don't help.;)

The Temp table I was referring to isn't actually temporary at all. Only the data is temporary.

We should come up with a better name for Temp tables.

It is a bit like TempVars which are actually more persistent than any of the variables in VBA. The Temp comes from being less persistent than values in tables but so perhaps TempVals would have been a better name.

The table is permanent and rows are added and held there until an entire transaction is assembled and validated before being added to the regular tables. This technique is frequently used for processing batches of data that have some control information such as a bank deposit or for splitting a transaction into pieces as is frequently done in general ledger systems.

All this can be done in the ADO recordset. They are ideal for anything that need not persist beyond the session. However they can also be saved to disk as an XML file and restored with single commands. It can be handy way to hold and transfer application settings.

The other place I like to use ADO recordsets is unbound Listboxes. They provide support to the listbox for filtering and reordering with very simple code that doesn't keep hitting the engine.
 

ECEstudent

Registered User.
Local time
Yesterday, 23:03
Joined
Jun 12, 2013
Messages
153
Thank you guys a lof for your advice and responses...I'm afraid I don't know how to do the ADO recordset. Any help would be much appreciated. Thanks again.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Jan 20, 2009
Messages
12,159
I wouldn't worry about the ADO technique if you already have your system working with the temp tables.

You would need to be quite familiar with working with coding of interaction with ordinary recordsets before trying to use this technique.
 

ECEstudent

Registered User.
Local time
Yesterday, 23:03
Joined
Jun 12, 2013
Messages
153
It's working exactly the way I want it to work except it is taking a very long time to complete the process and return the values that I want. I was wondering if there's a way to reduce the amount of time? My first thought was that it's only taking that long because the tables are extremely long and it has to go back and forth between access and SQL but if you have a way of reducing the time, that would be great! Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:03
Joined
Feb 19, 2002
Messages
30,539
Temp tables in the Front End (a bad practice in my opinion) need to be compacted. If they are linked they need to be connected.
I corrected my statement. The table isn't temporary. The data is temporary. It is inappropriate to create these recordsetsin memory since they may need to be saved. You don't want users to expend time entering data and then have to do it again because they couldn't finish before quitting time. Therefore, rather than have two methods, I choose one. A bound recordset.

@ECStudent - back in post #3 I pointed out that your code was using domain functions that are probably not necessary. Use a query that joins to a totals query to get the counts.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Jan 20, 2009
Messages
12,159
I corrected my statement. The table isn't temporary. The data is temporary.

I think you missed the points I was making about the disadvantages of Temp tables which I do understand are holding temporary records rather than being temporary tables as such.

Yes, the data is temporary. Those records are deleted and the database that holds them then requires compaction. That is one reason why Temp tables don't belong in the front end despite the fact that many developers do this.

If data and program are kept separate then the front end is not affected by records being written to it and deleted. Few people would write a shopping list in the margins of a reference book.

Moreover, if the Temp data is saved in the front and and a new front end pushed out overnight the user's partially completed work will be lost.

The point is that Temp tables have disadvantages wherever they are held. So long as the developer is aware of those disadvantages then that is fine.

It is inappropriate to create these recordsets in memory since they may need to be saved. You don't want users to expend time entering data and then have to do it again because they couldn't finish before quitting time.

The ADO recordset can be saved to a file with a single command.

Alternatively they can be saved to a back end table as a partly completed transaction. To use the example of an invoicing system, most already have the facility to save a quote. Good ones can flag a quote as a work in progress or a fixed posted quote.

If a system needs to save partially completed work then that facility should be incorporated. Temp tables is one way to do it but by no means the only one nor the most elegant.
 

spikepl

Eledittingent Beliped
Local time
Today, 08:03
Joined
Nov 3, 2010
Messages
6,144
The code to create the recordset is minimal. CreateObject, Append fields, Open. There is no connection object required.

@Galaxiom - so I'd urge you to cobble together a little demo in the Code Repository
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:03
Joined
Feb 19, 2002
Messages
30,539
@Galaxiom - I don't think I said to create the "work" tables in the FE. Work in process is a more appropriate term than temporary anyway. As to creating a recordset in memory - I'm sure that in some situation that might be appropriate but Access' strong point is bound forms and if a bound form works in a situation, why not use it? If there were absolutely no reason to ever save a recordset, then working with it in memory is more efficient than creating a "temp" table. In the situation I was talking about, work in process needs to be saved so why use two methods to work with the data when one method (bound form) handles all situations?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Jan 20, 2009
Messages
12,159
@Galaxiom - I don't think I said to create the "work" tables in the FE. Work in process is a more appropriate term than temporary anyway.

I realise you didn't suggest that. I was just reemphasising they should definitely not in the Front End because this is what many developers do.

I also outlined reasons why temp tables have issues whereever they are located. So long as the developer is aware of those issue that is fine.

As to creating a recordset in memory - I'm sure that in some situation that might be appropriate but Access' strong point is bound forms and if a bound form works in a situation, why not use it?

Access forms can also be bound to ADO recordsets. However Batching via disconnected recordsets doesn't work quite right on bound forms so code is required to write the records back to the table again.

However code is also required to resync temp tables back to the main table so they are not so different in that regard.

Done correctly, the ADO Batch can facilitate handling record conflicts which must be entirely managed by the update code when working with temp tables.

I suspect many developers who use Temp tables don't manage conflicts at all and hence risk other users' changes being lost.

Anyway, following spike's request I have been conceiving of a class that would provide a convenient substitute to temp tables and hope to post it to the Code Repository next week.

I will provide a link from here.
 

Users who are viewing this thread

Top Bottom