Getting info from recordset in to table???? 1

[ribhead]

I need some help transferring information from a recordset that I am retrieving from an AS400 dbase to a newly created table that is in Access. Below is what I have so far but I am stuck right now. Any help would be grrreat.

Sub HELP()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strpath As String
Dim strpnum As String
strpnum = "8800463%"
strpath = "DSN=AMES01"
DoCmd.DeleteObject acTable, "MYTABLE"
strSQL = "Create Table MYTABLE" _
& "([Part Number] TEXT(50),[Operation] TEXT(20)," _
& "[Rate]DOUBLE);"
DoCmd.RunSQL strSQL
strSQL = "SELECT MFRFMR02 AS ""Part Number""," & _
" MFRFMR03 AS ""Operation""," & _
" MFRFMR0P AS ""Rate"" FROM DPNEW WHERE(MFRFMR02='8800463')"
cnt.Open (strpath)
Set rst = cnt.Execute(strSQL)
cnt.Close
Set cnt = Nothing
'How do I get the Recordset in to _
the table?

'rst.Close
'Set rst = Nothing

End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Browse your recordset and for each retrieved record build a dynamic INSERT INTO ... VALUES ... sql string you execute with DoCmd.RunSQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Does the table you're appending to have the same 3 fieldnames? Are you going to append all of the records based on your criteria? Do you need to track if any records could not be appended (e.g. due to validation or primary key errors)?


John

http://www.interautomateconsulting.com

 

[ribhead]

I've worked on this a bit and I got it to work. Perhaps PHV you were wanting me to do something different? If so please let me know.

Sub HELP()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strpath As String
Dim strpnum As String
Dim fld As Field
Dim mypart As String
Dim myop As String
Dim i As Integer
Dim myrate As Double
strpath = "DSN=AMES01"

On Error Resume Next
DoCmd.DeleteObject acTable, "MYTABLE"
strSQL = "Create Table MYTABLE" _
& "([Part Number] TEXT(50)," _
& "[Operation] TEXT (10),[Rate]DOUBLE);"
DoCmd.RunSQL strSQL
strSQL = "SELECT MFRFMR02 AS ""Part Number""," & _
" MFRFMR03 AS ""Operation""," & _
" MFRFMR0P AS ""Rate"" FROM DPNEW WHERE(MFRFMR08 LIKE '121%' OR" & _
" MFRFMR08 LIKE '113%')"
cnt.Open (strpath)
Set rst = cnt.Execute(strSQL)

'Dumping data into table
DoCmd.SetWarnings (False)
i = 0
Do While Not rst.BOF And Not rst.EOF
mypart = Trim(rst.Fields(i))
myop = Trim(rst.Fields(i + 1))
myrate = rst.Fields(i + 2)
If myrate <> 0 Then
myrate = Round(1 / myrate, 2)
Else
End If
strSQL = "INSERT INTO MYTABLE([Part Number],[Operation],[Rate])" & _
" VALUES('" & mypart & "','" & myop & "'," & myrate & ");"
DoCmd.RunSQL (strSQL)
i = 0
rst.MoveNext
Loop

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

MsgBox "Done."
End Sub

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[PHV]

Perhaps PHV you were wanting me to do something different?
No, you did it exactly as I suggested you.

 

[ribhead]

Cool! I learned something! I didn't know if there was a quicker way of doing this or not. Thanks for the hint. But more importantly thanks for not telling me how to do it.

Thanks, Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm a frayed knot.

 

[JonFer]

What about linking DPNEW and then running a Make Table query using it as your source?


John

http://www.interautomateconsulting.com