Saving a recordset to a table

[John]

Hi

I have made a recordset that consist of a complete Access table. What I want
to do is to save the content of this recordset into a SQL server table. How
can I do this. Here are some of my code:

'set up connections for both db's
Dim connAccess As ADODB.Connection
Dim connSQL As ADODB.Connection

Dim rsAccess As ADODB.Recordset
Dim rsSQL As ADODB.Recordset

'Retrieve data from Access into recordset
Set connAccess = New ADODB.Connection
connAccess.Open ("DSN=mydsn;UID=;PWD=")

Set rsAccess = New ADODB.Recordset
rsAccess.CursorType = adOpenKeyset
rsAccess.LockType = adLockOptimistic
rsAccess.Open "TReplace", connAccess, , , adCmdTable

Now I have got the recordset into rsAccess....how can I get it's content
into the SQL server table "ReplaceTable" ???

Greatful for help

regards
/John

[mgaft]

"John" <jonasjohansson70@hotmail.com> wrote:
>
>Hi
>
>I have made a recordset that consist of a complete Access table. What I
want
>to do is to save the content of this recordset into a SQL server table.
How
>can I do this. Here are some of my code:
>
>'set up connections for both db's
>Dim connAccess As ADODB.Connection
>Dim connSQL As ADODB.Connection
>
>Dim rsAccess As ADODB.Recordset
>Dim rsSQL As ADODB.Recordset
>
>'Retrieve data from Access into recordset
>Set connAccess = New ADODB.Connection
>connAccess.Open ("DSN=mydsn;UID=;PWD=")
>
>Set rsAccess = New ADODB.Recordset
>rsAccess.CursorType = adOpenKeyset
>rsAccess.LockType = adLockOptimistic
>rsAccess.Open "TReplace", connAccess, , , adCmdTable
>
>Now I have got the recordset into rsAccess....how can I get it's content
>into the SQL server table "ReplaceTable" ???
>
>Greatful for help
>
>regards
>/John
>
>
>
Hi,

I am not sure how to do it with recordsets, however, it is possible to do
right on SQL server by creating a linkserver. Below is an approximate example.
Try it in query analizer.

EXEC sp_DropServer 'AccessSvr'
EXEC sp_addlinkedserver
@Server='AccessSvr',
@SrvProduct = 'OLE DB Provider for JET',
@Provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\Program Files\Microsoft Office\Office\Samples\Operators\NorthWind.mdb;Persist
Security Info=False'

GO

SELECT *
FROM OPENQUERY(AccessSvr, 'SELECT * from cLookup_Names')
GO

[Kamal]

"John" <jonasjohansson70@hotmail.com> wrote:
>
>Hi
>
>I have made a recordset that consist of a complete Access table. What I
want
>to do is to save the content of this recordset into a SQL server table.
How
>can I do this. Here are some of my code:
>
>'set up connections for both db's
>Dim connAccess As ADODB.Connection
>Dim connSQL As ADODB.Connection
>
>Dim rsAccess As ADODB.Recordset
>Dim rsSQL As ADODB.Recordset
>
>'Retrieve data from Access into recordset
>Set connAccess = New ADODB.Connection
>connAccess.Open ("DSN=mydsn;UID=;PWD=")
>
>Set rsAccess = New ADODB.Recordset
>rsAccess.CursorType = adOpenKeyset
>rsAccess.LockType = adLockOptimistic
>rsAccess.Open "TReplace", connAccess, , , adCmdTable
>
>Now I have got the recordset into rsAccess....how can I get it's content
>into the SQL server table "ReplaceTable" ???
>
>Greatful for help
>
>regards
>/John
>
>
>
1.) Create a stored procedure to insert records from rsAccess
to SQL
2.) Loop through rsAccess and call the stored procedure you
created on SQL using the Command Object