What is wrong with this code???

[SenFan]

Hi everyone. I am struggling with this code and could use some help from an
expert.

To start, I will explain my goal. I have a database that has a number of
tables that have all the same fields. Some of these tables will be deleted
and new tables will be added (with the exact same fields). I am trying to
create a query that will run through all of the tables in the Tabledefs
(other than the system tables) and copy the records to one table, whose name
(datatemp) will be constant. I really need the flexibility to have this
query run through any table added to the database.

Another feature I would like to add is that I would also like to update a
field with the Table's name before sending the data into the constant table.
Hope this makes sense.

Here is my code so far...

Dim dbs As DAO.Database
Dim tbl As DAO.TableDefs
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
DoCmd.RunSQL "INSERT INTO Datatemp SELECT * FROM" & " " & x & ";"
'This doesn't work - It does not recognize the value of x
'How do I update a field called "Categery" with the table.name
value
'How do I send that value to my constant table Datatemp??
End If
Next t

If anyone could help I would be grateful. Thanks.

 

[Duane Hookom]

I would use the variable sqlstr you created and add []s. Also, try
debug.print to find out what the strsql looks like.

Dim dbs As DAO.Database
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
sqlstr = "INSERT INTO Datatemp SELECT * FROM [" & x & "]"
debug.Print sqlrtr
DoCmd.RunSQL sqlstr 'This doesn't work - It does not
recognize the value of x
'How do I update a field called "Categery" with the table.name
value
'How do I send that value to my constant table Datatemp??
End If
Next t

 

[SenFan]

Thanks Duane, this worked great (I was so close).

Do you know how I would update a field named Category in the datatemp table
with the value of x? I would need to do this for each table in the loop.

Thanks again.

I would use the variable sqlstr you created and add []s. Also, try
debug.print to find out what the strsql looks like.

Dim dbs As DAO.Database
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
sqlstr = "INSERT INTO Datatemp SELECT * FROM [" & x & "]"
debug.Print sqlrtr
DoCmd.RunSQL sqlstr 'This doesn't work - It does not
recognize the value of x
'How do I update a field called "Categery" with the table.name
value
'How do I send that value to my constant table Datatemp??
End If
Next t
--
Duane Hookom
MS Access MVP

Hi everyone. I am struggling with this code and could use some help

from

an
expert.

To start, I will explain my goal. I have a database that has a number of
tables that have all the same fields. Some of these tables will be deleted
and new tables will be added (with the exact same fields). I am trying to
create a query that will run through all of the tables in the Tabledefs
(other than the system tables) and copy the records to one table, whose name
(datatemp) will be constant. I really need the flexibility to have this
query run through any table added to the database.

Another feature I would like to add is that I would also like to update a
field with the Table's name before sending the data into the constant table.
Hope this makes sense.

Here is my code so far...

Dim dbs As DAO.Database
Dim tbl As DAO.TableDefs
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
DoCmd.RunSQL "INSERT INTO Datatemp SELECT * FROM" & " " & x & ";"
'This doesn't work - It does not recognize the value of x
'How do I update a field called "Categery" with the table.name
value
'How do I send that value to my constant table Datatemp??
End If
Next t

If anyone could help I would be grateful. Thanks.

 

[Duane Hookom]

run this sql statement
"UPDATE DataTemp SET Category = """ & x & """"

--
Duane Hookom
MS Access MVP

Thanks Duane, this worked great (I was so close).

Do you know how I would update a field named Category in the datatemp table
with the value of x? I would need to do this for each table in the loop.

Thanks again.

I would use the variable sqlstr you created and add []s. Also, try
debug.print to find out what the strsql looks like.

Dim dbs As DAO.Database
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
sqlstr = "INSERT INTO Datatemp SELECT * FROM [" & x & "]"
debug.Print sqlrtr
DoCmd.RunSQL sqlstr 'This doesn't work - It does not
recognize the value of x
'How do I update a field called "Categery" with the table.name
value
'How do I send that value to my constant table Datatemp??
End If
Next t
--
Duane Hookom
MS Access MVP

Hi everyone. I am struggling with this code and could use some help

from

an
expert.

To start, I will explain my goal. I have a database that has a number of
tables that have all the same fields. Some of these tables will be deleted
and new tables will be added (with the exact same fields). I am

trying

to whose
name

update

a

 

[SenFan]

Thanks for your help Duane. Everything worked like a charm. You saved me a
lot of time and stress and I learned some great stuff thanks to you.

Cheers.

run this sql statement
"UPDATE DataTemp SET Category = """ & x & """"

--
Duane Hookom
MS Access MVP

Thanks Duane, this worked great (I was so close).

Do you know how I would update a field named Category in the datatemp table
with the value of x? I would need to do this for each table in the loop.

Thanks again.

I would use the variable sqlstr you created and add []s. Also, try
debug.print to find out what the strsql looks like.

Dim dbs As DAO.Database
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
sqlstr = "INSERT INTO Datatemp SELECT * FROM [" & x & "]"
debug.Print sqlrtr
DoCmd.RunSQL sqlstr 'This doesn't work - It does not
recognize the value of x
'How do I update a field called "Categery" with the table.name
value
'How do I send that value to my constant table Datatemp??
End If
Next t
--
Duane Hookom
MS Access MVP


Hi everyone. I am struggling with this code and could use some help from
an
expert.

To start, I will explain my goal. I have a database that has a

number
of

tables that have all the same fields. Some of these tables will be
deleted
and new tables will be added (with the exact same fields). I am

trying

to
create a query that will run through all of the tables in the Tabledefs
(other than the system tables) and copy the records to one table, whose
name
(datatemp) will be constant. I really need the flexibility to have this
query run through any table added to the database.

Another feature I would like to add is that I would also like to

update

a
field with the Table's name before sending the data into the constant
table.
Hope this makes sense.

Here is my code so far...

Dim dbs As DAO.Database
Dim tbl As DAO.TableDefs
Dim t As DAO.TableDef
Dim x As String
Dim sqlstr As String

Set dbs = CurrentDb

For Each t In dbs.TableDefs
If t.Attributes = 0 Then
MsgBox t.Name ' I placed this here to see if it works - It
does
x = t.Name
MsgBox x ' I placed this here to see if it works, - It does
DoCmd.RunSQL "INSERT INTO Datatemp SELECT * FROM" & " " & x

&
";"

'This doesn't work - It does not recognize the value

of

x