Results 1 to 15 of 15
  1. #1
    dwall2 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    3

    How to remove spaces from all tables within a Database

    Hi all,



    I am a complete beginner with access. I am an engineer and computer models we work with can be opened in access, doing so presents approximately 100 or so tables with all the information contained in the model within it.

    When I try to convert a model to another file type the converter needs there to be no spaces in strings within the original model. Currently, how I remedy this is, I will open the model in access and then go through each table and hit find and replace to replace any " " with "_". Its a painstaking process that can take ages.

    Is there a way of running a find and replace on every table contained within the document, rather than clicking through each one and doing it manually.

    Thanks,

    Dan

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I assume these tables aren't used anywhere else at the point of doing this?

    If that's correct then something like

    Code:
    Public Sub RemoveTblSpaces()
        Dim tbl As TableDef
        For Each tbl In CurrentDb.TableDefs
            If Len(tbl.Name) > 0 Then
                tbl.Name = Replace(tbl.Name, " ", "")
            End If
        Next
        Set tbl = Nothing
    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Note that will break all your existing queries and forms

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to current responses, I suggest you provide a few examples. As ranman says, you could break much of existing code.
    But readers need a little more context and sample data to offer more focused responses.
    Bottom line: Be cautious - don't act too quickly without some analysis --AND a Backup!

    Is it really Table names or table and field names??

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is it just the table name or the fields as well? If the latter then modify minty's code to

    Code:
    Public Sub RemoveTblSpaces()
        Dim tbl As TableDef
        dim fld as Field
        For Each tbl In CurrentDb.TableDefs
        If Len(tbl.Name) > 0 Then
            tbl.Name = Replace(tbl.Name, " ", "_")
                for each fld in tbl.fields
                   fld.name=replace(fld.name," ","_")  
                next fld
       End If
        Next tbl
        Set tbl = Nothing
    End Sub
    but as ranman says, you'll need to manually adjust queries, code, forms and reports

    You can create more looping code similar to the above to modify querydef sql and open forms/reports in design view and then loop through all the possible places where table and field names occur (recordsources, rowsources, controlsources) and of course modules. If you are using macros, pretty sure that will have to be a manual operation. It might be worth looking online, there may be a free or low cost tool you can download that will get you going

    As an aside, having spaces in table and field names and form and report control names (in fact any object) is not a good idea anyway and should be dropped as a practice.

  6. #6
    dwall2 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    3
    Just to clarify it is not the table name where the spaces need removes, its the strings contained within each table which need to have no spaces

  7. #7
    dwall2 is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    3
    Yes sorry I realise this might not be clear at all. As I mentioned early I am a complete beginner so really have no idea how to use Access. It just so happens that I can open models which are created in another software in access.

    Hopefully the screenshot will clear this up. For example the entry "M&S - Cas Don" needs to be replaced with "M&S_-_Cas_Don" in order for our converter to work. The way I am doing this current is to double click each table on the left then press control f to find and replace all instances of spaces with an underscore within the table.

    Hope this clears things up. Thanks

    Click image for larger version. 

Name:	Access.png 
Views:	26 
Size:	265.1 KB 
ID:	47053Click image for larger version. 

