tablename and fieldformat

[Tommy7571]

Hello,
I have two problems I want to rename all tables of a database because if I
import these tables they get the name public + 'realname' so I don't want to
keep public.

Sub Renamingtables()
Dim i, j, k As Integer
Dim s, newfile, searchstring, SearchChar As String
Dim tbl As TableDef, dbs As Object
Dim oldName, newname As String

Set dbs = CurrentDb.TableDefs

For Each tbl In dbs

oldName = dbs.Name
DoCmd.SelectObject acTable, oldName, True
SearchChar = "public_" ' Search for "Public".
searchstring = oldName

MyPos = InStr(1, searchstring, SearchChar, 1)
If MyPos <> 0 Then
DoCmd.Rename oldName, acTable, newname
End If

Next tbl

End Sub

This was a trial but it doesn't work although I tried many ways...
Can somebody help me ?

Secondly everytime I import tables from the server the fieldformat for all
fields with not integer numbers is 'decimal' but I have so much problems with
this format that I prefere double or Single. I tried this under options but
it doesn't change anything. How to change all fieldformats which are numeric
from decimal to double ?

Thanks very much

Thomas Willms

 

[Douglas J. Steele]

Sub Renamingtables()
Dim tbl As DAO.TableDef, dbs As DAO.Database
Dim oldName As String, newname As String
Dim MyPos As Long

Set dbs = CurrentDb.TableDefs
For Each tbl In dbs.TableDefse
MyPos = InStr(1, tbl.Name, "public_, 1)
If MyPos <> 0 Then
tbl.Name = newname
End If
Next tbl

End Sub

Of course, you're not providing a value for newname anywhere...

I'd like to point something out in your code, though.

Dim i, j, k As Integer
Dim s, newfile, searchstring, SearchChar As String
Dim tbl As TableDef, dbs As Object
Dim oldName, newname As String

doesn't do what you probably think it does. You can't "short circuit"
declarations: that first line declares k to be an integer, but i and j are
declared as variants. You probably want

Dim i As Integer, j As Integer, k As Integer
Dim s As String, newfile As String, searchstring As String, SearchChar As
String
Dim tbl As TableDef, dbs As Database
Dim oldName As String, newname As String

For the second problem, I'm not sure what you mean by "tried this under
options but it doesn't change anything.". What does "under options" mean?
Are these linked tables? If so, you have to change them in the database
where they actually exist.

 

[John Spencer]

A modification to Douglas Steele's code

Sub Renamingtables()
Dim tbl As DAO.TableDef, dbs As DAO.Database
Dim MyPos As Long

Set dbs = CurrentDb.TableDefs
For Each tbl In dbs.TableDefse
MyPos = InStr(1, tbl.Name, "public_", 1) '<<< Missed a quote
If MyPos = 1 Then
tbl.Name = Mid(tbl.Name,8) 'Strip off Public_
End If
Next tbl

End Sub

You might try the following to modify the field type. WARNING Backup your
database (or make a temp copy of the table) BEFORE you do this.

Sub fixFieldTypes(strTableName As String)
Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef
Dim fldAny As DAO.Field
Dim strSQL As String

Set dbAny = CurrentDb()
Set tblAny = dbAny.TableDefs(strTableName)

For Each fldAny In tblAny.Fields
If fldAny.Type = dbDecimal Then

strSQL = "ALTER TABLE " & tblAny.Name & _
" ALTER COLUMN " & fldAny.Name & " Double"
dbAny.Execute strSQL, dbFailOnError
End If 'Field Type
Next fldAny

End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..