none
Sql Server to Access DB RRS feed

  • Question

  • I have a requirement to convert a table in Sql Server 2005 database to Access DB.

    Any suggestions?

    Thursday, November 15, 2007 5:45 PM

All replies

  • You could use SQL to do this. It wasn't clear to me whether you need to create an index for the table or whether the table already exists in Access. Below is a code example that creates the table in Access on the fly and imports the data:

     

    Code Block

    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("SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)

     

    AccessCommand.ExecuteNonQuery()

    AccessConn.Close()

     

     

     

    Thursday, November 15, 2007 6:39 PM
  • Thank you for your response.

    I have a table with data in Sql Server 2005 db.

    I need create a table in in Access DB and store the Sql Server table in Access DB.

    Hence I need to convert the table from Sql Server to Access.

    Will the code yuo have posted do this?

    Thursday, November 15, 2007 9:37 PM
  • Yes, it should. Did you try the code?

     

    Friday, November 16, 2007 2:50 PM
  • Its very easy to convert, just read the table & place it in a dataset

    then write it to a Access DB file....

    its very simple, if you cant understand, say, i'll try & provide
     you the code...
    Friday, November 16, 2007 7:17 PM
  • I get an error for AccessCommand after 'FROM' clause.

    Can anyone help?

    Tuesday, November 20, 2007 2:07 PM
  • Could you post your SQL statement?

     

    Wednesday, November 21, 2007 3:01 PM