Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Back up SQL database to Access


I have a small SQL database running on my web site.  I want to back it
up on a weekly basis to an Access file and then download that file.

I can write code to open both the SQL connection and the Access file
and copy data in a loop, but someone told me there is a more efficient
way to do it.  But I haven't been able to find any example code.

Can anyone help?

Thanks!

Hi

You could use DTS package to export all tables to access db, but don't know
how to export views and procs, may be you could build a massive DTS system
to export all the views and procs rebuilt in Access

Regards
VT
Knowledge is power, share it....
http://oneplace4sql.blogspot.com/

"KoDell" <dnavarr@gmail.com> wrote in message

news:1181142431.421654.163650@p47g2000hsd.googlegroups.com...

You can set up DSN pointing to SQL database and create script in Access to
import all tables or subset of them using odbc.

Or export SQL data as txt/csv files and import them in Access - you'll need
script too.

I believe ODBC way is more convenient as the code could check for various
conditions and could be scheduled.

HTH
Alex

"KoDell" <dnavarr@gmail.com> wrote in message

news:1181142431.421654.163650@p47g2000hsd.googlegroups.com...

Thanks.  My goal is to write the code in VBScript and run it as an ASP
page that is part of my site admin.  So I can just go to the page,
click on "backup DB" and it will return a downloadable Access DB file.

I wrote a quick & dirty script that simply creates a copy of a blank
access DB, then opens each table in the SQL db and copies all of the
data to the access DB.  Then it gives me a link to download the DB
file.

While this works, the code internally just does a "SELECT *" from all
of the tables in the database and then copies everything into the
Access DB in a DO/LOOP.

I can't seem to find any sample DTS code to use and I've read in
various messages that it's a better/faster way to go.

-----------------------------------------------Reply-----------------------------------------------

On 6 Jun, 16:07, KoDell <dnavarr@gmail.com> wrote:

> I have a small SQL database running on my web site.  I want to back it
> up on a weekly basis to an Access file and then download that file.

> I can write code to open both the SQL connection and the Access file
> and copy data in a loop, but someone told me there is a more efficient
> way to do it.  But I haven't been able to find any example code.

> Can anyone help?

> Thanks!

Why not just copy the SQL database and then link Access to it?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc