• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Changing Table Def Settings Using VBA

Hello,

I wrote an import script that imports excel files.  Then it goes through and makes sure that all of the expected fields are present.  After doing so, it checks to make sure the fields have the right properties.  (i.e. Size, format, datatype, etc.)  If not, I want it to change the setting to the correct one.  This was my idea, below:
                    With db.TableDefs(str_Table)
                        For i = 0 To .Fields.Count - 1
                            
                            If .Fields(i).Name = str_SourceFLD Then
                                str_MatchSWX = "1"
                                If .Fields(i).Type = int_SourceDT Then
                                    str_MatchSWX = str_MatchSWX & "1"
                                Else
                                    str_MatchSWX = str_MatchSWX & "0"
                                    .Fields(i).Type = int_SourceDT
                                End If
                                
                            End If
                                        
                        Next
                    End With

Open in new window


Is this possible?

Wendee
0
pwdells
Asked:
pwdells
1 Solution
 
Jeffrey CoachmanCommented:
How are you defining: "the correct one"?

can you post the entire code?
0
 
pteranodon72Commented:
pwdells,
Once a field is added to a tabledef, its properties become read-only. The code above will work except for line 10, which tries to change the field's type after it has been added to the tabledef.

You can't change the properties of a field that is already added to the tabledef. You would have to
 - create a new (empty) field of the proper type
 - add the field to the tabledef
 - copy the data from the old field (which is problematic because the field was given the wrong type!) or re-import from Excel

If you have all the field names, datatypes, and field sizes of the target table ahead of time, perhaps you can:

1) create the (empty) target table in Access (tblTarget)
2) create a linked table to the Excel data (lnkExcel)
3) run an append query that add the lnkExcel to tblTarget

HTH,
pT72
0
 
Rey Obrero (Capricorn1)Commented:
you can change the field type using

currentdb.execute "alter table tableName alter column columnname text"

that will change the column data type to Text
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
pwdellsAuthor Commented:
db.Execute ("DELETE * FROM TMP_IMPORT")

'Get Tables to Import |  Append to TMP_IMPORT
str_Selected = _
    "INSERT INTO TMP_IMPORT ( [TableID], blnComplete, StatusID ) " & _
    "SELECT tbx_Tables.ID, 0 AS Complete, 0 AS Status " & _
    "FROM tbx_Tables " & _
    "WHERE tbx_Tables.Selected = '*'"
db.Execute (str_Selected)

'Pull info from TMP_IMPORT for display in lstSelected
str_Selected = _
    "SELECT DISTINCT tbx_Tables.TBX_Name, tbx_ImportStatus.StatusDesc " & _
    "FROM tbx_ImportStatus " & _
        "INNER JOIN (TMP_IMPORT " & _
            "INNER JOIN tbx_Tables " & _
                    "ON TMP_IMPORT.TableID = tbx_Tables.ID) " & _
                "ON tbx_ImportStatus.StatusID = TMP_IMPORT.StatusID "
    
Set rs_Selected = db.OpenRecordset(str_Selected)

Me.lstSelected.AddItem ("Table Name; Status; Error")

'Generate List
While Not (rs_Selected.EOF)

    Me.lstSelected.AddItem (rs_Selected.Fields(0) & ";" & rs_Selected.Fields(1) & "; -")
    rs_Selected.MoveNext

Wend

