Solved: Newbie needs a kick start...

[sladerwilson]

I have been looking through the posts... and reading everything I can lay my hands on, but I can't get the simplest code to work. Can someone help steer me in the right direction? I have experience in SQL and connecting databases to ASP pages but VBA is brand new to me.

I am using Access 2000.

I created a new module and copy/pasted some sample code... yet I can't get this code to run at all... I get the following error:
Run-time error '424':
Object required

When I debug it highlights the Set conn = Server.CreateObject("ADODB.Connection") line and when I hover over the conn variable it says it's empty.

Someone please point me in the right direction.


Option Compare Database
Private Sub testCode()

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM export WHERE Field2 = 'C'"
rs.Open strSQL, conn, 3, 3
rs.MoveFirst
While Not rs.EOF
UPDATE CoreItemStatus = 'MFD'
rs.MoveNext
Wend

End Sub

[mattj]

No delete button?

[mattj]

Arrghh... Where's the delete button?

[mattj]

I see this is code to open and update a recordset. Is the data you are trying to access exist in the same database where you are running the code?

If so,
Try something like:


Dim db as DAO.Database
Dim rs as DAO.Recordset

set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM export WHERE Field2 = 'C'")

rs.MoveFirst

With rs
Do until .EOF
.Edit
.CoreItemStatus = 'MFD'
.Update
.MoveNext
Loop
End With

rs.close
db.close

set rs = nothing
set db = nothing

[sladerwilson]

Thank you so much... I had to change your code a little but at least I got this thing to do something... LOL!!! Now that I know how to make a SQL query work... I can do ANYTHING! LMAO! Just kidding...I am sure I'll be here reading again real soon!

Oh I almost forgot... the only thing I had to edit was the line that said:
.CoreItemStatus = 'MFD'
'changed to
.CoreItemStatus = "MFD"

[mattj]

Glad you got it worked out. Just so you know, you can do the same thing using the following:


Dim strSQL as String
strSQL = "UPDATE export SET CoreItemStatus = "MFD" WHERE Field2 = 'C'")
CurrentDb.Execute strSQL,dbFailOnError

[sladerwilson]

Awesomeness! This is what I needed... a kick start... I've already modified the code to look for the first product it finds with a certain status, and change all the similar items... this VBA is awesome and FAST! Much faster than the web server running it through ASP.

[XLGibbs]

Just keep in mind that SQL is designed to be SET based, as opposed to record/row based operations. Unlike SAS or some other dataset operations, SQL is designed such that you don't need to loop through every record to make changes and such.

Appears this was solved per your last post. I have update the thread to mark it solved.