By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,224 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,224 IT Pros & Developers. It's quick & easy.

Backing Up Ones SQL-Server Data as Jet

P: n/a
I have a tiny db on a remote sql server. The owner of the sever backs up the
db daily, but these backups are not freely available to me. I want my data
(only my data) backed up in some form that is accessible to me.

I have an adp. It exists only to connect to the remote db.
I have an mdb. It has a form. The form opens on the dbís opening. This is the
code behind the form. It zaps any tables in the mdb. It examines the tables
in the adp. It imports them. It copies them to a backup device. It closes the
db and the access application.

I have not tried this with any medium or large sized db, only with a tiny db.
I expect it would take too long with a larger db; for a few tables and a few
hundred records it takes about 11 seconds.

I open the db through Windows scheduling every Sunday at 04:00, os even if it
took an hour I would not be inconvenienced. It has worked without incident
for several weeks now. I could modify it to back up SPROCs, VIEWs and
FUNCTIONs but I donít need that.

So, it gives me my data in JET, without intervention.
(Gee, I hope I haven't posted this before, but I think not.)
Option Compare Database
Option Explicit
Const ADPFile As String = "F:\Access\SomeBooks.adp"
Const Backupfile As String = "E:\SomeBooks.mdb"

Const b As String = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=DB_A0A0A0;" _
& "DATA SOURCE=Some.Remote.SQL.Server"
Const p As String = "X9X9X9"
Const u As String = "UserName"

Private Sub BackupSQLTablesAsJET()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset

' zap old tables
Set c = New ADODB.Connection
With c
.Open CurrentProject.BaseConnectionString
End With
' get table names
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))
With r
Do While Not .EOF
CurrentProject.Connection.Execute ("DROP TABLE " & !TABLE_NAME)
.MoveNext
Loop
End With

' refresh TableDefs
DBEngine(0)(0).TableDefs.Refresh

' set persist security information
' in the adp file to true
SecurityInformation "TRUE"

' connect to the ADP file
With c
.Close
.Open b & ";USER ID=" & u & ";PASSWORD=" & p
End With

' get table names
Set r = c.OpenSchema( _
adSchemaTables, Array(Empty, Empty, Empty, "Table"))

' import the SQL tables (as JET)
With r
Do While Not .EOF
If Left(!TABLE_NAME, 2) <> "dt" Then _
DoCmd.TransferDatabase acImport, "Microsoft Access", _
ADPFile, acTable, !TABLE_NAME, !TABLE_NAME, False
.MoveNext
Loop
.Close
End With

' set persist security information
' in the adp file to false
SecurityInformation "FALSE"

' copy the tables to the BackUp Device
SaveAsText 6, "", Backupfile

DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Close()
Application.Quit
End Sub

Private Sub Form_Open(Cancel As Integer)
BackupSQLTablesAsJET
End Sub

Private Sub SecurityInformation(ByVal vPERSIST As String)
Dim a As Access.Application
Set a = New Access.Application
With a
.OpenAccessProject ADPFile
With .CurrentProject
If .IsConnected Then .CloseConnection
.OpenConnection Replace(b, "FALSE", vPERSIST), u, p
End With
.Quit
End With
End Sub

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On 17 Apr 2004 15:17:21 GMT, Lyle Fairfield
<Mi************@Invalid.Com> wrote:

Neat. But why not use a DTS package in SQL Server? No code to write.
But then, little learned.

-Tom.
<clip>
Nov 12 '05 #2

P: n/a
Tom van Stiphout <to*****@no.spam.cox.net> wrote in
news:cf********************************@4ax.com:
On 17 Apr 2004 15:17:21 GMT, Lyle Fairfield
<Mi************@Invalid.Com> wrote:

Neat. But why not use a DTS package in SQL Server? No code to write.


That's true. But I find DTS fails on the tiniest of errors. And I am always
struggling with it to import just what I want, (all of that), and nothing
else. I've erred in importing all the logins from the remote server several
times; it's no fun identifying and removing several hundred extra logins on
the local sever. But probably I should just emphasize my efforts there.

Also the idea of having my data in a form entirely separate from SQL server
appeals to me.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #3

P: n/a
Lyle Fairfield <Mi************@Invalid.Com> wrote:
I have a tiny db on a remote sql server. The owner of the sever backs up the
db daily, but these backups are not freely available to me. I want my data
(only my data) backed up in some form that is accessible to me.
Nice idea. Thanks for posting the code.
So, it gives me my data in JET, without intervention.


FWIW you could download MSDE (sorta SQL Server lite) for free. Or purchase the SQL
Server Developer Tools for $50.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #4

