data manipulation in access from Excel vba

right, i have a excel file with data in it. I can import the data into
access db, but once i get it into there i want a user to be able press
a button in excel that the goes and manipulates all the data that is in
the access db and then produce the results in a new excel file.

i'm having issues with the start of the code that manipulates the data
- the recordset does not seem to start and goes straight to the msgbox

strFilePath = ThisWorkbook.path
fileDBPath = strFilePath & "\cof.mdb"

strDBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
fileDBPath
Set Conn = CreateObject("ADODB.Connection")
Conn.Open strDBPath

strSQL = "SELECT AutoNum, ORDER_STATUS_NOTE FROM POUpdateDetail WHERE
ORDER_STATUS_NOTE Like 'Requested Delivery Date Changed from*'"

Set rs = CreateObject("ADODB.Recordset")

Set rs = Conn.Execute(strSQL)

Do While Not rs.EOF

strAutoNum = rs("AutoNum")
strRFPDate = rs("ORDER_STATUS_NOTE")
strDate = Trim(Right(strRFPDate, 20))
strDate = Format(strDate, "dd/mm/yyyy")

strSQL = "UPDATE POUpdateDetail SET DelDate = '" & strDate & "'
WHERE AutoNum = " & strAutoNum
Conn.Execute (strSQL)

rs.MoveNext
Loop

rs.Close: Set rs = Nothing

Conn.Close: Set Conn = Nothing

MsgBox "Complete"

Try ???
strSQL = "UPDATE POUpdateDetail SET DelDate = #" & strDate & "#"

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"[email protected]" wrote:

> right, i have a excel file with data in it. I can import the data into
> access db, but once i get it into there i want a user to be able press
> a button in excel that the goes and manipulates all the data that is in
> the access db and then produce the results in a new excel file.
>
> i'm having issues with the start of the code that manipulates the data
> - the recordset does not seem to start and goes straight to the msgbox
>
> strFilePath = ThisWorkbook.path
> fileDBPath = strFilePath & "\cof.mdb"
>
> strDBPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
> fileDBPath
> Set Conn = CreateObject("ADODB.Connection")
> Conn.Open strDBPath
>
> strSQL = "SELECT AutoNum, ORDER_STATUS_NOTE FROM POUpdateDetail WHERE
> ORDER_STATUS_NOTE Like 'Requested Delivery Date Changed from*'"
>
> Set rs = CreateObject("ADODB.Recordset")
>
> Set rs = Conn.Execute(strSQL)
>
> Do While Not rs.EOF
>
> strAutoNum = rs("AutoNum")
> strRFPDate = rs("ORDER_STATUS_NOTE")
> strDate = Trim(Right(strRFPDate, 20))
> strDate = Format(strDate, "dd/mm/yyyy")
>
> strSQL = "UPDATE POUpdateDetail SET DelDate = '" & strDate & "'
> WHERE AutoNum = " & strAutoNum
> Conn.Execute (strSQL)
>
> rs.MoveNext
> Loop
>
> rs.Close: Set rs = Nothing
>
> Conn.Close: Set Conn = Nothing
>
> MsgBox "Complete"
>
>