Name:	Access.png 
Views:	26 
Size:	265.1 KB 
ID:	47053

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Ah okay - so you are updating the field contents to remove the spaces.
    IGNORE AND DELETE my previous example.

    More questions I'm afraid if you want a sensible solution;
    Is this a one off exercise and is it every field in every table or just specific ones?

    Edit For clarity - TABLE - hold a number of fields. FIELDS - Hold the actual data.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    IGNORE AND DELETE my previous example.
    and mine

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is a version of the code adapted from Ajax:
    Code:
    Public Sub Remove_Spaces_in_All_Table_Data()
        Dim tbl As DAO.TableDef,db as DAO.Database
        dim fld as DAO.Field,sField as String
    
    
    Set db=CurrentDb
        
    For Each tbl In CurrentDb.TableDefs
      If Len(tbl.Name) > 0 Then        
         for each fld in tbl.fields
             sField=fld.Name
    	db.Execute "UPDATE [" & tbl.Name & "] SET [" & sField & "] = replace([" & sField & "]," ","_")",dbFailOnError  
            'fld.name=replace(fld.name," ","_")  
         next fld
       End If
    Next tbl
    
    
    Set tbl = Nothing
    Set db=Nothing
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Should avoid looping over system tables? I have a recordset version almost ready but I suppose the sql method would be more compact. Have to go to an appointment now anyway.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're right, here is an updated version to do that:
    Code:
    Public Sub Remove_Spaces_in_All_Table_Data()
        Dim tbl As DAO.TableDef,db as DAO.Database
        dim fld as DAO.Field,sField as String
    
    
    Set db=CurrentDb
        
    For Each tbl In CurrentDb.TableDefs
      If Len(tbl.Name) > 0 And Left(tbl.Name,4)<>"msys" Then        
         for each fld in tbl.fields
             sField=fld.Name
    	db.Execute "UPDATE [" & tbl.Name & "] SET [" & sField & "] = replace([" & sField & "]," ","_")",dbFailOnError  
            'fld.name=replace(fld.name," ","_")  
         next fld
       End If
    Next tbl
    
    
    Set tbl = Nothing
    Set db=Nothing
    
    
    End Sub
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I started with Vlad's approach, but the db.execute update...... was a compile error from the start.

    So I worked with a sample table AIRCRAFTRaw that I had. To get the field values I used a recordset. Replacing text field values with "space to underscore" was successful with this adjusted code. I'm sure there are other methods to achieve same.

    Code:
    Public Sub Remove_Spaces_in_All_Table_Data()
              Dim tbl As DAO.TableDef, db As DAO.Database
              Dim fld As DAO.Field, sField As String
              Dim rs As DAO.Recordset
              Dim ifld As Integer
    10        Set db = CurrentDb
              
    20        For Each tbl In CurrentDb.TableDefs
    30            If Len(tbl.name) > 0 And Left(tbl.name, 4) <> "msys" And tbl.name = "AircraftRaw" Then
    40                Set rs = db.OpenRecordset(tbl.name)
    50                Do While Not rs.EOF
           
    60                    For ifld = 0 To rs.Fields.Count - 1
    70                        Debug.Print rs.Fields(ifld).name & "   " & rs.Fields(ifld).value
    80                        sField = rs.Fields(ifld).name
    
                      'only want to process text datatype where value is not null/empty string
    90                        If rs.Fields(ifld).Type = 10 And Not rs.Fields(ifld).value = "" Then
    100                           rs.Edit
    110                           Debug.Print "update " & tbl.name & " SET " & rs.Fields(ifld).name & " = '" & Replace(rs.Fields(ifld), " ", "_") & "'"
    120                           rs.Fields(ifld) = Replace(rs.Fields(ifld).value, " ", "_")
    130                           rs.Update
    140                       Else
    150                       End If
    160                   Next ifld
    170                   rs.MoveNext
    180               Loop
    190           End If
    200       Next tbl
    
    
    210       Set tbl = Nothing
    220       Set db = Nothing
    
    
    End Sub
    Table initially:

    Rec Date ID fld

    2 Aircraft N3456

    3
    12-Sep-12 4 456723-101 FD34566
    14-Sep-15 5 566788-303 GS23445

    6 This is a sample

    7 Aircraft N5684

    8
    06-Sep-13 9 566788-303 GS23445
    21-Sep-12 10 456723-101 GD23478
    29-Sep-13 11 566788-303 GS45444
    20-Sep-09 12 434545-444 JI345345

    Table final:

    Rec Date ID fld

    2 Aircraft_N3456

    3
    12-Sep-12 4 456723-101_FD34566
    14-Sep-15 5 566788-303_GS23445

    6 This_is_a_sample

    7 Aircraft_N5684

    8
    06-Sep-13 9 566788-303_GS23445
    21-Sep-12 10 456723-101_GD23478
    29-Sep-13 11 566788-303_GS45444
    20-Sep-09 12 434545-444_JI345345

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, that's what happens when you write things in Notepad, here is a properly tested version:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Sub Remove_Spaces_in_All_Table_Data()
        Dim tbl As DAO.TableDef, db As DAO.Database
        Dim fld As DAO.Field, sField As String
        Dim sSQL As String
    
    
    Set db = CurrentDb
    On Error Resume Next
    
    
    For Each tbl In CurrentDb.TableDefs
      If Len(tbl.Name) > 0 And Left(tbl.Name, 4) <> "msys" Then
         For Each fld In tbl.Fields
            sField = fld.Name
            sSQL = "UPDATE [" & tbl.Name & "] SET [" & sField & "] = Replace([" & sField & "],Chr(32),Chr(95));"
            Debug.Print sSQL
            db.Execute sSQL
         Next fld
       End If
    Next tbl
    
    
    
    
    Set tbl = Nothing
    Set db = Nothing
    
    
    MsgBox "All tables have been processed"
    
    
    End Sub
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 2
    Last Post: 04-26-2020, 02:01 AM
  2. Replies: 3
    Last Post: 03-05-2019, 07:13 AM
  3. Replies: 4
    Last Post: 09-13-2018, 10:21 AM
  4. How to remove extra spaces from a field?
    By accessmatt in forum Queries
    Replies: 1
    Last Post: 09-22-2014, 06:57 AM
  5. Remove blank spaces after strings in fields
    By Modify_inc in forum Access
    Replies: 8
    Last Post: 08-18-2012, 06:30 PM

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