Solved

Changing Table Def Settings Using VBA

Posted on 2011-09-06
6
252 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
Comment Utility
How are you defining: "the correct one"?

can you post the entire code?
0
 
LVL 14

Expert Comment

by:pteranodon72
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pwdells
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

11 Experts available now in Live!

Get 1:1 Help Now