Solved

Changing Table Def Settings Using VBA

Posted on 2011-09-06
6
291 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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