running a queryDef query???

[pdbowling]

Hi, all.

I'm trying to do an update on a database using an SQL statement built from a string. What's wrong with this code snippet?

Sub addDate()

Dim db As Database
Dim qdf As QueryDef
Dim mySQL As String
Dim lastMonth As Integer
Dim reportYear As Integer

lastMonth = Form_ReportMonth.myMonth
reportYear = Form_ReportMonth.myYear

Set db = OpenDatabase("c:\TruckReport\TruckReport.mdb")
Set qdf = db.CreateQueryDef("")

mySQL = "UPDATE costRecord SET costRecord.[Date] = #" & CStr(lastMonth) & "/1/" & CStr(reportYear) & "#;"

qdf.SQL = mySQL

qdf.Execute <--- crashes here, can't find table????

qdf.Close
db.Close

End Sub

Thanks
PB

 

[Mile-O]

Try this:

mySQL = "UPDATE costRecord SET costRecord.[Date] = #" & DateSerial(reportYear, lastMonth, 1) & "#;"

 

[pdbowling]

Hmmm

I still get the same error.....

The Microsoft Jet database engine cannot find the input table or query 'costRecord'. Make sure it exists and that it is spelled correctly.

A more succint way of doing it would be more than welcomed as well as long as it uses a string SQL statement.

Thanks
PB

 

[Mile-O]

Sub addDate()
    
    Dim db As Database
    Dim qdf As QueryDef
    Dim mySQL As String
    Dim lastMonth As Integer
    Dim reportYear As Integer
        
    lastMonth = Form_ReportMonth.myMonth
    reportYear = Form_ReportMonth.myYear
        
    Set db = OpenDatabase("c:\TruckReport\TruckReport.mdb")
    
    mySQL = "UPDATE costRecord SET costRecord.[Date] = #" & DateSerial(reportYear, lastMonth, 1) & "#;" 

    Set qdf = db.CreateQueryDef("", mySQL)

    qdf.Execute

    qdf.Close
    db.Close
    
End Sub

That's my last suggestion as I've never used Querydefs...yet!

 

[raskew]

Borrowing on Mile-O-Phile's code, I modified it to act against one of my own applications. It worked!

Sub addDate()

Dim db As DATABASE
Dim qdf As QueryDef
Dim mySQL As String
Dim lastMonth As Integer
Dim reportYear As Integer

'not used
lastMonth = 3
reportYear = 2003

Set db = OpenDatabase("c:\My Documents\dateomatic2.mdb")
Set qdf = db.CreateQueryDef("")

mySQL = "UPDATE tblFormName SET tblFormName.FormName = 'lbCluck'" _
    & " WHERE (((tblFormName.FormName)='lbClock'));"


qdf.SQL = mySQL

qdf.Execute

qdf.Close
db.Close

End Sub

Since you say it can't find the table, the first place to look is the db path and/or the table name. Try stepping through it line by line. That should give you a better feel for where it's encountering the error.

 

[pdbowling]

Great!

Hey, thanks for the tips guys. In my inexperience, I overlooked the easiest method of all. DoCmd.RunSQL. Sorry to have bothered you.
PB