recordset form table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I create a recordset based on records in my table.

For example I want to set my recordset = myfield.mytable.

Rgds,

Bruce
 
Bruce said:
How do I create a recordset based on records in my table.

For example I want to set my recordset = myfield.mytable.
Click to expand...

In principle (using DAO objects):

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT myfield FROM mytable" )

You can also do it with ADO.

What exactly are you trying to accomplish?
 
Thanks Dirk.

I want to set the recordset records as an array and loop through (i think).

The field.table I refer to contains elemets that I want to pass as variables
to some other code.
 
Bruce said:
Thanks Dirk.

I want to set the recordset records as an array and loop through (i
think).
Click to expand...

You can put the recordset's records and fields into an array, but why do
that instead of looping through the recordset itself?
The field.table I refer to contains elemets that I want to pass as
variables to some other code.
Click to expand...

I'm afraid I'm not following you here. Maybe if you posted a more
concrete example, I'd see what you're after.
 
Dirk,

This is the code I have so far. So far this works and the array myArr
contains the elements that I pass to the SQL below.

The elements in myArr exist in one of my tables already so I would like to
fill an array with these instead of hard coding, this is because there are
1,500 records in the table).

Any ideas,

Sub myAppend()

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double


myArr = Array("NAB", "CBA", "ANZ")
myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

For Each mySecurity In myArr

