Copying data from an access .mdb file to a SQL database

Question

We have a customer who is running a proprietary application, that they want to extract some data from so they can display it on a Web site. The application has an export capability that writes the needed data to a MS Access .MDB file.

We wrote a simple program, using MS's JET drivers, that simply copied the relevant tables to a SqlServer database, where another program would run queries against them to collect what was needed. This worked fine for a number of years.

Then the vendor updated their proprietary application, and while the .MDB files look the same, and display the correct data when viewed in MS Access, what we are getting in our program when we pull records with the JET drivers is corrupt. It sometimes looks like the field alignments are wrong - a numeric field that should contain 23.40 will contain .23, I'm getting dates in the 2150's, etc.

My program, running on my machine, processing the same file works correctly. The same program, running on the servers where it needs to run, reads corrupt data.

I noticed that the version of the JET driver - msjetoledb40.dll - is higher, and the date newer, on my Windows 7 box, than on any of the servers we've tried it on. so I thought it might be a problem with an out-of-date driver. Unfortunately, the latest version of the JET drivers that is available is SP 8.0, which is the version we had installed, and which isn't working. Later versions are only available with OS installs.

So:

Is there any way of getting and installing more current JET drivers?

Are we being mislead by the version differences in the JET drivers? Is there something else that could be causing the problem, that we should be looking at?

Is there some other means or tool of extracting data from a MS Access .MDB file? (Note - this is a password-protected file, so we'd need a tool that could handle that).

Answer 1

SQL Server comes with different ways for importing data. You could always look at building a SSIS job that pulls the data in. You didn't mention what version of SQL Server it is, but you don't have to use a customer application to import the data in.

Answer 2

If you have a copy of Access, you can set up a DSN for SQL Server, then link tables from SQL Server to Access. (File | Get external data | Link, or something like that.) Having done that, you could use Access queries to push data up to SQL Server.

Answer 3

I actually have a VB ASP.NET website that uses an Access 2000 database as a backend. Granted, I use it only as a read-only file, but I'm sure writing to it would also work. You wouldn't need to do anything with SQLServer with this method. You can then write your own methods to access tables and queries in the database.

Public Class AccessDatabase
    Friend db As New OleDbConnection
    Private sPath As String

    Public Sub New(ByRef sPath As String)
        GetDatabase(sPath)
    End Sub

    'Use Server.MapPath("App_Data\WebContent.mdb") to load the database.
    Private Function GetDatabase(ByRef sPath As String) As OleDbConnection
        Try
            If db.State <> System.Data.ConnectionState.Open Then
                db = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath)
                db.Open()
            End If
        Catch e As Exception
            Throw New Exception("Error happened when opening " & sPath, e)
        End Try
        Return db
    End Function

    Public Sub Close()
        If db.State <> Data.ConnectionState.Closed Then
            db.Close()
        End If
    End Sub

    Protected Overrides Sub Finalize()
        Try
            If Not db Is Nothing Then
                If db.State <> Data.ConnectionState.Closed Then
                    db.Close()
                End If
            End If
        Catch ex As Exception
        End Try
    End Sub
End Class