I need to write an Access 97 .mdb file

Question

I need to export data from a SQL server 2005 DB to an Access 97 .mdb file. The client that needs it, needs it to be Access 97 because the system that they're importing it into requires Access 97 file format (don't get me started). Any suggestions how to write an old-timey Access file from SQL or .Net (or VB6 or Ruby or Python..)?

Thanks in advance, Lee

Answer 1

I'd let Sql 2005 do it for you.

In the Sql Management Stuidio, right-click on your source database, then Tasks, then Export Data. You can use this to export directly into your Access database, just follow the prompts. Or you can output it to a file format you can use to put into Access.

Answer 2

This might give you a starting point. And this article is a bit old, but you might be able to pick up something. I can only find those using Jet 4.0 which is compatible w/ Access 2000 as in the previous article. Using the MS Access driver might give you what you want.

After you created the database, use regular ODBC / OLE DB related stuffs in ADO.NET to create your table and populate them w/ your data.

Answer 3

I think it's crazy to do it from SQL Server. Just create an ODBC DSN for your SQL Server and import the tables into your Access 97 MDB and be done with it. The only reason you might want to do it otherwise is if you want to automate it and do it repeatedly, but that can be automated in Access, too (TransferDatabase can do ODBC imports), and will take only as many lines of code as there are tables to import.

Answer 4

This is a great question! I've actually wanted to be able to do this kind of thing in a programmatic way, but in the past I've had nothing but trouble coming up with it. However, have matured a bit in my .NET skills over the years, I thought I would take a shot at writing a solution that could be executed as a Console app. This can be implemented either as a scheduled task on the windows server or sql server (using the Sql Server agent). I don't see why this couldn't be automated from the Sql Server without the following code, but I really had fun with this, so I just have to put it out there. The table in both Sql and Access is a list of dogs, with an ID, a name, a breed, and a color. Generic stuff. This actually works on my desktop between a local instance of Sql Server and Access (2007, but I don't know why it wouldn't work with 97). Please feel free to critique.

BTW, has the following:

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

Here:

static void Main(string[] args)
{
    SqlConnectionStringBuilder cstrbuilder = new SqlConnectionStringBuilder();
    cstrbuilder.DataSource = "localhost";
    cstrbuilder.UserID = "frogmorton";
    cstrbuilder.Password = "lillypad99";
    cstrbuilder.InitialCatalog = "Dogs";
    SqlConnection sconn = new SqlConnection(cstrbuilder.ToString());
    sconn.Open();
    SqlCommand scmd = new SqlCommand("select * from Dogs", sconn);

    SqlDataReader reader = scmd.ExecuteReader();

    if (reader.HasRows)
    {

        OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
        sb.Provider = "Microsoft.Jet.OLEDB.4.0";
        sb.PersistSecurityInfo = false;
        sb.DataSource = @"C:\A\StackOverflog\DogBase.mdb";
        OleDbConnection conn = new OleDbConnection(sb.ToString());
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("Delete from Dogs", conn);
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
        conn.Close();

        OleDbConnection conn2 = new OleDbConnection(sb.ToString());
        conn2.Open();
        OleDbCommand icmd = new OleDbCommand("Insert into dogs (DogID, DogName, Breed, Color) values ({0}, '{1}', '{2}', '{3}');", conn2);
        icmd.CommandType = CommandType.Text;

        while (reader.Read())
        {
            string insertCommandString =
                String.Format("Insert into dogs (DogID, DogName, Breed, Color) values ({0}, '{1}', '{2}', '{3}');"
                , reader.GetInt32(0)
                , reader.GetString(1)
                , reader.GetString(2)
                , reader.GetString(3)
                );
            icmd.CommandText = insertCommandString;
            icmd.ExecuteNonQuery();

        }
        conn2.Close();
    }

    sconn.Close();
}

Answer 5

What you need to do is export into an Access file for whatever Access version you have installed (as long as it's 2000...2003; Access 2007 can't write to Access 97 files). I assume you already know how to do this.

Then you can create an Access object via COM and ask it to convert your new .mdb file into a new Access 97 database. In VBScript, the code looks like this (adjust as necessary if you're using VBA, VB.Net, or another language):

const acFileFormatAccess97 = 8

dim app
set app = CreateObject("Access.Application")
app.ConvertAccessProject "y:\mydatabase.mdb", "y:\mydatabase97.mdb", acFileFormatAccess97

If you have Access 97 installed, the above command won't work, because Access didn't have the ConvertAccessProject function in that version. Of course, you don't need to convert the file in that case anyway.