Create a Temp Table

[whojstall11]

I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?

Private Sub Command0_Click()
 

    'OBJECTIVE: to build a table by extracting some fields from a main database

    Dim rst As Recordset
    Dim strSQL As String
    Dim strSQL2 As String
    Dim intCount As Integer
    Dim intNumFields As Integer
    
    Dim i As Integer
    Dim strRecords() As String
    Dim strFields As String
    
    Dim intStatus As Integer
    
    'A query to a "main frame computer"
      strSQL2 = " ALTER TABLE ImportFundedCardstbl MODIFY ID int(4) auto_increment;"
    strSQL = "SELECT DISTINCT ID,[Name],[Card Number],Amount,[Trans Linker],[Funding Account Balance] FROM ImportFundedCardstbl"
    
   
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
  
    'LOAD ARRAY FOR FIELD NAMES FROM THE RST
    
    'FIND THE # OF RECORDS IN THIS SET...
    rst.MoveLast
    rst.MoveFirst
    
    'Total number of records in this recordset
    intCount = rst.RecordCount - 1 '0-based
    i = 0
    
    intNumFields = rst.Fields.Count
    
    'strFields = rst.Fields(0).Name 'for 1 field name
   strFields = rst.Fields(0).Name & "," & rst.Fields(1).Name & "," & rst.Fields(2).Name & "," & rst.Fields(3).Name & "," & rst.Fields(4).Name & "," & rst.Fields(5).Name
    
    Do Until rst.EOF

        'Store the values of the query in an array...
        ReDim Preserve strRecords(i)

        'strRecords(i) = rst.Fields(0) 'for 1 field
        strRecords(i) = rst.Fields(0) & "," & rst.Fields(1) & "," & rst.Fields(2) & "," & rst.Fields(3) & "," & rst.Fields(4) & "," & rst.Fields(5)

        'Debug.Print strRecords(i)

        i = i + 1
       rst.MoveNext
    
    Loop

    'rst.Close
    Close
    Set rst = Nothing
    
    'BUILD A CUSTOM TABLE BASED ON THE FIELD NAMES IN THE ARRAY...
    
    intStatus = CreateTable(strFields, strRecords(), intNumFields, Me.txtNewTableName)

    If intStatus = True Then

        MsgBox "Table created and data entered successfully"
        
    End If

End Sub

 

[whojstall11]

Ill take it back this where I should change the data, but how to I tell access to change it. I know it goes before this For intCounter = 0 To num_fields - 1
strCreateTable = strCreateTable & "[" & strFields(intCounter) & "] varchar(150)," statement but how.

Public Function CreateTable(table_fields As String, table_data As Variant, num_fields As Integer, table_name As String) As Boolean
    Dim strCreateTable As String
    
    Dim intCount As Integer
    
    Dim strFields() As String
    Dim strValues() As String
        
    Dim strInsertSQL As String
    
    Dim intCounter As Integer
    Dim intData As Integer
    
    
    On Error GoTo errHandler
    
    'split the string on the comma delimiter
    strFields = Split(table_fields, ",")
    
    If TableExists(table_name) Then
        'DROP THE TABLE IF IT EXISTS.
        CurrentDb.Execute "DROP TABLE " & table_name
    End If
 
    'this creates the table structure...
    strCreateTable = "CREATE TABLE " & table_name & "("
    
    For intCounter = 0 To num_fields - 1
        strCreateTable = strCreateTable & "[" & strFields(intCounter) & "] varchar(150),"
    Next
  
    If Right(strCreateTable, 1) = "," Then
        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1)
        strCreateTable = strCreateTable & ")"
    End If

    CurrentDb.Execute strCreateTable
    
    intCounter = 0 'reset
    intData = 0 'reset
    
    If Err.Number = 0 Then
        
        
        For intData = 0 To UBound(table_data)
                        
            'split the row on the comma delimiter
            strValues = Split(table_data(intData), ",")
                                       
            '=======================================================================
            'now insert the values into the new table
            '=======================================================================
            strInsertSQL = "INSERT INTO " & table_name & "("
            
            For intCounter = 0 To num_fields - 1
                strInsertSQL = strInsertSQL & "[" & strFields(intCounter) & "],"
            Next
                
            If Right(strInsertSQL, 1) = "," Then
                strInsertSQL = Left(strInsertSQL, Len(strInsertSQL) - 1)
                strInsertSQL = strInsertSQL & ")"
            End If
            
            '=======================================================================
            'now enter the values...
            '=======================================================================
            strInsertSQL = strInsertSQL & " VALUES ("
            
            intCounter = 0
            
            
            For intCounter = 0 To num_fields - 1
                strInsertSQL = strInsertSQL & """" & strValues(intCounter) & ""","
                
            Next
            
            If Right(strInsertSQL, 1) = "," Then
                strInsertSQL = Left(strInsertSQL, Len(strInsertSQL) - 1)
                strInsertSQL = strInsertSQL & ")"
            End If
            
            '=======================================================================
            'insert data row...
            '=======================================================================
            Debug.Print strInsertSQL
            CurrentDb.Execute strInsertSQL
            
        Next 'next data row
                                
        CreateTable = True
    End If


    Exit Function
errHandler:
    CreateTable = False
    MsgBox Err.Number & " " & Err.Description
    
End Function

 

[namliam]

Why the *HELL* would you need an autonumber in a temp table?
Creating a temp table is *BAD* enough to start with, why compound a bad thing with a worse thing?

 

[whojstall11]

I need my users to be able to copy excel data into the Database. Then I need that data copied to generate a report that sends emails. Everything works but my database is set to have a query filter the data my user pasted in with data from a different table (it only shows data that matches both fields).

If I dont use a temp table my query will always show all data the my users have ever enter. I want them to see only the most recent data. The temp table should delete everything in its table every time it closes............ Does that my sense.

And you are right I wont need the autonumber

 

[namliam]

Yeah a temp table like this is called a "Staging" table... a table that temporarily holds external data for internal use.

So now that you dont need the auto number, does that resolve your issue(s) or do you have anything outstanding you would like assistance with?

 

[whojstall11]

I do I going to open a new thread now.