[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

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.
0
Mohammed Hamada
Asked:
Mohammed Hamada
  • 2
1 Solution
 
BodestoneCommented:
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

0
 
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.

Thanks
source.jpg
destination.jpg
error.jpg
0
 
BodestoneCommented:
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
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now