Export SQL database to Access - ASP.NET

Question

Is there any way to export data (not necessarily schema) to an access database via asp.net?

The server has no office components installed and the process must occur via a webpage (like an excel export).

Answer 1

Here is a very detailed article. It is something I stumbled upon, not an approach I am familiar with:

File Uploading to Access Database using ASP.NET by Faisal Khan.

Answer 2

You have to do it programatically.

1. Open the source table
2. Create a new AccessDB using ADO Extensions (as shown above)
3. Create the table in the AccessDB by reading the source schema (CREATE TABLE X ...)
4. Iterate thought the source table inserting the records in the Access table

Note: Code from http://www.freevbcode.com/ShowCode.asp?ID=5797 posted here in case the link cease to exists in the future

    'select References from the Project Menu, choose the COM tab, 
    'and add a reference to Microsoft ADO Ext. 2.7 for DDL and Security

    Public Function CreateAccessDatabase( ByVal DatabaseFullPath As String) As Boolean
        Dim bAns As Boolean
        Dim cat As New ADOX.Catalog()
        Try


         'Make sure the folder
         'provided in the path exists. If file name w/o path 
         'is  specified,  the database will be created in your
         'application folder.

            Dim sCreateString As String
            sCreateString = _
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               DatabaseFullPath
            cat.Create(sCreateString)

            bAns = True

        Catch Excep As System.Runtime.InteropServices.COMException
            bAns = False
            'do whatever else you need to do here, log, 
            'msgbox etc.
        Finally
            cat = Nothing
        End Try
        Return bAns
    End Function


    DEMO
    ====


    '      If CreateAccessDatabase("F:\test.mdb") = True Then
    '           MsgBox("Database Created")
    '      Else
    '           MsgBox("Database Creation Failed")
    '      End If