VBA Excel SQL INSERT query adds apostrophe to all data inserted

Question

I'm trying to use an INSERT statement to insert data from a userform to an excel spreadsheet, but it adds apostrophes to every inserted value, even dates and numbers.

How can I do an insert statement that will not insert any apostrophe for any value?

Code I'm using currently:

Sub Insert_data()
Dim con As Object
Dim vSQL1 As String
Dim dbpath As String

dbpath = myworkbookpath

Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=0"";"
con.Open

'CancelledFeeEntryForm.LogDate.Value = Date
'CancelledFeeEntryForm.RecordAppNum.Value = 123456789

vSQL1 = "INSERT INTO [Sheet1$] ([Employee],[Date Of Call],[Application Number]) " & _
"VALUES('" & CancelledFeeEntryForm.Employee.Value & "'," & _
"#" & CancelledFeeEntryForm.LogDate.Value & "#," & _
CancelledFeeEntryForm.RecordAppNum.Value & ");"

con.Execute (vSQL1)
con.Close

End Sub

Kevin · Accepted Answer · 2019-04-09 20:23:02Z

1

You should've debugged and looked at what exactly vSQL1 is containing.

From looking at it, this is what your SQL statement is going to look like:

... VALUES ('SomeStringValue',#SomeDateValue,123')

... aka, there's an apostrophe at the end of the numerical value... but not at the beginning.

To be honest, I'm glad Excel VBA is handling it like this. Because the alternative would be having an open security hole for SQL Injection Attack (I was about 5 seconds away from going on a rant about how you should never do SQL statements like this, until I noticed that VBA protected you from a serious security mistake.)

answered Apr 9, 2019 at 20:23

Kevin

2,2431 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

W-hit Over a year ago

sorry, that was an abridged version of the full code. I've edited my post without the extra apostrophe.

Kevin Over a year ago

@W-hit Huh. Well, that completely invalidates my answer. About the only thing I can think of now is that the bridge from 'SQL' to excel is trying to figure out the datatype off the SQL side of things and it's coming up with 'general/string' for the answer. What happens if you change the SQL statement to something like convert(int, myNumVal) - and see if it's better with the forced context of a data type? Dunno - I might have to fire up VBA to see if I can reproduce the problem and play around with it, simply for curiosity's sake.

W-hit Over a year ago

tried convert with no luck. Most likely because it's excel that's the problem rather than the sql syntax.

W-hit · Accepted Answer · 2019-04-10 14:43:05Z

0

Figured out a work around, although it's annoying, it'll have to do for now.

As is with excel in many cases, I had to enter dummy data on line 2 of the workbook i'm inserting data in the format I want. Then, when using the SQL insert code, it will match the existing data.

If anyone knows how to do it through code, feel free to pitch in. thanks

answered Apr 10, 2019 at 14:43

W-hit

3615 silver badges15 bronze badges

Collectives™ on Stack Overflow

VBA Excel SQL INSERT query adds apostrophe to all data inserted

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related