INSERT INTO MS Access DB with dates

[opie]

I keep getting a syntax error everytime I insert a date or time into the DB. I cannont understand what I am doing wrong. Any suggestions?

Thank you

1 Dim conEntry As ADODB.Connectio n
2 Dim datDate As Date
3 Dim strTime As String
4 Dim strEntry As String
5 Dim strInitials As String
6 Dim datDateTime As Date

7 datDate = Date
8 strTime = txtTime.Text
9 strEntry = txtEntry.Text
10 strInitials = UCase(txtInitia ls.Text)
11 datDateTime = Now()

12 'Open a connection to the Database
13 Set conEntry = New ADODB.Connectio n
14 conEntry.Connec tionString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\DutyL og.mdb; Persist Security Info=False"
15 conEntry.Open

16 'Add entry
17 conEntry.Execut e ("INSERT INTO tblLog (Date, Time, Entry, Initials, DateTime) VALUES (" & _
"#" & datDate & "#, " & _
"'" & strTime & "', " & _
"'" & strEntry & "', " & _
"'" & strInitials & "', " & _
"#" & datDateTime & "#)")

18 'Close the connection to the Database
19 conEntry.Close

[lotus18]

[code=vb]
'Add entry
conEntry.Execut e ("INSERT INTO tblLog (Date, Time, Entry, Initials, DateTime) VALUES (" & _
"#" & datDate & "#, " & _
"'" & strTime & "', " & _
"'" & strEntry & "', " & _
"'" & strInitials & "', " & _
"#" & datDateTime & "#)")

'Close the connection to the Database
conEntry.Close
[/code]

Really? Double-check your codes again. I only see the datDate and datDateTime are enclosed with #, how about your strTime?
BTW, what is the the datatype of datDateTime? Is it really a date or a string?

Rey Sean

[opie]

Hi opie

You cannot use the Date and Time as your field name. Date and Time are predefined function in sql. Also, when dealing with time, you should enclosed it with #.

Rey Sean

I did use # signs as shown, but using Date or Time as a column in a table should not effect a query statement, but just to make sure I renamed them anyways and it still did not work.

[lotus18]

Hi Opie

See my reply at #2 above. I overwrite my reply. LOL

Rey Sean

[opie]

Hi Opie

See my reply at #2 above. I overwrite my reply. LOL

Rey Sean

Really? Double-check your codes again. I only see the datDate and datDateTime are enclosed with #, how about your strTime?
BTW, what is the the datatype of datDateTime? Is it really a date or a string?

Rey Sean

All my declarations are in line 1 thru 5 in my original post and written the way they were intendend to be written


Dim conEntry As ADODB.Connectio n
Dim datDate As Date
Dim strTime As String
Dim strEntry As String
Dim strInitials As String
Dim datDateTime As Date

[opie]

Really? Double-check your codes again. I only see the datDate and datDateTime are enclosed with #, how about your strTime?
BTW, what is the the datatype of datDateTime? Is it really a date or a string?

Rey Sean

All my declarations are in line 1 thru 5 in my original post and written the way they were intendend to be written


Dim conEntry As ADODB.Connectio n
Dim datDate As Date
Dim strTime As String
Dim strEntry As String
Dim strInitials As String
Dim datDateTime As Date

Does anyone else have any other ideas?

[lotus18]

Does anyone else have any other ideas?

I don't see any syntax error from your sql statement. Try to enclosed the entry with [ ] or check again the datatypes from your datatbase.

Rey Sean

[opie]

I rewrote part of the statement and narrowed it down to the datDateTime variable as the issue since I can insert the rest of the data w/o incident.

Dim datDateTime As Date

datDateTime = Now()

("INSERT INTO tblLog (EventDate, EventTime, Entry, Initials, DateTime) VALUES (#" & datDate & "#, '" & strTime & "', '" & strEntry & "', '" & strInitials & "', #" & datDateTime & "#)")


and the Access DB datatype is Date/Time any other ideas?

Thank you

Dim conEntry As ADODB.Connectio n
Dim datDate As Date
Dim strTime As String
Dim strEntry As String
Dim strInitials As String
Dim datDateTime As Date

datDate = Date
strTime = txtTime.Text
strEntry = txtEntry.Text
strInitials = UCase(txtInitia ls.Text)
datDateTime = Now()

'Open a connection to the Database
Set conEntry = New ADODB.Connectio n
conEntry.Connec tionString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\DutyL og.mdb; Persist Security Info=False"
conEntry.Open

'Add entry
conEntry.Execut e ("INSERT INTO tblLog (EventDate, EventTime, Entry, Initials, DateTime) VALUES (#" & datDate & "#, '" & strTime & "', '" & strEntry & "', '" & strInitials & "', #" & datDateTime & "#)")

'Close the connection to the Database
conEntry.Close

[Torgg]

Opie,

You have used a reserved word in your INSERT INTO Statement. The word “DateTime” is a reserved word in Access and should be enclosed in square brackets [] when used in a query. For example if you take your SQL Statement and enclose the field name “DateTime” in square brackets “[DateTime]” it will work.

("INSERT INTO tblLog (EventDate, EventTime, Entry, Initials, [DateTime]) VALUES (#" & datDate & "#, '" & strTime & "', '" & strEntry & "', '" & strInitials & "', #" & datDateTime & "#)")

Here is a link to all the reserved words for Access.

http://support.microsoft.com/kb/286335

I tested this and it works fine with the square brackets… You should also know that this was the problem with your original post/query as you were using a lot of reserved words for field names. That’s why when you changed most of the field names and wrote a new query it worked. I would recommend that you change your “DateTime” field name to “EventDateTime” so that future queries will work without error and you or someone else won’t have to remember to use the square brackets. Hope this helps.

Torgg