Backup SQL Server data to Access

[Bob]

Interesting client request, they want to be able to backup their SQL Server
data to an Access database on their local machine. They want this all to be
done programmatically. Any thoughts on the best way to do this?

Thanks!

[Carl Prothman]

"Bob" <bdu...@soft-ally.com> wrote in message news:#GRKkScJBHA.1940@tkmsftngp04...

> Interesting client request, they want to be able to backup their SQL Server
> data to an Access database on their local machine. They want this all to be
> done programmatically. Any thoughts on the best way to do this?
>

Bob,
Use a "SELECT INTO" sql command as follows:

'*********
Dim oConn As ADODB.Connection
Dim sSQL As String

' Create and open a new connection to local backup MDB
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\Backup.mdb;"

' Select all records from Products table in Northwind SQL Server DB
' and place them into a Products_Backup in local backup MDB
sSQL = "SELECT * INTO Products_Backup FROM "
sSQL = sSQL & "[odbc;Driver={SQL Server};Server=(local);"
sSQL = sSQL & "Database=northwind;Uid=myUsername;Pwd=myPassword;].Products"
oConn.Execute sSQL, , adExecuteNoRecords
'*********
--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

[chris]

I'm afraid it will be quite complicated to programmatically implement this
task. For example, you need to determine whether the destination Access
database is already existed; determine whether the table already exists;
whether you want to drop and recreate all the tables and records or just
want to update the parts that have been changed...

In SQL Server Enterprise Manager, select the database you want to backup,
click Tools | Data Transfirmation Services | Export Data, you will see a
Wizard that can be used to export data to kinds of destinations including
Access database. You can have a look at this wizard and will understand how
many tasks you will have to consider if you want to programmatically
implement the same function.

You may want to consider contact a Microsoft Certified Solution Provider
for help on this issue. For detailed information about Microsoft Certified
Solution Providers, please see the following page on the World Wide Web:
http://mcspreferral.microsoft.com/

[Bob]

Hi Chris!
I use DTS all the time but that's not an option. I already have the
code to detect that the file exists and if the table already exists as well.
If it exists I drop the table in Access but am getting an error when I then
try to execute my CREATE TABLE statement immediately after the delete:

err.number = -2147217865
err.Description = Cannot find table or constraint.

If I shutdown the app and restart, the CREATE will work.

I have most of the code written including creating the tables and INSERT
statements (which I dynamically build using the syscolumns table). I'm just
afraid all the process will make it extremely slow. I was hoping for
something like Carl suggested but wasn't aware you could run the SELECT INTO
across databases. I'm going to try that out and see if I have any luck with
it.

Thanks!

"chris" <chri...@microsoft.com> wrote in message
news:se1spUj...@cppssbbsa01.microsoft.com...

[Bob]

Hi Carl!
I was hoping for something like this. I wasn't sure you could go across
databases with a single select. I'll let you know how it works out.

Thanks!
Bob

"Carl Prothman" <car...@spamcop.net> wrote in message
news:uvWFFadJBHA.860@tkmsftngp02...

[Bob]

You are the king!

That's exactly what I was looking for.

Thanks!
Bob

"Carl Prothman" <car...@spamcop.net> wrote in message
news:uvWFFadJBHA.860@tkmsftngp02...

[Bob]

One more quick question....is there a way I can use a dsn name as opposed
to:

"[odbc;Driver={SQL Server};Server=(local);"

Thanks again.
Bob

"Carl Prothman" <car...@spamcop.net> wrote in message
news:uvWFFadJBHA.860@tkmsftngp02...

[Carl Prothman]

"Bob" <bdu...@soft-ally.com> wrote in message news:eqQg3zmJBHA.1356@tkmsftngp05...

> One more quick question....is there a way I can use a dsn name as opposed
> to:
> "[odbc;Driver={SQL Server};Server=(local);"

Bob,
Yes try the following connection string:
sSQL = sSQL & "[odbc;DSN=northwind;UID=myUsername;PWD=myPassword;].Products"

For other connection string examples, see:
http://www.able-consulting.com/ADO_Conn.htm

[Bob]

thanks again!
Bob

"Carl Prothman" <car...@spamcop.net> wrote in message

news:OxmJBenJBHA.1096@tkmsftngp04...

[David Yuan]

yes you can use preset DSNs. Suppose that you have created a DSN for the
SQL server named DBToBackup, you can change the code like this:

sSQL = "SELECT * INTO BackupTable FROM "
sSQL = sSQL & "[odbc;DSN=DBToBackup;]"
sSQL = sSQL & ".TableToBackup"

[David Yuan]

please refer to the other thread.

[David Yuan]