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
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