field names with spaces (XP-professional)

Topic

New Reply

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,

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans. This is exactly what I had in mind.

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote