2 Replies - 11198 Views - Last Post: 10 January 2008 - 05:36 PM Rate Topic: -----

#1 girasquid  Icon User is offline

  • Barbarbar
  • member icon

Reputation: 108
  • View blog
  • Posts: 1,825
  • Joined: 03-October 06

Export SQL Server Database to Access

Posted 10 January 2008 - 04:44 PM

Hello, all.

I am currently working on a project where I need to export a database stored within SQL Server into an empty Access database(filename.mdb).

I created a .dtsx file and loaded it in, changed the destination and source connection strings to the locations of the files I wanted, and then executed it - but it isn't outputting the SQL database into my access file, and I am getting an error about being unable to access a process. Here is my code:
		Application app = new Application();
		Package package = app.LoadPackage(Server.MapPath("COAA Export Access.dtsx"),null);
		Connections c = package.Connections;

		// create a copy of our database.
	FileStream fs = new FileStream(Server.MapPath("empty.mdb"), FileMode.Open);
		FileStream output = new FileStream(Server.MapPath("database.mdb"), FileMode.Create);
		int i;
		do
		{
			i = fs.ReadByte();
			if (i != -1) output.WriteByte((byte)i);
		} while (i != -1);

		fs.Close();
		output.Close();

		// change our source database and our destination database
		c["SourceConnectionOLEDB"].ConnectionString = ConfigurationManager.ConnectionStrings["COAAConnection"].ConnectionString;
		c["DestinationConnectionOLEDB"].ConnectionString = string.Format("Data Source={0};OLD DB SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;", Server.MapPath("database.mdb"));
		// and finally, execute the package to do the copy thing.
		package.Execute();



Does anyone know what I might be doing wrong?
Thanks,
Girasquid

Is This A Good Question/Topic? 0
  • +

Replies To: Export SQL Server Database to Access

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4318
  • View blog
  • Posts: 12,100
  • Joined: 18-April 07

Re: Export SQL Server Database to Access

Posted 10 January 2008 - 05:06 PM

Why aren't you just going to the SQL server and selecting "export" and set the destination to Access? They are both microsoft products so they do have that ability. Or do you not have access to the machine SQL is installed on?
Was This Post Helpful? 0
  • +
  • -

#3 girasquid  Icon User is offline

  • Barbarbar
  • member icon

Reputation: 108
  • View blog
  • Posts: 1,825
  • Joined: 03-October 06

Re: Export SQL Server Database to Access

Posted 10 January 2008 - 05:36 PM

I don't have access to the machine - this is supposed to be getting accomplished through a web control panel.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1