CODEWITHSUNDEEP
sqlexcelvba
W-hit
W-hit

Reputation: 361

VBA Excel SQL INSERT query adds apostrophe to all data inserted

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

Upvotes: 0

Views: 280

Answers (2)

W-hit
W-hit

Reputation: 361

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

Upvotes: 0

Kevin
Kevin

Reputation: 2243

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

Upvotes: 1

Related Questions