'Get a list count.
If rs_Selected.RecordCount > 0 Then
    rs_Selected.MoveLast
    int_Table = rs_Selected.RecordCount
    rs_Selected.MoveFirst

    Debug.Print "Table Count: " & int_Table
    Debug.Print "***************"
    
    While Not (rs_Selected.EOF)
        
        str_Table = rs_Selected.Fields(0)
        Debug.Print str_Table
        Debug.Print " -- Processing " & str_Table & "..."
        
        str_getTable = _
            "SELECT tbx_Tables.ID, tbx_Tables.TBX_Name, tbx_Tables.PK " & _
            "FROM tbx_Tables " & _
            "WHERE tbx_Tables.TBX_Name ='" & str_Table & "' " & _
                " AND tbx_Tables.Type = 4"
        Set rs_getTable = db.OpenRecordset(str_getTable)
        
        int_TableID = rs_getTable.Fields(0)
        fld_PK = rs_getTable.Fields(2)
        
        db.Execute ("UPDATE TMP_IMPORT SET StatusID =  1 WHERE [TableID] = " & int_TableID)
        Call Reload_Selected_Tables
        
        Debug.Print " -- Table Found: " & int_TableID & " | " & rs_getTable.Fields(1)
        
        str_getFields = _
            "SELECT tbx_Fields.Field, tbx_Fields.DataType, tbx_Fields.Format, tbx_Fields.Size " & _
            "FROM tbx_Fields " & _
            "WHERE tbx_Fields.Active = True " & _
                "AND tbx_Fields.TableID = " & int_TableID
                
        Set rs_getFields = db.OpenRecordset(str_getFields)
        
        rs_getFields.MoveLast
        fld_SourceCT = rs_getFields.RecordCount
        rs_getFields.MoveFirst
        
        Debug.Print " -- Fields:"
        
        While Not (rs_getFields.EOF)
            Debug.Print "    " & rs_getFields.Fields(0) & "  |  " & rs_getFields.Fields(1) & _
                "  |  " & rs_getFields.Fields(2) & "  |  " & rs_getFields.Fields(3)
            rs_getFields.MoveNext
            
        Wend
        
        Debug.Print " "
        
        'Get Raw Data Storage Information
        str_getRDS = _
            "SELECT tbx_Tables.ID, [FilePath] & '\' & [ExcelFileName] AS ImportPath " & _
            "FROM tbx_Tables " & _
            "WHERE tbx_Tables.ID = " & int_TableID
        Set rs_getRDS = db.OpenRecordset(str_getRDS)
        
        Debug.Print " -- IMPORTING"
        str_RDS = rs_getRDS.Fields(1)
        Debug.Print "    * Raw Data Location: " & str_RDS
        
        'check to see if this table exists
        str_chkTable = _
            "SELECT * " & _
            "FROM MSysObjects " & _
            "WHERE Name = '" & str_Table & "'"
            
        Set rs_chkTable = db.OpenRecordset(str_chkTable)
                        
        If rs_chkTable.RecordCount > 0 Then
            'yes - drop it
            db.Execute ("DROP TABLE " & str_Table)
        End If
        
        'import the table
        Debug.Print "    * Importing " & str_Table & " from " & str_RDS & "..."
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, str_Table, str_RDS, True
        Debug.Print "    * " & str_Table & " - Complete "
        Debug.Print " "
        
        db.Execute ("UPDATE TMP_IMPORT SET StatusID =  2 WHERE [TableID] = " & int_TableID)
        Call Reload_Selected_Tables
        
        'analyze the fields that were imported
            'how many fields were imported?
            Debug.Print " -- ANALYZING"
            Debug.Print "    * Source Field Count: " & fld_SourceCT
            Debug.Print "    * Subject Field Count: " & db.TableDefs(str_Table).Fields.Count
            Debug.Print " "
            
            fld_SubjectCT = db.TableDefs(str_Table).Fields.Count
            
            If fld_SourceCT > fld_SubjectCT Then
                Debug.Print "    ERROR! Fields in file to be imported!"
            Else
                Debug.Print "-- MATCHING FIELDS"
                rs_getFields.MoveFirst
                While Not (rs_getFields.EOF)
                    str_SourceFLD = rs_getFields.Fields(0)
                    int_SourceDT = rs_getFields.Fields(1)
                    str_SourceFMT = rs_getFields.Fields(2)
                    int_SourceSZ = rs_getFields.Fields(3)
                    Debug.Print "    FIELD:" & rs_getFields.Fields(0)
                    str_MatchSWX = "FAIL"
                    With db.TableDefs(str_Table)
                        For i = 0 To .Fields.Count - 1
                            
                            If .Fields(i).Name = str_SourceFLD Then
                                str_MatchSWX = "1"
                                If .Fields(i).Type = int_SourceDT Then
                                    str_MatchSWX = str_MatchSWX & "1"
                                Else
                                    str_MatchSWX = str_MatchSWX & "0"
                                    '.Fields(i).Type = int_SourceDT
                                End If
                                
                                If .Fields(i).Properties("Format") = str_SourceFMT Then
                                    str_MatchSWX = str_MatchSWX & "1"
                                Else
                                    str_MatchSWX = str_MatchSWX & "0"
                                    '.Fields(i).Properties("Format") = str_SourceFMT
                                End If
                                
                                If .Fields(i).Properties(6) = int_SourceSZ Then
                                    str_MatchSWX = str_MatchSWX & "1"
                                Else
                                    str_MatchSWX = str_MatchSWX & "0"
                                    '.Fields(i).Properties(6) = int_SourceSZ
                                End If
                            End If
                                        
                        Next
                    End With
                    Debug.Print "    SWX: " & str_MatchSWX
                    Debug.Print "    -------------------"
                    Debug.Print " "
                    str_SubjectFLD = ""
                    rs_getFields.MoveNext
                Wend
            End If
            'how many records were imported?
            str_RecCt = _
                "SELECT Count(" & str_Table & "." & fld_PK & ") AS CountOfID " & _
                "FROM " & str_Table
            Set rs_RecCt = db.OpenRecordset(str_RecCt)
            int_RecCt = rs_RecCt.Fields(0)
            Debug.Print "Record Count for " & str_Table & ": " & int_RecCt
            
        rs_Selected.MoveNext
    Wend
    
    Debug.Print "done"
    
Else
    'No tables were selected.
    ErrorCode = " ('TAB1-001', 'Null Recordset', 'No Recordset found during field matching process.')"
    ErrorCode = "INSERT INTO tbx_DEBUG ( ErrorCode, Error, Description ) VALUES " & ErrorCode
    CurrentDb.Execute (ErrorCode)
    
End If

Open in new window

0
 
pwdellsAuthor Commented:
I have two tables that I call source tables.  One table has the table info in it:

Table
Name
Primary Key Name
Subject File
Subject File Path

The other has the field info in it:
Fields
Table ID Foreign Key
Name
Data Type
Format
Size
SAP Name
0
 
pwdellsAuthor Commented:
What I ended up with:

str_Alter = "ALTER TABLE " & str_Table & " ALTER COLUMN " & str_SourceFLD & " Text"

Debug.Print str_Alter

db.Execute (str_Alter)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now