Processing Data in VBA using For...Next

[sladerwilson]

I have "some" experience in programming ASP/SQL - the code that follows works... but I want to get this script OFF my webserver. I can't get this code to work (in VBA)... can someone help me?

This is the ASP/SQL Code:

Dim i
For i = 1 To 2500

'Get the first product that has NOT been processed
objRst.Open "SELECT TOP 1 * from export WHERE Field48 = 'C' AND onWeb <> 'y' ORDER BY Field2 ASC"

Dim strItemName, strID
strItemName = objRst("Field45")
strID = objRst("Field2")

objRst.Close

'Set all items to MFD that are not Core Items, yet still have the same name but don't have any attributes.

objRst.Open "UPDATE export SET CoreItemStatus = 'MFD' WHERE Field45 = '"& strItemName &"' AND Field48 <> 'C' AND Field44=''"

'Now set all items to MFD that are ST-xxxxx items but DON'T have the ID of the first ST-xxxx item
objRst.Open "UPDATE export SET CoreItemStatus = 'MFD' WHERE Field45 = '"& strItemName &"' AND Field48 = 'C' AND Field2 <> '"& strID &"' AND Field44=''"

objRst.Open "UPDATE export SET Field48 = 'Y' WHERE Field45 = '"& strItemName &"' "

Next

Response.Write("Done!")

[sladerwilson]

Do I have to set all the connection parameters??? Such as:

Dim objConnect 'Default Connection Object
Dim objCmd 'Default Command Object
Dim objRst 'Default Recordset Object
Dim filepath 'File Path to Database

'Establish connection
Set objConnect = Server.CreateObject("ADODB.Connection")
filepath = Server.MapPath("db1.mdb")
objConnect.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +filepath)

'Create Command object
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection=objConnect
objCmd.CommandType = 1

'Set up objRst
Set objRst = Server.CreateObject("ADODB.Recordset")
Set objRst.ActiveConnection=objConnect
Set objRst.Source = objCmd

[XLGibbs]

Are you executing this within a MS Access shell?

If just VB you would have to specify (declare) the objects and types,

but you want a statement like

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myTable"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
'your action here
rs.MoveNext
WEND



http://support.microsoft.com/kb/257819
http://www.simongibson.com/intranet/adooledb/