访问远程Access数据库,使用SqlServer 2005的连接服务器实现

用SqlServer 连接服务器实现，c#Ado.net 编程实现。

连接服务器的建立及一些语句如下：

--Create a linked server.
EXEC sp_addlinkedserver 
   @server = 'txtsrv1', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'D:\My Documents\Visual Studio 2008\Projects\gcmsWeb\gcmsWeb\gcmsWeb\test.mdb'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv1, FALSE, sa, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv1
GO

--Query one of the tables: file1#txt
--using a four-part name. 
SELECT * 
FROM txtsrv1...[department]

SELECT *
FROM txtsrv1...[testtable]



SELECT *
FROM OPENQUERY(txtsrv1, 'SELECT * from testtable') as queyr1;

--Insert data
INSERT OPENQUERY(txtsrv1,'SELECT col1,col2 FROM    testtable') values('REMOTTING1','REMOTTIN1')
insert into txtsrv1...testtable values ('remottin2','remottig2')
insert into txtsrv1...testtable values ( '123', '456')
 
--update data
UPDATE OPENQUERY (txtsrv1, 'SELECT col2 FROM testtable where col1=id') SET col2 = 'ADifferentName',id='33'
update txtsrv1...testtable set col1='456' where col2='33'

--delete data
DELETE OPENQUERY (txtsrv1, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
delete from txtsrv1...testtable where col1='33'


--Delete link server login user

EXEC sp_droplinkedsrvlogin 'txtsrv1', 'sa'

--Dlete link server
EXEC   sp_dropserver     'txtsrv1'  

--Query all link server 
EXEC sp_helpserver;

 

 

 

 数据库模型建立：

 

     /// <summary>
    /// 实体类testtable 。(属性说明自动提取数据库字段的描述信息)
    /// </summary>
    [Serializable]
    public class testtable
    {
        public testtable()
        {}
        #region Model
        private string _col1;
        private string _col2;
        /// <summary>
        /// 
        /// </summary>
        public string col1
        {
            set{ _col1=value;}
            get{return _col1;}
        }
        /// <summary>
        /// 
        /// </summary>
        public string col2
        {
            set{ _col2=value;}
            get{return _col2;}
        }
        #endregion Model

    }
    

 

 

 数据库访问类代码实现：

    /// <summary>
    /// 数据访问类testtable。
    /// </summary>
    public class RemottesttableDAL
    {
        public RemottesttableDAL()
        { }
        #region  成员方法



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(testtable model)
        {
            //insert into txtsrv1...testtable values ('remottin2','remottig2')
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into txtsrv1...testtable ");
            strSql.Append("values (");
            strSql.Append(string.Format(" '{0}', '{1}'",model.col1,model.col2));
            strSql.Append(")");
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(testtable model)
        {
            //update txtsrv1...testtable set col1='456' where col2='33'
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update txtsrv1...testtable ");
            strSql.Append(string.Format("set col1='{0}' ",model.col1));
            strSql.Append("where ");
            strSql.Append(string.Format("col2='{0}'",model.col2));
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(string id)
        {
            //delete from txtsrv1...testtable where col1='33'
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from txtsrv1...testtable ");
            strSql.Append(string.Format("where col1 ='{0}'", id));
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public testtable GetModel(string id)
        {
            //SELECT * FROM txtsrv1...[testtable] where col1='11'

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from txtsrv1...testtable ");
            strSql.Append(string.Format("where col1 ='{0}'",id));

            testtable model = new testtable();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.col1 = ds.Tables[0].Rows[0]["col1"].ToString();
                model.col2 = ds.Tables[0].Rows[0]["col2"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<testtable> GetList(string strWhere)
        {
            //SELECT * FROM txtsrv1...[testtable]
            List<testtable> listTable = new List<testtable>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from txtsrv1...testtable ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet set = DbHelperSQL.Query(strSql.ToString());
            int rowCount = set.Tables[0].Rows.Count;
            DataTable setTable = set.Tables[0];
            if (rowCount > 0)
            {
                for (int i = 0; i < rowCount; i++)
                {
                    testtable table = new testtable();
                    table.col1 = setTable.Rows[i]["col1"].ToString();
                    table.col2 = setTable.Rows[i]["col2"].ToString();
                    listTable.Add(table);
                }
            }
            return listTable;
        }

        #endregion  成员方法
    }

 

 

  注意的一点：

D:\My Documents\Visual Studio 2008\Projects\gcmsWeb\gcmsWeb\gcmsWeb\

此处，Access存放的目录，必须给添加Everyone用户的读写权限。负责只能查询，不能修改。

源代码：

 /Files/csharponworking/LinkServerAccess.rar