ADO Recordset

[anasser]

How can I insert data (returned from an executed sql server 2000 store
Procedure) to a local MS Access table using VBA, and if its possible,
can I have the cursor run on the server side to make the processing
faster.

'Declarations and connect strings goes here.

'Create New RecordSet
Set Rs = New ADODB.Recordset

Rs.CursorLocation = adUseClient
Rs.Open Cmd.Execute, Conn, adOpenDynamic, adLockReadOnly
'the Cmd.Execute value here is the execution of the
'Server store procedure

If Rs.RecordCount > 0 Then
Rs.MoveFirst
While Not Rs.EOF
sql = "INSERT INTO LocalMSAccessTable(" & _
"Field Names here" & _
"Values()" ??????

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

Rs.MoveNext
Wend
End If

Thank you

 

[Dirk Goldgar]

How can I insert data (returned from an executed sql server 2000 store
Procedure) to a local MS Access table using VBA, and if its possible,
can I have the cursor run on the server side to make the processing
faster.

'Declarations and connect strings goes here.

'Create New RecordSet
Set Rs = New ADODB.Recordset

Rs.CursorLocation = adUseClient
Rs.Open Cmd.Execute, Conn, adOpenDynamic, adLockReadOnly
'the Cmd.Execute value here is the execution of the
'Server store procedure

If Rs.RecordCount > 0 Then
Rs.MoveFirst
While Not Rs.EOF
sql = "INSERT INTO LocalMSAccessTable(" & _
"Field Names here" & _
"Values()" ??????

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

Rs.MoveNext
Wend
End If

Thank you

It seems to me that the most efficient way may not be to use a recordset
at all, but instead have a pass-through query that executes the stored
procedure, and then use that query as the basis for an append query that
inserts all the returned records into the local table. For example, if
you have a stored procedure named "spMyProc", then you could have a
pass-through query that executes it -- SQL for the pass-through query
being something like this:

EXECUTE spMyProc

The PT query's ReturnsRecords property would be set to Yes/True.
Suppose you call this query "qryMyyPassThruQuery".

Then you just execute another query with SQL like this:

INSERT INTO LocalMSAccessTable
SELECT * FROM qryMyPassThruQuery;

Or you may need to specify a field list, in which case your SQL would be
more like this:

INSERT INTO LocalMSAccessTable
(SomeField, AnotherField, YetAnotherField)
SELECT
SomeField, AnotherField, YetAnotherField
FROM qryMyPassThruQuery;

 

[anasser]

It seems to me that the most efficient way may not be to use a recordset
at all, but instead have a pass-through query that executes the stored
procedure, and then use that query as the basis for an append query that
inserts all the returned records into the local table. For example, if
you have a stored procedure named "spMyProc", then you could have a
pass-through query that executes it -- SQL for the pass-through query
being something like this:

EXECUTE spMyProc

The PT query's ReturnsRecords property would be set to Yes/True.
Suppose you call this query "qryMyyPassThruQuery".

Then you just execute another query with SQL like this:

INSERT INTO LocalMSAccessTable
SELECT * FROM qryMyPassThruQuery;

Or you may need to specify a field list, in which case your SQL would be
more like this:

INSERT INTO LocalMSAccessTable
(SomeField, AnotherField, YetAnotherField)
SELECT
SomeField, AnotherField, YetAnotherField
FROM qryMyPassThruQuery;


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

(please reply to the newsgroup)

 

[anasser]

Thank you for responding...I'll give the pass through query a try and
see if it will solve my problem.