P: n/a
Tony Toews <tt****@telusplanet.net> wrote in
news:q8********************************@4ax.com:
Lyle Fairfield <Mi************@Invalid.Com> wrote:
I have a tiny db on a remote sql server. The owner of the sever backs up
the db daily, but these backups are not freely available to me. I want
my data (only my data) backed up in some form that is accessible to me.


Nice idea. Thanks for posting the code.
So, it gives me my data in JET, without intervention.


FWIW you could download MSDE (sorta SQL Server lite) for free. Or
purchase the SQL Server Developer Tools for $50.


And your point is?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5

P: n/a
Lyle Fairfield <Mi************@Invalid.Com> wrote:
FWIW you could download MSDE (sorta SQL Server lite) for free. Or
purchase the SQL Server Developer Tools for $50.


And your point is?


If you wanted to copy your data to SQL Server or work on the software on your own
system with exactly the same environment.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #6

P: n/a
Tony Toews <tt****@telusplanet.net> wrote in
news:38********************************@4ax.com:
Lyle Fairfield <Mi************@Invalid.Com> wrote:
FWIW you could download MSDE (sorta SQL Server lite) for free. Or
purchase the SQL Server Developer Tools for $50.


And your point is?


If you wanted to copy your data to SQL Server or work on the software on
your own system with exactly the same environment.


And you thought I was using ADPs but was unaware of MSDE, SQL and DTS?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #7

P: n/a
On Apr 23 2004, 05:11 pm, Lyle Fairfield <Mi************@Invalid.Com> wrote
in news:Xn*******************@130.133.1.4:
Tony Toews <tt****@telusplanet.net> wrote in
news:38********************************@4ax.com:
Lyle Fairfield <Mi************@Invalid.Com> wrote:
FWIW you could download MSDE (sorta SQL Server lite) for free. Or
purchase the SQL Server Developer Tools for $50.

And your point is?


If you wanted to copy your data to SQL Server or work on the software on
your own system with exactly the same environment.


And you thought I was using ADPs but was unaware of MSDE, SQL and DTS?


BTW, Lyle, did you know that when you are working with an ADP, you are
actually connecting to a SQL Server, not just any SQL? <gdr>

--
remove a 9 to reply by email
Nov 12 '05 #8

P: n/a
Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:
On Apr 23 2004, 05:11 pm, Lyle Fairfield <Mi************@Invalid.Com>
wrote in news:Xn*******************@130.133.1.4:
Tony Toews <tt****@telusplanet.net> wrote in
news:38********************************@4ax.com:
Lyle Fairfield <Mi************@Invalid.Com> wrote:

> FWIW you could download MSDE (sorta SQL Server lite) for free. Or
> purchase the SQL Server Developer Tools for $50.

And your point is?

If you wanted to copy your data to SQL Server or work on the software
on your own system with exactly the same environment.


And you thought I was using ADPs but was unaware of MSDE, SQL and DTS?


BTW, Lyle, did you know that when you are working with an ADP, you are
actually connecting to a SQL Server, not just any SQL? <gdr>


Really? That' so ... so ...
uhhh ...
wonderful!
Dimitri.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #9

P: n/a
On Apr 23 2004, 08:30 pm, Lyle Fairfield <Mi************@Invalid.Com> wrote
in news:Xn*******************@130.133.1.4:
Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@127.0.0.1:
On Apr 23 2004, 05:11 pm, Lyle Fairfield <Mi************@Invalid.Com>
wrote in news:Xn*******************@130.133.1.4:
Tony Toews <tt****@telusplanet.net> wrote in
news:38********************************@4ax.com:

Lyle Fairfield <Mi************@Invalid.Com> wrote:

>> FWIW you could download MSDE (sorta SQL Server lite) for free. Or
>> purchase the SQL Server Developer Tools for $50.
>
>And your point is?

If you wanted to copy your data to SQL Server or work on the software
on your own system with exactly the same environment.

And you thought I was using ADPs but was unaware of MSDE, SQL and DTS?


BTW, Lyle, did you know that when you are working with an ADP, you are
actually connecting to a SQL Server, not just any SQL? <gdr>


Really? That' so ... so ...
uhhh ...
wonderful!
Dimitri.


Hey, it's Friday night, I am allowed to be sillier than usual...

--
remove a 9 to reply by email
Nov 12 '05 #10

P: n/a
Lyle Fairfield <Mi************@Invalid.Com> wrote:
FWIW you could download MSDE (sorta SQL Server lite) for free. Or
purchase the SQL Server Developer Tools for $50.

And your point is?


If you wanted to copy your data to SQL Server or work on the software on
your own system with exactly the same environment.


And you thought I was using ADPs but was unaware of MSDE, SQL and DTS?


Yup, you're right. I had missed that when I had replied and hadn't thought things
through.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.