dcsimg
Results 1 to 6 of 6

Thread: Export Access database in Excel - OLEDB

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2018
    Posts
    17

    Export Access database in Excel - OLEDB

    hi to all again
    i have another question.

    how can i extract the tables from a database to Excel?

    i search online but i found only code with DoCmd line
    I dno't know why but when i insert that codeline the program not recognize that.

    someone can help me with that how to import that command or know how to export the database to excel without using doCmd?

    Thanks to all

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,128

    Re: Export Access database in Excel - OLEDB

    Let's be clear on the language first. You've posted this in the VB.NET forum. I'm not 100% sure but I expect that DoCmd is a VBA thing. Do you definitely want a solution in VB.NET?

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,314

    Re: Export Access database in Excel - OLEDB

    Hi,

    perhaps you mean with SQL ?
    if so you need a valid connection with Database to execute the SQL

    Code:
    strSQL_Excel = "SELECT * INTO [mySheetName] In  'C:\data81.xls' 'EXCEL 9.0;' FROM myAccessTable"
    the will Export the Table in Access = MyAccessTable
    to a new ExcelWorkbook = data81.xls
    and create the Sheet = mySheetName

    with the Data from the Access Table

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jul 2018
    Posts
    17

    Re: Export Access database in Excel - OLEDB

    Quote Originally Posted by jmcilhinney View Post
    Let's be clear on the language first. You've posted this in the VB.NET forum. I'm not 100% sure but I expect that DoCmd is a VBA thing. Do you definitely want a solution in VB.NET?
    Yes
    i do a windows application who connect to a local database in Access.
    My database is not an sql database.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,359

    Re: Export Access database in Excel - OLEDB

    SQL is not a kind of database, it is an (almost) standardised language for talking to databases, which can be used for databases created in Access and many other systems (including Oracle, Informix, and ones that happen to have SQL as part of their name, like SQL Server and MySQL). Instructions like "SELECT * FROM tablename" and "INSERT INTO tablename VALUES (...)" are examples of SQL statements.

    The SQL statement ChrisE showed is designed to be run against an Access based database, and will export the data to Excel. Just set up an OLEDBConnection to the database, and an OLEDBCommand with CommandText set to what ChrisE showed (altering the table names etc as apt).

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,513

    Re: Export Access database in Excel - OLEDB

    Hello,

    Check out the following MSDN code sample and be sure to read the comments which talk about possible issues and side affects.

    Note there is a good deal of code involved in the above code sample yet at the core it's very simple (keeping in mind of side affects).

    Code:
    cmd.CommandText = $"SELECT {SelectStatement} INTO [{Provider}DATABASE={ExcelFileName};HDR=No].[{WorkSheetName}] FROM [{TableName}]"
    Solution
    Name:  F1.png
Views: 238
Size:  14.3 KB

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width