INSERT Statements. How to do it!

Hi,
Im wanting to insert a date value (quote_date) into a a table called "quotes". I have formatted into my database for my VB application. Can someone give me some pointers. Here is my code so far

Public Sub InsertQuote(strQuoteDate As Date)
Dim strSql As String

strSql = "insert into quotes (quote_date) values(" & strQuoteDate & ")"

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\irs\irs1.mdb;" & _
"Uid=admin;" & _
"Pwd="


End Sub

Basically, How do I execute it and format it for my access database to accept?

Andy

Quote:

---

Originally posted by andycharger
Hi,
Im wanting to insert a date value (quote_date) into a a table called "quotes". I have formatted into my database for my VB application. Can someone give me some pointers. Here is my code so far

Public Sub InsertQuote(strQuoteDate As Date)
Dim strSql As String

strSql = "insert into quotes (quote_date) values(" & strQuoteDate & ")"

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\irs\irs1.mdb;" & _
"Uid=admin;" & _
"Pwd="


End Sub

Basically, How do I execute it and format it for my access database to accept?

Andy

---

strQuoteDate FORMAT: "#DATE#"

Execute: oConn.Execute strSql

VB Code:

Public Sub InsertQuote(strQuoteDate As Date)
    Dim oConn As ADODB.Connection
 
    Set oConn = New ADODB.Connection
 
    With oConn
        .ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
                            "Dbq=c:\irs\irs1.mdb;" & _
                            "Uid=admin;" & _
                            "Pwd="
    
        .Execute "INSERT INTO quotes (quote_date) VALUES (#" & strQuoteDate & "#)"
    End With
    
    Set oConn = Nothing
End Sub

That is great except, the connectionstring command did not work. I changed it to .open instead and it does. However, if I want to pass more than one value from the form, I thought I would do it like this:
insertQuote( strQuoteDate as Date, strQuoteNumber as String)
However, I get anerror saying:
Compile Error

Expected: List seperator or )

How do I get around this?

Code:

---

strSQL = "INSERT INTO TableNameHere "
strSQL = strSQL & "(TableColumn1Name, TableColumn2Name) "
strSQL = strSQL & "VALUES ( '" & FormControlColumn1Val & "', '" & FormControlColumn2Val & "')"

oConn.execute strSQL

If oConn.errors.count > 0 Then
    Msgbox "Error occurred!!!"
End If

---

Notice that I've used ' characters there, so if the statement will come out like:

Code:

---

"INSERT INTO table1 (column1) VALUES ('Textbox1TextVal')"

---

You only need to enclose string values in single quotes, if you're dealing with numbers (i.e. like passing a 0 or 1 value back from a checkbox control value, you don't enclose these values in the single quotation marks. :)