473,230 Members | 1,432 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

Backing Up Ones SQL-Server Data as Jet

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
10 1687
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Eric | last post by:
I've taken a look at mysqlhotcopy and mysqldump but neither seem to do what I need them to do which is to backup only the data which has changed since the last backup. Is there a way to do this?...
9
by: J. Frank Parnell | last post by:
hello, i dont know asp at all, but i have been asked to backup a database that is used on a site which uses .aspx. i dont need to do anything with it, just copy it and send it along to someone...
9
by: Jerry Porter | last post by:
Is there a way to back up the design changes in a SQL Server database without backing up all the data? It's just test data at this point.
3
by: war_wheelan | last post by:
I am having a problem backing up my database and TLog files due to a lack of local diskspace. The db file is about 30GB and the TLog is about 20GB each on a different hard disk. Each disk doesn't...
5
by: Stewart Graefner | last post by:
I would like to know if an Access db can be backed up with the push of a command button. I work with extreamly lazy operators who despite crashing their db's still refuse to see the value in...
1
by: Martin Schmid | last post by:
I am developing using Visual C#.net. It appears that my web projects create files in both "My Documents\Visual Studio Projects" and ".\inetpub\webroot". Does backing up these files ensure that my...
0
by: Martin Schmid | last post by:
I am developing using Visual C#.net. It appears that my web projects create files in both "My Documents\Visual Studio Projects" and ".\inetpub\webroot". Does backing up these files ensure that my...
5
by: rdemyan via AccessMonster.com | last post by:
I have code in my front end that opens a form for backing up the front end. I'll give a brief description of what the form does: 1) When the backup form opens, it closes all open forms except for...
9
by: jim | last post by:
I'd like to get some .net sample code that shows me how to make a complete backup of a hard drive (like my C: drive) to another location (say my D: drive) while the C: drive is in use. ...
4
by: Joerg Battermann | last post by:
Hello there, does anyone know the precise naming conventions used for internal backing fields for automatic properties? Something official besides looking at the compiled assemblies that might...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.