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.
LVL 24
Mohammed HamadaSenior IT ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
sp_addlinkedserver 'MyDB', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:\MyDB.mdb'

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

Open in new window

Mohammed HamadaSenior IT ConsultantAuthor Commented:
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.

Yes, I am not sure if it is possible to connect to an access 2007 database from SQL 2000. If so then you would need to install additional things and I do not know what they are.

You would really need to try and create an access 2003 database (filename.mdb).
Once a blank database is created then it can be re-used. If it is then sent to someone with Access 2007 they will be able to open it.

Step 1: Create a blank Access 2003 database and make a copy locally on the SQL server box.
Step 2: Run the export wizard and choose that database as the destination (Access with a key).
Step 3: Chose Copy Tables and Views
Step 4: Tick the relevant tables then for each table click the button in the Transform column.
Step 5: Ensure Drop and recreate destination table is ticked.
Step 6: Tick the Schedule DTS Package for later and the 3 dot button (leave Run immediately on)
Step 7: Choose your schedule for the export.
Step 8: Give the DTS package a sensible name.
Step 9: Run it.

You will get errors this time round since there are no tables in the blank database to drop but it should populate the data.
Make a copy of the database and open it to check. (Viewing of data should always be done on a copy just to make sure the main DB is not open by someone when trying to export)
You will now have a package in Data Transformation Services which you can execute manually.
There will also be a job in Management\SQL Server Agent which you can run manually.

Note, if you open

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.