Access, recordset, saving back to table?

[skiflyer]

Ok, so I'm new to VB and Access (using 2000) and need some what I think is pretty basic help.

I have a table, it has a field I want to pull apart, process and use that information to populate other fields.

I've used a connection and a recordset to pull out the information, been able to process it fine, and print it to my immediate window the VB area, but now I want to save it back to the table and am not sure where to go with that... any hints? Below is the code I'm using.

Follow up question, if I wanted to convert my integers to characters such that all entries were two digits (i.e. 01, 02...) how would I go about that in VB?

Thanks,
Rob

Option Compare Database

Sub convertandadd()
Dim cnn As Connection
Dim rsTester As New ADODB.Recordset

Set cnn = CurrentProject.Connection

rsTester.Open "tester", cnn, adOpenKeyset, adLockBatchOptimistic, adCmdTableDirect

While Not rsTester.EOF
temp = rsTester.Fields("tendigid&quot
temp12 = Math.Round((temp / 100000000) - 0.5)
temp34 = Math.Round((temp / 1000000) - 0.5) - (temp12 * 100)
temp56 = Math.Round((temp / 10000) - 0.5) - (temp34 * 100) - (temp12 * 10000)
temp78 = Math.Round((temp / 100) - 0.5) - (temp56 * 100) - (temp34 * 10000) - (temp12 * 1000000)
temp910 = temp - temp78 * 100 - temp56 * 10000 - temp34 * 1000000 - temp12 * 100000000

Debug.Print temp12

rsTester.MoveNext
Wend

rsTester.Close
cnn.Close

End Sub

 

[jebry]

Hi Rob!

I can't tell from your code what your table(s) structure is but you can open a table as a recordset and use the following to update or add records:

rst.Edit
your stuff
rst.Update

or
rst.AddNew
your stuff
rst.Update

Alternatively, you can build a query in code to update or append and use DoCmd.RunSQL to do you changes.

For your second question use:

YourString = Format(YourInteger, "00&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[LittleSmudge]

Before you go ANY further - you need to answer one fundimental question.

WHY are you decomposing this data and storing it back into the table ?

Do you intend to delete the original tendigid field ?
If not than WHY are you duplicating the data.



Next step.
Do you REALLY need leading zeros ?
Is this a requirement of some post processing - or - is it just some 'look nice' issue ?


G LS

 

[skiflyer]

So it's the .edit property followed by updated, beautiful thank you very much.


And as to why, it's because the guys in the office next to me say so, I personally agree with you, I think it's an easy calculation and can always been done on the fly, they want it static for some specific agregrations they want to run on a GIS server... don't get my started on the other curious DB design issues there, however, they know their GIS and I don't, so it all sounds plausible if curious.

-Rob

 

[skiflyer]

I'm confused, .edit doesn't seem to exist in for recordsets in my version, .editupdate is the closest, and when I try that it says I'm using it improperly.

Any ideas?

-Rob

 

[LittleSmudge]

Yes.

.Edit is only needed in DAO

You are using ADO - so you don't need it.

What you do need is to set the fields in the recordset equal to the values that you have created

Eg

While Not rsTester.EOF
    temp = rsTester.Fields("tendigid")
    temp12 = Math.Round((temp / 100000000) - 0.5)
    temp34 = Math.Round((temp / 1000000) - 0.5) - (temp12 * 100)
    temp56 = Math.Round((temp / 10000) - 0.5) - (temp34 * 100) - (temp12 * 10000)
    temp78 = Math.Round((temp / 100) - 0.5) - (temp56 * 100) - (temp34 * 10000) - (temp12 * 1000000)
    temp910 = temp - temp78 * 100 - temp56 * 10000 - temp34 * 1000000 - temp12 * 100000000

    Debug.Print temp12
    rst!dig12 = temp12
    rst!dig34 = temp34    
    rst!dig56 = temp56    
    rst!dig78 = temp78    
    rst!dig910 = temp910

    rst.Update

    rsTester.MoveNext
Wend
    
rsTester.Close

Change dig12 etc to the names of the new fields you've added into the "tester" table


'ope-that-'elps.

G LS

 

[skiflyer]

I really appreciate all this help, but I'm afraid I'm just not getting anywhere with it. So here's my code now.


Sub convertandadd()
Dim cnn As Connection
Dim rsTester As New ADODB.Recordset

Set cnn = CurrentProject.Connection

rsTester.Open "tester", cnn, adOpenKeyset, adLockBatchOptimistic, adCmdTableDirect

'While Not rsTester.EOF
temp = rsTester.Fields("eightdigid&quot
temp12 = Math.Round((temp / 1000000) - 0.5)
temp34 = Math.Round((temp / 10000) - 0.5)
temp56 = Math.Round((temp / 100) - 0.5)

Debug.Print temp12, temp
rsTester!firsttwo = temp12
rsTester!threefour = temp34
rsTester!fivesix = temp56
rsTester.Update
rsTester.MoveNext
'Wend

rsTester.Close
cnn.Close

End Sub


I commented out the loop because if I leave it in I get a
Number of rows with pending changes exceeds the limit.
error
(After it has printed out two rows of sensible data and by the debugger appears to be on its third iteration (there are 9 test rows in total, I want to get this test table working before applying to the real set)

If I take it out it runs to completion, printing out sensible data to my immediate window, but changing nothing in the table.

This is confusing, and actually closer to where I started then where I was when I posted... ugh. My thought then was I was changing a value of a copy of recordset rather than the actual table.

Thanks again.

-Rob

 

[LittleSmudge]

YES - I think I have spotted it.

The enum adLockBatchOptimistic in the rst.Open line is not compatible with rst.Update.

Change the Lock Type to adLockOptimistic


'ope-that-'elps.

G LS

 

[skiflyer]

That is indeed the answer to all my problems... what happens when you try to learn a language without a reference book.

Thanks so much,

Rob

 

[LittleSmudge]

. . . what happens when you try to learn a language without a reference book.

You end up learning all sorts of interesting - although unrelated - things.

You find lots of ways of NOT doing what you intended.

You tend to spend a lot of time on the TekTips site.

Then after a while you find you're spending more time giving - than receiving - help, because you've already been to all those crazy places that other people are trying to work their way out of.



Programme on dude.

G LS