none
OLEDB Dumping datatable into Access file RRS feed

  • Question

  • Hi,

    Using OLEDB in C#, is there a way to dump entries from a datatable directly into a new Microsoft Access file?
    For instance, I load records from SQL server, and want to save data into Access file locally, how can that be accomplished?

    Thanks in advance,

    Joe
    Thursday, July 24, 2008 4:01 AM

Answers

  • Yes, you can use a SQL statement to do this:

            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                    "Data Source=C:\Test Files\db1.mdb")
            AccessConn.Open()

            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)

            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    If the column names between the tables are different then they will probably need to be specified in the SQL INSERT statement.
    Thursday, July 24, 2008 2:03 PM

All replies

  • There is no built-in functionality to do it in .NET. What you could do is to use Jet OLEDB provider or ACE provider to create empty database and then transfer data row-by-row from DataTable to database. Another way is to use SQL Server Integration Services (formet DTS) that allows export/import data between different data sources.

     

    Thursday, July 24, 2008 9:55 AM
    Moderator
  • Yes, you can use a SQL statement to do this:

            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                    "Data Source=C:\Test Files\db1.mdb")
            AccessConn.Open()

            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)

            AccessCommand.ExecuteNonQuery()
            AccessConn.Close()

    If the column names between the tables are different then they will probably need to be specified in the SQL INSERT statement.
    Thursday, July 24, 2008 2:03 PM
  • Hi, I'm now trying to copy the database from a DBF file to an Access file, but using the same syntax above produces errors, does anyone know why?
    Monday, September 22, 2008 12:06 AM
  • Could you post your code?

    Monday, September 22, 2008 1:16 AM
  • Actually, I realize where my mistake is, though I am still curious in what I should put inside the orange highlights if I wanted to copy from a dbf file:

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)

    Monday, September 22, 2008 2:08 AM
  • There is an example in the below link:

     

    http://tinyurl.com/46y6vy

     

     

    Monday, September 22, 2008 3:28 AM
  • Paul,

    Your example works in my VISTA but not in Wind 7 64bit. I think this has to do with something of 32bit ODBC VS. 64bit ODBC. How can I modify your example for Win 7 64bit?

    Thanks..Nam

    Friday, April 5, 2013 5:18 AM
  • You will need to compile your app in 32bit mode for Windows 7 instead of 64bit mode.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Sunday, April 7, 2013 10:35 PM
  • Kevin, I'm developing my app on VISTA Home premium with VS 2010 both of which are 32 bit. - Thanks..Nam
    Sunday, April 14, 2013 7:57 PM