Results 1 to 6 of 6
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Alter Table Statement

    I have a table that holds the field names of a secondary table. I create a recordset to iterate the fieldnames and alter the table to set the field to be Text(40). This synatx works perfect for the 1st iteration, but the Next is hit, and the code continues on as if the table only holds one field.



    Why does this syntax not fully iterate all fields contained in the table?

    Code:
    Public Function AlterFieldType()
    	Dim fld As DAO.Field
    	Dim StrSQL As String,secondSQL As String
    	Set db = CurrentDb()
    
    
                StrSQL = "select fieldname from needstobetext"
                
                Set rs3 = db.OpenRecordset(StrSQL)
                
                For Each fld In rs3.Fields
                
                    secondSQL = "ALTER TABLE prod ALTER COLUMN [" & fld.Value & "] TEXT(40);"
                    DoCmd.RunSQL secondSQL
                    
                Next
                
                Set fld = Nothing
                rs3.Close
    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yu shouldn't need to alter field lengths. Once set for your data.
    Just set them for max 255.

    and you don't alter field lengths with a query, you alter the field property:
    Like: fld.length=40

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    I am importing an Excel spreadsheet through VBA and field types are being set to number when I need them to be text.

    This looked like the easiest method to remedy, instead of having to manually map columns in excel to columns in a table in access etc. just alter the table after creation.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does your excel have the same format each time?
    If so, you might want to write a small vba routine to Create table( with your fields and lengths) before you do the actul import of the excel data.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by orange View Post
    Does your excel have the same format each time?
    If so, you might want to write a small vba routine to Create table( with your fields and lengths) before you do the actul import of the excel data.
    Basic structure is always the same, the amount of columns varies greatly depending from time to time.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Basic structure is always the same, the amount of columns varies greatly depending from time to time.
    ???
    Do you mean Rows, not columns?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ALTER TABLE rename a column
    By rockell333 in forum Queries
    Replies: 8
    Last Post: 09-18-2015, 02:59 PM
  2. Alter Table, YES/NO field
    By gmaster in forum Queries
    Replies: 1
    Last Post: 10-29-2014, 03:25 AM
  3. If statement to alter text box value
    By eskybel in forum Reports
    Replies: 13
    Last Post: 08-05-2013, 06:17 PM
  4. Trying to get an Alter table to work
    By itm in forum Programming
    Replies: 5
    Last Post: 07-31-2012, 06:32 AM
  5. Alter table Yes/No field
    By Cojack in forum Queries
    Replies: 7
    Last Post: 10-27-2010, 11:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums