Solved

Changing Table Def Settings Using VBA

Posted on 2011-09-06
6
264 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
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 119

Accepted Solution

by:
Rey Obrero earned 500 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now