myString = "INSERT INTO tbl_Volatility ( ASXCode, Close_H,
Close_L, Volatility )" & _
"SELECT myCode(""" & mySecurity & """), Close_H(""" &
mySecurity & """," & myStartDate & "), Close_L(""" & mySecurity & """," &
myStartDate & "), Volatility(""" & mySecurity & """," & myStartDate & ");"
DoCmd.RunSQL myString

Next mySecurity

DoCmd.SetWarnings True

MsgBox ("Done!")

End Sub
 
Bruce said:
Dirk,

This is the code I have so far. So far this works and the array myArr
contains the elements that I pass to the SQL below.

The elements in myArr exist in one of my tables already so I would
like to fill an array with these instead of hard coding, this is
because there are 1,500 records in the table).

Any ideas,

Sub myAppend()

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double


myArr = Array("NAB", "CBA", "ANZ")
myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

For Each mySecurity In myArr

myString = "INSERT INTO tbl_Volatility ( ASXCode,
Close_H, Close_L, Volatility )" & _
"SELECT myCode(""" & mySecurity & """), Close_H(""" &
mySecurity & """," & myStartDate & "), Close_L(""" & mySecurity &
"""," & myStartDate & "), Volatility(""" & mySecurity & """," &
myStartDate & ");" DoCmd.RunSQL myString

Next mySecurity

DoCmd.SetWarnings True

MsgBox ("Done!")

End Sub
Click to expand...

Here's a recordset-based revision, based on the assumption that you have
a table named "tblSecurities" with field "Security" containing your
data:

'----- start of code version 2 -----
Sub myAppend2()

On Error GoTo Err_Handler

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double

Dim rs As DAO.Recordset

myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

Set rs = CurrentDb.OpenRecordset("tblSecurities")

With rs
Do Until .EOF
myString = _
"INSERT INTO tbl_Volatility (" & _
"ASXCode, Close_H, Close_L, Volatility) " & _
"SELECT myCode(""" & !Security & """), " & _
"Close_H(""" & !Security & """," & myStartDate & _
"), " & _
"Close_L(""" & !Security & """," & myStartDate & _
"), " & _
"Volatility(""" & !Security & """," & myStartDate & _
");"

DoCmd.RunSQL myString

.MoveNext
Loop
.Close
End With

Set rs = Nothing

MsgBox ("Done!")

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub

'----- end of code version 2 -----

Here's an even simpler, SQL-based version base on the same assumptions.
This ought to work, and be more efficient, provided that myCode(),
Close_H(), Close_L(), and Volatility() are all public functions in a
standard module:

'----- start of code version 3 -----
Sub myAppend3()

On Error GoTo Err_Handler

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double

myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

myString = _
"INSERT INTO tbl_Volatility " & _
"(ASXCode, Close_H, Close_L, Volatility) " & _
"SELECT " & _
"myCode([Security]), " & _
"Close_H([Security]," & myStartDate & "), " & _
"Close_L([Security]," & myStartDate & "), " & _
"Volatility([Security]," & myStartDate & ") " & _
"FROM tblSecurities;"

DoCmd.RunSQL myString

MsgBox ("Done!")

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code version 3 -----
 
Hi Dirk,

Yes, myCode(), Close_H(), Close_L(), and Volatility() public functions.....

I will process your recommendations over the weekend. Great tips I must say.

I will try both option because you provide as I want to optimise processig
time as much as possible because;

The database contains a header table with 1500 securites.
The related detail table hold 2,000,000 records.

I guess this is why I have chosen a procedural approach to this problem (by
appending each security) rather than via a relational approach (no code, just
access query).

Will let ya know how I get on.

Bruce.

Dirk Goldgar said:
Bruce said:
Dirk,

This is the code I have so far. So far this works and the array myArr
contains the elements that I pass to the SQL below.

The elements in myArr exist in one of my tables already so I would
like to fill an array with these instead of hard coding, this is
because there are 1,500 records in the table).

Any ideas,

Sub myAppend()

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double


myArr = Array("NAB", "CBA", "ANZ")
myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

For Each mySecurity In myArr

myString = "INSERT INTO tbl_Volatility ( ASXCode,
Close_H, Close_L, Volatility )" & _
"SELECT myCode(""" & mySecurity & """), Close_H(""" &
mySecurity & """," & myStartDate & "), Close_L(""" & mySecurity &
"""," & myStartDate & "), Volatility(""" & mySecurity & """," &
myStartDate & ");" DoCmd.RunSQL myString

Next mySecurity

DoCmd.SetWarnings True

MsgBox ("Done!")

End Sub
Click to expand...

Here's a recordset-based revision, based on the assumption that you have
a table named "tblSecurities" with field "Security" containing your
data:

'----- start of code version 2 -----
Sub myAppend2()

On Error GoTo Err_Handler

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double

Dim rs As DAO.Recordset

myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

Set rs = CurrentDb.OpenRecordset("tblSecurities")

With rs
Do Until .EOF
myString = _
"INSERT INTO tbl_Volatility (" & _
"ASXCode, Close_H, Close_L, Volatility) " & _
"SELECT myCode(""" & !Security & """), " & _
"Close_H(""" & !Security & """," & myStartDate & _
"), " & _
"Close_L(""" & !Security & """," & myStartDate & _
"), " & _
"Volatility(""" & !Security & """," & myStartDate & _
");"

DoCmd.RunSQL myString

.MoveNext
Loop
.Close
End With

Set rs = Nothing

MsgBox ("Done!")

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub

'----- end of code version 2 -----

Here's an even simpler, SQL-based version base on the same assumptions.
This ought to work, and be more efficient, provided that myCode(),
Close_H(), Close_L(), and Volatility() are all public functions in a
standard module:

'----- start of code version 3 -----
Sub myAppend3()

On Error GoTo Err_Handler

Dim myString As String, myDel As String
Dim myArr
Dim myStartDate As Double

myStartDate = startDate(-10)

DoCmd.SetWarnings False

myDel = "DELETE tbl_Volatility.* FROM tbl_Volatility;"
DoCmd.RunSQL myDel

myString = _
"INSERT INTO tbl_Volatility " & _
"(ASXCode, Close_H, Close_L, Volatility) " & _
"SELECT " & _
"myCode([Security]), " & _
"Close_H([Security]," & myStartDate & "), " & _
"Close_L([Security]," & myStartDate & "), " & _
"Volatility([Security]," & myStartDate & ") " & _
"FROM tblSecurities;"

DoCmd.RunSQL myString

MsgBox ("Done!")

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code version 3 -----


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Bruce said:
Hi Dirk,

Yes, myCode(), Close_H(), Close_L(), and Volatility() public
functions.....

I will process your recommendations over the weekend. Great tips I
must say.

I will try both option because you provide as I want to optimise
processig time as much as possible because;

The database contains a header table with 1500 securites.
The related detail table hold 2,000,000 records.

I guess this is why I have chosen a procedural approach to this
problem (by appending each security) rather than via a relational
approach (no code, just access query).

Will let ya know how I get on.
Click to expand...

Yes, keep us posted.

Are these functions -- Close_H(), Close_L(), and Volatility() -- having
to crunch 2M records to come up with the values they return for each
security? Are you opening recordsets inside the functions to accomplish
it? If so, I expect the bulk of your processing time is going to be
spent in those functions, and it could be (or not -- I'm just wondering)
that you can get the same output by including the SQL necessary to
calculating the function values in the main query, eliminating the
function calls and the recordsets. Sometimes, if the calculations lend
themselves to this approach, you can get massive gains in performance
that way.
 
Back
Top