ADP Update Table Code

[Craig]

Hello. I'm using an Access XP .apd (Access data project).

I've written a module in DAO that loops through each record in a table, does
some analyzing, then updates another table with a result.

However, I need to perform this same procedure on an .adp with similar data.
But I get the "object variable or with variable not set" error message
(because, from what I read, you can't have the "CurrentDB" term in the module.



Here's a snippet of my code:

Dim db As DAO.Database, rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strX As String, x As Integer, i As Integer
ReDim MyArray(10) As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("vwBase")
Set rs1 = db.OpenRecordset("tblResults")
'
Do While Not rs.EOF
..
..
..
..
Do While MyArray(i) <> ""
If InStr(strX, "@kyoc") = 0 Then
rs1.AddNew
rs1!Id = rs("ID")
rs1!Field1 = rs("Field1")
rs1.Update
Exit Do
End If
Loop
..
..
..
..


So, how do I identify and update a table def using ADO?? (similar to what I
did above with DAO?). I know the basics (ie, Dim conn as ADODB.Connection,
etc.). But what I need is to be able to replicate the above DAO procedure
using ADO.

Thanks.

 

[Robert Morley]

You can't use DAO with ADP, you'll have to convert your code to ADO.

The code you've shown below is fairly simple to re-write, but there's
obviously more to it than you're showing, so if you have any further
problems, just post back.

Also, this is written off the top of my head, so there's the possibility
I've goofed somewhere. Oh and, "Do While Not" is the same as "Do Until", so
I changed it.

Dim rs As ADODB.Recordset, rs1 As ADODB.Recordset
Dim strX As String, x As Integer, i As Integer
ReDim MyArray(10) As String
Set rs = New ADODB.Recordset
rs.Open "vwBase", CurrentProject.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "tblResults", CurrentProject.Connection
'
Do Until rs.EOF
...
...
...
...
Do While MyArray(i) <> ""
If InStr(strX, "@kyoc") = 0 Then
rs1.AddNew
rs1!Id = rs("ID")
rs1!Field1 = rs("Field1")
rs1.Update
Exit Do
End If
Loop



Rob

 

[Craig]

Thanks Robert! I certainly will post with other issues. This definitely
helps though...

 

[Robert Morley]

Don't feel bad, I only clued in about a year ago myself. Probably cuz I got
into the habit of using While...Wend originally, and only used Do when I
needed the comparison at the end of the loop.

Then some time back, there was a discussion about the fact that Do
While...Loop is faster than While...Wend (at least in timing tests; usually
only noticeable in tight loops), so I went through the code in all my main
projects and changed them all. Then a while after that, one of my
constructs ended up being morphed into a Do Until .EOF, and I kinda thwapped
myself on the head when I realized what a moron I'd been all this time.

So...you're not alone!


Rob

 

[Stuart McCall]

Don't feel bad, I only clued in about a year ago myself. Probably cuz I
got into the habit of using While...Wend originally, and only used Do when
I needed the comparison at the end of the loop.

Then some time back, there was a discussion about the fact that Do
While...Loop is faster than While...Wend (at least in timing tests;
usually only noticeable in tight loops), so I went through the code in all
my main projects and changed them all. Then a while after that, one of my
constructs ended up being morphed into a Do Until .EOF, and I kinda
thwapped myself on the head when I realized what a moron I'd been all this
time.

So...you're not alone!


Rob

There's another advantage to using Do...While instead of While...Wend. If
you (perhaps later) decide that you need to exit the loop when some
condition is true, you can use Exit Do, whereas there's no Exit Wend.

 

[Robert Morley]

This is true too, though since I've been accused of being a structured
programming zealot before, this is usually not a concern for me. I've got
one Exit Do and two Exit For's in about 100,000 lines of code...and only one
of the Exit For's is my own; the Exit Do and other Exit For are someone
else's code that I've incorporated without re-writing.


Rob