DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Saving a recordset to a table

  1. #1
    John Guest

    Saving a recordset to a table


    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




  2. #2
    mgaft Guest

    Re: Saving a recordset to a table


    "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



  3. #3
    Kamal Guest

    Re: Saving a recordset to a table


    "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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center