By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

INSERT INTO MS Access DB with dates

P: 15
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.Connection
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(txtInitials.Text)
11 datDateTime = Now()

12 'Open a connection to the Database
13 Set conEntry = New ADODB.Connection
14 conEntry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DutyLog.mdb; Persist Security Info=False"
15 conEntry.Open

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


18 'Close the connection to the Database
19 conEntry.Close
Dec 5 '07 #1
Share this Question
Share on Google+
8 Replies


lotus18
100+
P: 866
Expand|Select|Wrap|Line Numbers
  1.  
  2.  'Add entry
  3. conEntry.Execute ("INSERT INTO tblLog (Date, Time, Entry, Initials, DateTime) VALUES (" & _
  4.                     "#" & datDate & "#, " & _
  5.                     "'" & strTime & "', " & _
  6.                     "'" & strEntry & "', " & _
  7.                     "'" & strInitials & "', " & _
  8.                     "#" & datDateTime & "#)")
  9.  
  10. 'Close the connection to the Database
  11. conEntry.Close
  12.  
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
Dec 5 '07 #2

P: 15
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.
Dec 5 '07 #3

lotus18
100+
P: 866
Hi Opie

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

Rey Sean
Dec 5 '07 #4

P: 15
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.Connection
Dim datDate As Date
Dim strTime As String
Dim strEntry As String
Dim strInitials As String
Dim datDateTime As Date
Dec 5 '07 #5

P: 15
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.Connection
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?
Dec 5 '07 #6

lotus18
100+
P: 866
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
Dec 6 '07 #7

P: 15
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.Connection
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(txtInitials.Text)
datDateTime = Now()

'Open a connection to the Database
Set conEntry = New ADODB.Connection
conEntry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DutyLog.mdb; Persist Security Info=False"
conEntry.Open

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

'Close the connection to the Database
conEntry.Close
Dec 6 '07 #8

P: 41
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
Dec 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.