VBA and access

[osbel]

I have an access database with 20+ tables each with 20+ fields. I need a way to change the field names with spaces to have "_" instead. Does anyone have a way to do this or something to help me on my way? Thanks.

 

[Databaseguy]

You'll be using the tableDef and Field objects, and the instr function.

Dim dbs as database
Dim tdef as tabledef
Dim x as integer
Dim y as integer
Set dbs = currentdb()
For x = 0 to dbs.tabledefs.count-1
set tdef = dbs.tabledefs(x)
For y = 0 to tdef.fields.count
With tdef
if instr("_",.fields.name) then
.fields(i).name = 'code here to strip & rename field using another loop, mid, left etc
Next y
Set tdef = nothing
Next tdef
set dbs = nothing

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[osbel]

Thanks for the help. When I go to run the code it says that the database is a user-defined type and is not defined. How do I remedy this?

 

[Databaseguy]

OK, you're using 2000 then.

In the VBE, go to tools, references and select DAO 3.6. Use the arrows to move it to near the top.(It probabaly won't go all the way)

Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[osbel]

thanks, this is the code i ended up with. Works great

Public Sub ChangeNames()
Dim x As Integer
Dim y As Integer
Dim space As Integer
Dim count As Integer
Set dbs = CurrentDb()
Dim test As String
For x = 0 To dbs.tabledefs.count - 1
If InStr(dbs.tabledefs(x).Name, "MS&quot = 0 Then
Set tdef = dbs.tabledefs(x)
For y = 0 To tdef.Fields.count - 1
tdef.Fields.Name = Replace(tdef.Fields.Name, " ", "_&quot
Next y
End If
Next x
Set tdef = Nothing
Set dbs = Nothing
End Sub

 

[Databaseguy]

Ahhhh, VBs got a replace function. Very Cool. We Access Guys gotta loop through the string one character at a time...AND, we don't have control arrays. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy