VB Forum / Database Access / April 2005

hi AUDI,

if you like to use a mixed scenario, with JET and SQL Server data source,
you can have a look at a linked server solution with an SQL Server
connections...

you can add a linked server pointing to the Access database like following,
where a pesudo copy of the original JET table is created an populated from
the original data

SET NOCOUNT ON
USE master
GO
-- adding linked server
EXEC sp_addlinkedserver
  @server = 'my_JET_db',
  @provider = 'Microsoft.Jet.OLEDB.4.0',
  @srvproduct = 'OLE DB Provider for Jet',
  @datasrc = 'D:\NWIND.MDB' -- point to Nortwind JET database
GO

-- use your database.. here just define a new table in tempdb
USE tempdb
GO
CREATE TABLE dbo.MSDE_customers (
CustomerID VARCHAR(60) PRIMARY KEY ,
CompanyName VARCHAR(60) ,
ContactName VARCHAR(60) ,
ContactTitle VARCHAR(60) ,
Address VARCHAR(60) ,
City VARCHAR(60) ,
Region VARCHAR(60) ,
PostalCode VARCHAR(60) ,
Country VARCHAR(60) ,
Phone VARCHAR(60) ,
Fax VARCHAR(60)
)
GO
PRINT 'SELECT from the linked server Jet database'
SELECT TOP 10 * FROM my_JET_db...Customers

PRINT 'Import rows to the MSDE database via INSERT INTO'
INSERT INTO dbo.MSDE_customers SELECT * FROM my_JET_db...Customers

PRINT ''
PRINT 'SELECT from the MSDE database'
SELECT TOP 10 * FROM dbo.MSDE_customers
GO
-- cleanup
DROP TABLE dbo.MSDE_customers
GO
USE master
go
EXEC sp_dropserver 'my_JET_db', 'droplogins'

further information and relative synopsis about sp_addlinkedserver system
stored procedure can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_a
dda_8gqa.asp