?
Solved

Changing Table Def Settings Using VBA

Posted on 2011-09-06
6
Medium Priority
?
310 Views
Last Modified: 2012-05-12
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
Comment
Question by:pwdells
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489040
How are you defining: "the correct one"?

can you post the entire code?
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 36489049
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36489191
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:pwdells
ID: 36489214
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
 

Author Comment

by:pwdells
ID: 36489259
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
 

Author Closing Comment

by:pwdells
ID: 36505732
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question