Replace all tbl records with new data

[Rod]

I have tblDN and tblDNRawData. tblDN only has [DNNumber]. tblDNRawData has
[Number] and a few other records.... Via VB I would like to:

1) delete all of the records in tblDN's DNNumber
2) take all of the data in tblDNRawData's Number and push into tblDN's
DNNumber

Thanks for your help!

 

[Sergey Poberezovskiy]

CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError

 

[Rod]

Private Sub FixDN_Click()
CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError
End Sub

Returned:
Run-time error '3134: Syntax error INSERT INTO statement.

CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError

I have tblDN and tblDNRawData. tblDN only has [DNNumber]. tblDNRawData has
[Number] and a few other records.... Via VB I would like to:

1) delete all of the records in tblDN's DNNumber
2) take all of the data in tblDNRawData's Number and push into tblDN's
DNNumber

Thanks for your help!

 

[Sergey Poberezovskiy]

Rod,

just add "into" into the second statement:

CurrentDb.Execute "insert INTO tblDN (Number) select Number from
tblDNRawData", dbFailOnError

when I wrote the code I was just working in SQL Server where INTO is
optional

Private Sub FixDN_Click()
CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError
End Sub

Returned:
Run-time error '3134: Syntax error INSERT INTO statement.

CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError

I have tblDN and tblDNRawData. tblDN only has [DNNumber]. tblDNRawData has
[Number] and a few other records.... Via VB I would like to:

1) delete all of the records in tblDN's DNNumber
2) take all of the data in tblDNRawData's Number and push into tblDN's
DNNumber

Thanks for your help!

 

[Rod]

Great! Thanks much!

Rod,

just add "into" into the second statement:

CurrentDb.Execute "insert INTO tblDN (Number) select Number from
tblDNRawData", dbFailOnError

when I wrote the code I was just working in SQL Server where INTO is
optional

Private Sub FixDN_Click()
CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError
End Sub

Returned:
Run-time error '3134: Syntax error INSERT INTO statement.

CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from tblDNRawData",
dbFailOnError


:

I have tblDN and tblDNRawData. tblDN only has [DNNumber]. tblDNRawData has
[Number] and a few other records.... Via VB I would like to:

1) delete all of the records in tblDN's DNNumber
2) take all of the data in tblDNRawData's Number and push into tblDN's
DNNumber

Thanks for your help!

 

[George Nicholson]

Might consider using SELECT DISTINCT to avoid duplicates (if that's an
issue), even though OP did say "..all the data.."

HTH,

Rod,

just add "into" into the second statement:

CurrentDb.Execute "insert INTO tblDN (Number) select Number from
tblDNRawData", dbFailOnError

when I wrote the code I was just working in SQL Server where INTO is
optional

Private Sub FixDN_Click()
CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from
tblDNRawData",
dbFailOnError
End Sub

Returned:
Run-time error '3134: Syntax error INSERT INTO statement.

CurrentDb.Execute "delete * from tblDN", dbFailOnError
CurrentDb.Execute "insert tblDN (Number) select Number from
tblDNRawData",
dbFailOnError


:

I have tblDN and tblDNRawData. tblDN only has [DNNumber].
tblDNRawData has
[Number] and a few other records.... Via VB I would like to:

1) delete all of the records in tblDN's DNNumber
2) take all of the data in tblDNRawData's Number and push into
tblDN's
DNNumber

Thanks for your help!