Spaces in fields names

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

The below codes appends tables that all have the word
attendances in the name, but the fields it appends to
the target table have spaces in the names, since there
are lots of tables I don't want to go into design view to
change them. How do I amend the code to cope with spaces
in field names.

Sub Append_Tables()
TargetTable = "ALLData"
For Each tbl In CurrentDb.TableDefs
If Right(tbl.Name, 11) = "Attendances" Then
sourcetable = tbl.Name
strsql = "INSERT INTO " & TargetTable _
& " SELECT provider,arrival date, tel No._
& " FROM [" & sourcetable & "]"
'& " WHERE ep = 1"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

End If
Next
End Sub
 
Daniel said:
The below codes appends tables that all have the word
attendances in the name, but the fields it appends to
the target table have spaces in the names, since there
are lots of tables I don't want to go into design view to
change them. How do I amend the code to cope with spaces
in field names.

Sub Append_Tables()
TargetTable = "ALLData"
For Each tbl In CurrentDb.TableDefs
If Right(tbl.Name, 11) = "Attendances" Then
sourcetable = tbl.Name
strsql = "INSERT INTO " & TargetTable _
& " SELECT provider,arrival date, tel No._
& " FROM [" & sourcetable & "]"
'& " WHERE ep = 1"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

End If
Next
End Sub
Click to expand...

Square brackety things will do the job for you e.g. [arrival date]
 
How do I amend the code to cope with spaces
in field names.
Click to expand...

Use square brackets:

Sub Append_Tables()
TargetTable = "ALLData"
For Each tbl In CurrentDb.TableDefs
If Right(tbl.Name, 11) = "Attendances" Then
sourcetable = tbl.Name
strsql = "INSERT INTO [" & TargetTable _
& "] SELECT provider,arrival date, tel No._
& " FROM [" & sourcetable & "]"

BUT... if you have multiple tables all containing the same
type of data, you REALLY should investigate your database
design! Storing data redundantly is almost NEVER
necessary. If you're assuming that you must extract a
subset of the data from ALLData into a new table in order
to generate reports, etc., reconsider that assumption; it
is *routine* to base Reports, Exports, Forms, etc. etc. on
a Select query without the overhead of a MakeTable query.

John W. Vinson/MVP
 
Back
Top