PDA

View Full Version : INSERT OVERWRITE INTO correct syntax?



paulked
12-18-2017, 05:24 AM
Hi

I am holding one record in an Access DB which I update from Excel.

This works:



With CreateObject("ADODB.Connection")
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\KK\KKDB.accdb"
.Execute "DELETE FROM Settings"
.Execute "INSERT INTO Settings " _
& "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Y:\KK\KKNewTrack.xlsm].[DBSettings$];"
End With


but it is slow!

So I tried:



With CreateObject("ADODB.Connection")
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\KK\KKDB.accdb"
.Execute "INSERT OVERWRITE INTO Settings " _
& "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Y:\KK\KKNewTrack.xlsm].[DBSettings$];"
End With


But it doesn't like it, I get an "Syntax error in INSERT INTO statement.

Any ideas?

Best regards

Paul Ked

Aflatoon
12-18-2017, 05:34 AM
For one record I suspect it would be faster to run a simple UPDATE SQL passing the values you want.

paulked
12-18-2017, 05:42 AM
Thanks, I'll give it a try...