Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001
    Posts
    24

    Unanswered: Sample code for exporting tables to Access

    Hello,

    Can someone give me some sample code on how to export tables to an Access database please. I would be very happy with it, because I don't get how it works.

    Thanks!

    Tom

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Have you looked into using DTS or even the Export Wizard to create the DTS package for you? Or are you looking for a program writing in a language like Perl to export data?

  3. #3
    Join Date
    Dec 2001
    Posts
    24
    I know it's easy to export tables by using the wizard, but I'm looking for some written code, in for example Visual Basic, to let it happen.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If you want use VB to write some code then incorporate ADO. You'll have to create two ADO connections one to your SQL Server and one to the MS Access DB. Execute a SELECT statement on the SQL Server connection return the values to a recordset object. Then while (!Recordset.EOF) insert the values in the MS Access using the second connection and format in insert statement. After the INSERT do a MoveNext on the SQLServer recordset.

    Or you can use Linked Servers

    EXEC sp_addlinkedserver
    @server = 'MyAccessDB',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = 'C:\Temp\dbExport.mdb'
    GO

    exec sp_addlinkedsrvlogin 'MyAccessDB', 'FALSE', NULL, 'Admin', NULL
    GO

    insert MyAccessDB...Employee (LastName,FirstName)
    select au_lname, au_fname from pubs..Authors
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •