Spaces in fields names

[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

 

[Brian]

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

Square brackety things will do the job for you e.g. [arrival date]

 

[John W. Vinson/MVP]

How do I amend the code to cope with spaces
in field names.

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