I keep getting a syntax error

[JASONW10]

I keep getting a syntax error on This lline Call rs.Open(strsql, strConnectionString)
for my strsql string My string equals "INSERT INTO Usage (eDates) Values (#05/06/2011#);"
My access Data base Edates is set to be a date/time value. Does anyone know what I am doing wrong?

public counta as integer
Sub Insertinga()


'stay
counta = 4
While Range("a" & counta) <> ""
Application.Run ("senddata")
counta = counta + 1
Wend


End Sub
Sub senddata()
Dim rs As Recordset
Set rs = New Recordset


Dim n As Date
Dim edates As Date
Dim etimes As Date
Dim Peak As String
Dim KWh As Double
Dim KWd As Double
Dim KWa As Double
Dim KVar As Double
Dim PF As Double


edates = Range("h" & counta)
etimes = Range("i" & counta)
emonths = Range("j" & counta)
Peak = Range("b" & counta)
KWh = Range("c" & counta)
KWd = Range("d" & counta)
KWa = Range("e" & counta)
KVar = Range("f" & counta)
PF = Range("g" & counta)






'strsql = "INSERT INTO Usage (Dates, Times, Peak, KWh, Kwd, KVa, KVar, PF, Months) VALUES (" & "#" & dates & "#, #" & times & "#, " & Peak & ", " & KWh & ", " & KWd & ", " & KVa & ", " & KVar & ", " & PF & ", #" & edates & "#);"
strsql = "INSERT INTO Usage (eDates) Values (#" & edates & "#);"
'Adjust Filepath to Access in this string as needed
Const strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"\\pvnt2\home\Manufacturing\JWallace\Accessdatabases\Energyusage.mdb" & _
";Persist Security Info=False"
Call rs.Open(strsql, strConnectionString)
'rs.MoveFirst 'Header
'rs.MoveNext 'Actual first record

If (rs.State And ObjectStateEnum.adStateOpen) Then rs.Close
If Not rs Is Nothing Then Set rs = Nothing


End Sub

 

[Ruddles]

You don't Call rs.Open, you just rs.Open.

rs.Open isn't a procedure: .Open is a method of the object rs.

 

[JASONW10]

I tried it without the CALL but then it reds out the intire line. Furthermore, I used the call method many times before. Therefore, I think there is something else I am doing wrong, for it only tells my that I have a syntax error in my slq statment.

 

[Hermanito]

Try to execute that exact same query directly in your Access database. Access will tell you if there is an error in the query, Excel won't...

 

[Norie]

As well as removing Call remove the parentheses.

 

[Ruddles]

I used the call method many times before.

Good grief, you're right - that's a new on on me! My apologies.

Next step is to use Debug.Print to display the contents of strsql. If you've obtained this value from a worksheet cell, it may well contain an Excel date serial, rather than a string of characters representing a date which SQL understands and expects.

What's in strsql? If it's something like 40,500, you'll need to format it explicitly as a date:-

strsql = "INSERT INTO Usage (eDates) Values (#" & [COLOR=red][B]format([/B][/COLOR]edates[COLOR=red][B],"dd-mmm-yyyy)[/B][/COLOR] & "#);"

(I always use dd-mmm-yyyy format because then I'm sure which way round the day and the month are, but you could also use mm/dd/yyyy.)

 

[JASONW10]

I figured it out USage is a resive word in sql, so I changed it to EUsage for my table name. now everything works fine. Thanks for all your help