I need to write an Access 97.mdb file

Question

I need to export data from a SQL Server 2005 database to an Access 97.mdb file. The client that needs it needs Access 97, because the system they import it into requires the Access 97 file format (don't run me). Any suggestions on how to write an old-fashioned access file from SQL or .NET (or VB6 or Ruby or Python ..)?

Thanks in advance, Lee

Answer 1

What you need to do is export to an access file for any version of Access that you installed (before 2000 ... 2003; Access 2007 cannot write Access 97 files). I suppose you already know how to do this.

Access COM .mdb Access 97. VBScript ( , VBA, VB.Net ):

const acFileFormatAccess97 = 8

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

Access 97, , Access ConvertAccessProject . , .

Answer 2

Sql 2005 .

Sql Management Stuidio , "", " ". Access, . , Access.

Answer 3

. , , , - . , Jet 4.0, Access 2000, . MS Access , .

ODBC/OLE DB ADO.NET, .

Answer 4

, SQL Server. DSN ODBC SQL Server MDB Access 97 . , , , , - , , Access (TransferDatabase ODBC), , .

Answer 5

! , , . , .NET, , , . Windows sql ( Sql Server). , Sql , , . Sql Access - , , . . Sql Server Access (2007), , 97). , .

BTW, :

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

:

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 6

- PInvoke CREATE_DBV3 SqlConfigDataSource(). , JetSqlUtil.cs OSS PlaneDisaster.NET:

    #region PInvoke
    private enum ODBC_Constants : int {
        ODBC_ADD_DSN = 1,
        ODBC_CONFIG_DSN,
        ODBC_REMOVE_DSN,
        ODBC_ADD_SYS_DSN,
        ODBC_CONFIG_SYS_DSN,
        ODBC_REMOVE_SYS_DSN,
        ODBC_REMOVE_DEFAULT_DSN,
    }

    private enum SQL_RETURN_CODE : int
    {
        SQL_ERROR = -1,
        SQL_INVALID_HANDLE = -2,
        SQL_SUCCESS = 0,
        SQL_SUCCESS_WITH_INFO = 1,
        SQL_STILL_EXECUTING = 2,
        SQL_NEED_DATA = 99,
        SQL_NO_DATA = 100
    }

    [DllImport("ODBCCP32.DLL",CharSet=CharSet.Unicode, SetLastError=true)]
    private static extern int SQLConfigDataSource (int hwndParent, ODBC_Constants fRequest, string lpszDriver, string lpszAttributes);

    [DllImport("ODBCCP32.DLL", CharSet = CharSet.Auto)]
    private static extern SQL_RETURN_CODE SQLInstallerError(int iError, ref int pfErrorCode, StringBuilder lpszErrorMsg, int cbErrorMsgMax, ref int pcbErrorMsg);
    #endregion


    internal static string GetOdbcProviderName()
    {
        if (string.IsNullOrEmpty(OdbcProviderName))
        {
            var odbcRegKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBCINST.INI\\ODBC Drivers", false);
            var drivers = new List<string>(odbcRegKey.GetValueNames());
            if (drivers.Contains("Microsoft Access Driver (*.mdb, *.accdb)"))
            {
                OdbcProviderName = "Microsoft Access Driver (*.mdb, *.accdb)";
            }
            else if (drivers.Contains("Microsoft Access Driver (*.mdb)"))
            {
                OdbcProviderName = "Microsoft Access Driver (*.mdb)";
            }
            else
            {
                //TODO: Condider checking for 32 versus 64 bit.
                //TODO: Find a better exception type. http://stackoverflow.com/questions/7221703/what-is-the-proper-exception-to-throw-if-an-odbc-driver-cannot-be-found
                throw new InvalidOperationException("Cannot find an ODBC driver for Microsoft Access. Please download the Microsoft Access Database Engine 2010 Redistributable. http://www.microsoft.com/download/en/details.aspx?id=13255");
            }
        }



    /// <summary>
    /// Creates an Access 2003 database. If the filename specified exists it is 
    /// overwritten.
    /// </summary>
    /// <param name="fileName">The name of the databse to create.</param>
    /// <param name="version">The version of the database to create.</param>
    public static void CreateMDB (string fileName, AccessDbVersion version = AccessDbVersion.Access2003) {
        ;
        if (File.Exists(fileName)) {
            File.Delete(fileName);
        }

        string command = "";
        switch (version)
        {
            case AccessDbVersion.Access95:
                command = "CREATE_DBV3";
                break;
            case AccessDbVersion.Access2000:
                command = "CREATE_DBV4";
                break;
            case AccessDbVersion.Access2003:
                command = "CREATE_DB";
                break;
        }

        string attributes = String.Format("{0}=\"{1}\" General\0", command, fileName);
        int retCode = SQLConfigDataSource 
            (0, ODBC_Constants.ODBC_ADD_DSN,
             GetOdbcProviderName(), attributes);
        if (retCode == 0)
        {
            int errorCode = 0 ;
            int  resizeErrorMesg = 0 ;
            var sbError = new StringBuilder(512);
            SQLInstallerError(1, ref errorCode, sbError, sbError.MaxCapacity, ref resizeErrorMesg);
            throw new ApplicationException(string.Format("Cannot create file: {0}. Error: {1}", fileName, sbError));
        }
    }

64- SQL-, 64- Office 2010 Microsoft Access Database Engine 2010 Redistributable.