Automating a rename of a table?

[BeckyC]

Need a methodology for automating (either in VBA code or inside a macro)
renaming a data source (the problem here is that when you import a filename
with the same name as an existing data source, the imported filename gets
subscripted with a 1 - in order for the queries to run properly, we have to
rename the data sources removing the subscript and overwriting the original
data source before executing the queries) . I need to automate this for about
90 tables.

 

[Stefan Hoffmann]

hi Becky,

Need a methodology for automating (either in VBA code or inside a macro)
renaming a data source (the problem here is that when you import a filename
with the same name as an existing data source, the imported filename gets
subscripted with a 1 - in order for the queries to run properly, we have to
rename the data sources removing the subscript and overwriting the original
data source before executing the queries) . I need to automate this for about
90 tables.

Public Function TableExists(ATableName As String _
) As Boolean

Dim Count As Integer

TableExists = False
CurrentDbC.TableDefs.Refresh
For Count = 0 To CurrentDbC.TableDefs.Count - 1
If CurrentDbC.TableDefs(Count).Name = ATableName Then
TableExists = True
Exit For
End If
Next Count

End Function



mfG
--> stefan <--

 

[Douglas J. Steele]

Actually, that code checks whether a table with a given name exists, as
opposed to renaming it.

If the table already exists, you won't be able to rename the new table to
that name anyhow. You can use Stefan's function to determine, in advance,
whether the table exists, delete it if it does, and then do your import. A
better approach, however, might be to link to the new data source and then
run a query to transfer the data from the linked table to the actual table.
(or import to a temporary table and run the query)

 

[Stefan Hoffmann]

hi douglas,

Actually, that code checks whether a table with a given name exists, as
opposed to renaming it.

Yup, I've to admit it was to lazy to give an explanation.



mfG
--> stefan <--