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"

data manipulation in access from Excel vba
 

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

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


" 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"