asked on

Automated process to export SQL database and convert into MS access 2007 database

I would like to know if there's anyway to automatically export & convert or automatically convert all the data on our SQL server to Access database on different machine on the network or there's a possible free software or paid program that does so.

Our financial department manager would like to daily get the data from SQL server's certain database to MS access.
Any suggestion is appreciated.
Thanks.

Bodestone

Might be difficult to export to Access 2007 from SQL Server 2000

If you can create an access 2003 database and the relevant tables to hold the data then you can either use the data export wizard and save the DTS package or so somethign similar to the following:

IF EXISTS (select * from master.dbo.sysservers WHERE srvname = 'MyDB') BEGIN SP_dropserver 'MyDB' END
GO
sp_addlinkedserver 'MyDB', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:\MyDB.mdb'
GO

DELETE MyDB...MyTable1
INSERT MyDB...MyTable1(Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM dbo.Mytable1

Open in new window

Mohammed Hamada

ASKER

Dear bodestone
I tried your comment, However I'm not really familiar with SQL or Access and it never works for me when I choose the destination as I don't know the steps exactly !!!
Should I choose MS access treiber MDB or Microsoft access (The key sign) !! Whenever I choose file name I have to enter username and password? I donno which are these belonging to ?

I'm attaching these below, in addition our financial manager have tried to use MS access 2007 linking method to SQL DATAbase to automatically update the new MS access DB but it returns an error as below.

Thanks
source.jpg
destination.jpg
error.jpg

ASKER CERTIFIED SOLUTION

Bodestone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial