SQL Server and Access data read and write

1.ways to query data in Access:

SELECT * from OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d-atabase=c:/db2.mdb ', ' select * from Serv_user ')

or select * from OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:/db2.mdb "; User id=admin; Password= ')

2.to write data from SQL Server to access:

Insert into OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d atabase=c:/db2.mdb ', ' SELECT * from Accee table ')
SELECT * From SQL Server table

or with Bcpmaster. xp_cmdshell ' bcp ' serv-htjs.dbo.serv_user "out" C:/db3.mdb "-c-q-S". "-U" sa "-P" sa "

The main difference is that OPENROWSET requires MDB and table presence, and BCP generates the MDB when it does not exist.

3.to write data from access to SQL Server:

With the basics, it's easy.

INSERT INTO SQL Server table select * FROM
OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d atabase=c:/db2.mdb ', ' SELECT * from Accee table ')

or with bcp

Master.. xp_cmdshell ' bcp ' Serv-htjs.dbo.serv_user "in" C:/db3.mdb "-c-q-S". "-U" sa "-P" sa "

4.Delete Access data:

Delete from OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d-atabase=c:/db2.mdb ', ' select * from Serv_user ')
where lock=0

5.To Modify Access data:

Update OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d atabase=c:/db2.mdb ', ' select * from Serv_user ')
Set Lock=1

SQL Server and Access data read and write