Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    field names with spaces (XP-professional)

    Is there a quick and easy way to loop through all the field names in a table and replace the spaces with _underscores (eg. [Client Name] to Client_Name)? We are moving some old access tables to SQL Server 2000 and need to clean up the table structures. I am about to write a procedure that loops through all the tables in CurrentDB one at a time and then loop through all the fields in each table and basically search for spaces and replace them with underscores. Is there an easier way that anyone knows?
    thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: field names with spaces (XP-professional)

    No, that would be the way to do it. Watch out for system tables!

    Sub CorrectNames()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "MSys" And tdf.Connect = "" Then
    For Each fld In tdf.Fields
    If InStr(fld.Name, " ") > 0 Then
    fld.Name = Replace(fld.Name, " ", "_")
    End If
    Next fld
    End If
    Next tdf

    ExitHandler:
    On Error Resume Next
    Set fld = Nothing
    Set tdf = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You need a reference to the Microsoft DAO 3.6 Object Library for this code to run.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: field names with spaces (XP-professional)

    You might try the upsizing wizard on a few Access tables. The version for Access 2002 does a pretty decent job in general, though the end result will depend on the design issues associated with your Access tables.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: field names with spaces (XP-professional)

    Wendell,
    Thanks for the tip. I did try the upsizing wizard and SQL put [square brackets] around the field names instead of _underscores_ . I don't recall any options in the upsizing wizard that would allow you to customize how Access handles field names. I was told however, that using DTS and pulling data into SQL instead of pushing data out of access might be an option. If anyone has any knowledge on this, let me know.

    Thanks,

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: field names with spaces (XP-professional)

    Thanks Hans. This is exactly what I had in mind. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: field names with spaces (XP-professional)

    We do also use the DTS from SQL Server at times to pull in an Access table when we want only a subset of the records. I don't recall for certain, but I think it also applies square brackets around the name as well - note that Access does that in some cases when it thinks you intend to use a table name.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •