Hi I have a split database and a code to backup the back end at the front end however I want the back end to backed up in a folder called "Backups" a the Back End and not at the front end. This is the code am using now to back up the destination at the front end. Please help me change the destination to the back end(Server) - Public Sub BackUpDatabase()
-
-
On Error GoTo Err_Handler
-
Dim oFSO As Object
-
Dim strDestination As String
-
Dim strSource As String
-
Dim path As String, name As String
-
-
path = CurrentProject.path
-
name = CurrentProject.name
-
Const conPATH_FILE_ACCESS_ERROR = 75
-
-
'Get the source of the back end
-
strSource = Split(Split(CurrentDb.TableDefs("AssessmentT").Connect, "Database=")(1), ";")(0)
-
-
'Determine backup destination
-
strDestination = path & "\" & Left(name, Len(name) - 6) & "_backup" & "_" & _
-
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".accdb"
-
-
'this removes a file created on the same day
-
If Dir(strDestination) <> "" Then
-
Kill strDestination
-
End If
-
-
'this creates a backup into destination path
-
If Dir(strDestination) = "" Then
-
'Flush the cache of the current database
-
DBEngine.Idle
-
-
'Create a file scripting object that will backup the db
-
Set oFSO = CreateObject("Scripting.FileSystemObject")
-
oFSO.CopyFile strSource, strDestination
-
Set oFSO = Nothing
-
-
'Compact the new file, ...
-
Name strDestination As strDestination & ".cpk"
-
DBEngine.CompactDatabase strDestination & ".cpk", strDestination
-
Kill strDestination & ".cpk"
-
-
'Notify users
-
MsgBox "Backup file '" & strDestination & "' has been created.", vbInformation, "Backup Completed!"
-
-
End If
-
-
Exit_Button_Backup:
-
-
Exit Sub
-
-
Err_Handler:
-
If Err.Number = conPATH_FILE_ACCESS_ERROR Then
-
MsgBox "The following Path, " & strDestination & ", already exists or there was an Error " & _
-
"accessing it!", vbExclamation, "Path/File Access Error"
-
Else
-
MsgBox Err.Description, vbExclamation, "Error Creating " & strDestination
-
-
End If
-
Resume Exit_Button_Backup
-
-
-
-
End Sub
-
9 8414 zmbd 5,501
Recognized Expert Moderator Expert
Line #17: Alter this to change the path to the location desired...
Line#17 is based on the "name" and "path" in lines #9 and #10
Line #9 would more than likely be the easiest place to put your network or other drive/path information
You can also use: Select a File or Folder using the FileDialog Object use the "folder" method to have the user select the location returning the value to line #9
One other thing I noted in your code is the use of reserved tokens as variables ("name" "path" etc...). I highly advise against doing this as it will eventually cause you a nightmare of troubleshooting; thus, you might want to review:: Access 2007 reserved words and symbols AllenBrowne- Problem names and reserved words in Access
Thanks Zmbd I get your point about changing the path to the network location. I tried this and it worked but I realize whenever I have to install the program on another computer, I have to change the code to suite that network path but I do not want it this way. I want the database to detect the source path and create the backup in the folder on the source path.I want the backup to be saved in the Back end folder irrespective of which machine I install on it on. I also appreciate your view about the reserved words. I would work on that. Thanks for the quick answer.
This was the code I was using initially and it works fine but whenever I have to install on another computer, I have to go into the code and change the code to suite the new network path so I modified it into the above code which is also limiting me to the front end - Public Function Button_Backup()
-
Dim str As String
-
Dim buf As String
-
Dim MD_Date As Variant
-
Dim fs As Object
-
Dim source As String
-
-
Const conPATH_FILE_ACCESS_ERROR = 75
-
On Error GoTo Backup_Button_Backup
-
-
'Where to backup to. Creates BACKUP folder is it does not exist
-
If MsgBox("Do You want to backup database?", vbYesNo + vbQuestion, "Falcon Systems") <> vbYes Then Exit Function
-
buf = path & "\\CREDIT-MANAGER\Falcon Systems\Data\Backups\"
-
MkDir buf
-
-
Resume Backup_Button_Backup
-
-
Backup_Button_Backup:
-
-
'Create a folder in BACKUP with YYYY-mm-dd hhmm-ss as the name
-
-
MD_Date = Format(Date, "yyyy-mm-dd ") & Format(Time, "hh-mm-ss")
-
str = path & "\\CREDIT-MANAGER\Falcon Systems\Data\Backups\" & MD_Date
-
-
'Where is the data to backup
-
source = path & "\\Credit-Manager\Falcon Systems\Data\"
-
MkDir str
-
Set fs = CreateObject("Scripting.FileSystemObject")
-
fs.CopyFile source & "*.accde", str
-
Set fs = Nothing
-
-
'Successful
-
MsgBox "A database backup has been stored by name" & vbCrLf & MD_Date & vbCrLf & "Successfully!", _
-
vbInformation, "Falcon Systems Backup Successful"
-
-
Exit_Button_Backup:
-
-
Exit Function
-
-
'Use this part if you have not used hh-mm-ss
-
-
Err_Button_Backup:
-
If Err.Number = conPATH_FILE_ACCESS_ERROR Then
-
MsgBox "The following Path, " & str & ", already exists or there was an Error " & _
-
"accessing it!", vbExclamation, "Path/File Access Error"
-
Else
-
MsgBox Err.Description, vbExclamation, "Error Creating " & str
-
-
End If
-
Resume Exit_Button_Backup
-
End Function
-
zmbd 5,501
Recognized Expert Moderator Expert
use the UNC naming convention.
For example:
My IT guys are very kind and my personal network drive is mapped to "Z:" however the UNC path is:
"\\servername\users\dept\qaqc\z_personaldrive\ "
(I also updated my first post :) )
Ok I'll try it. Thanks very much
But is there a way to use a path similar to this one: - strSource = Split(Split(CurrentDb.TableDefs("AssessmentT").Connect, "Database=")(1), ";")(0)
I tried using it as the destination but I received an error. Am thinking I have to modify this code to fall into the split folder instead of file
yashuaking ~ I know this is an older post, however for folks searching for an answer to your question I have two bits of code that may help.
I use a simple module to get the database backend path. The first one provides the exact directory where the backend resides. - Public Function GetDBBEPath() As String
-
On Error GoTo GetDBBEPath_Err
-
Dim strFullPath As String
-
strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("NameLinkedTable").Connect, 11)
-
GetDBBEPath = Left(strFullPath, InStrRev(strFullPath, "\"))
-
'for troubleshooting Debug.Print GetDBBEPath
-
GetDBBEPath_Exit:
-
Exit Function
-
GetDBBEPath_Err:
-
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
-
VBE.ActiveCodePane.CodeModule & "(GetDBBEPath Function).", vbOKOnly, "Error"
-
LogError ("GetDBBEPath Error " & VBE.ActiveCodePane.CodeModule & " " & Err.Number & " " & Err.Description & " " & Err.Source & " " & CreateObject("WScript.Network").userName & " " & CreateObject("WScript.Network").ComputerName)
-
End Function
-
[/CODE}
-
Please make sure you replace "NameLinkedTable" with any one of your linked table names. The process is simple, if your linked tables are stored at I:\Databases\MyDatabase\Db.accbe, then the function returns ... I:\Databases\MyDatabase
- .
-
-
For ease, I have included another function which does the same, however steps up one subfolder. I use this function a lot and have the need to access other folders in the same parent directory. Please make sure you replace "NameLinkedTable" with one of your linked table names.
-
-
For this to work you also need to replace "\FolderName\" with whatever folder your BE file resides. In the above sample, the \FolderName\ would read "\MyDatabase\"
-
-
[CODE}
-
Public Function GetDBBEPathSub() As String
-
On Error GoTo GetDBBEPathSub_Err
-
-
Dim strFullPath As String
-
strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("NameLinkedTable").Connect, 11)
-
GetDBBEPathSub = Left(strFullPath, InStrRev(strFullPath, "\FolderName\"))
-
'Debug.Print GetDBBEPathSub
-
GetDBBEPathSub_Exit:
-
Exit Function
-
GetDBBEPathSub_Err:
-
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
-
VBE.ActiveCodePane.CodeModule & "(GetDBBEPathSub Function).", vbOKOnly, "Error"
-
LogError ("GetDBBEPathSub Error " & VBE.ActiveCodePane.CodeModule & " " & Err.Number & " " & Err.Description & " " & Err.Source & " " & CreateObject("WScript.Network").userName & " " & CreateObject("WScript.Network").ComputerName)
-
End Function
-
When you need to use the folder path, simply call the function and add whatever you need afterwards such as this: -
strDatabasePath = GetDBBEPath & "\" & "Folder or file path here"
-
Also, I didn't remove my crazy Error Handling or error logger. Happy coding!
I use this utility i got from the net once upon a time. Customize it for you and check the code. It is a separate utility, but works great. Hold in shift when opening
twinnyfo 3,653
Recognized Expert Moderator Specialist
The .Connect call should be all that is needed. Then, once you have that string, removing the left 10 characters (";DATABASE=") will get you the entire path and file name. Then, from the end of the file name, look for the first "\" and take the Left() of the remaining string. I do this in a bit of an opposite way to find out the name of the DB to which my DB is linked. But once you have that string, it is merely an issue of manipulating or identifying the characters that you want/don't want.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: cloverme |
last post by:
The database is configured for single publisher, many subscribers,
merge replication. The maintenance plan started to fail a couple of
months ago and the database would not get backed up. After...
|
by: kusanagihk |
last post by:
To all,
How to backup a database into a number of smaller files ?
For example, can I can fully backup a DB of 10 MB into 10 files (each 1
MB)???
The problem I've met is that the DB backup...
|
by: HB2 |
last post by:
I am using the Linked Table Manager in Access to try and connect my front
end database (on my desktop) to my backend database (on my web host) I get
an invalid name error.
Any help much...
|
by: Wayne |
last post by:
I have a backend database (Backend1.mdb)that has 3 main data tables,
I'll call them Data1, Data2 and Data3, plus several other associated
tables. All relationships are set in the backend database...
|
by: s w dunn |
last post by:
Hello,
I am in charge of maintaining a little ACCESS database used by our purchasing department. I need to add some new values to a table, but I can't open the .be database in order to access the...
| |
by: Scott |
last post by:
I need to compact the backend database on server regularly using Jetcomp
utility. Can this utility support commandline so I can do it with Windows
scheduler. Your advice on the syntax is...
|
by: zafar |
last post by:
I need to creating backup of database on user defined location in hard
disk. thanx for helping in this regard
|
by: vishnutd4u |
last post by:
Hi Friends,
I am New To this forums.I have Some Doubt regarding taking backup in mysql on linux using VC++ code.By using Mysqldump i am taking backup of database but how to ask that code to...
|
by: vishnutd4u |
last post by:
Hi Friends,
I am New To this forums.I have Some Doubt regarding taking backup in mysql on linux using VC++ code.By using Mysqldump i am taking backup of database but how to ask that code to perform...
|
by: michael ngong |
last post by:
pramod@rtimes.com (Pramod Ramachandran) wrote in message news:<6616e304.0306240122.4dd3ecd5@posting.google.com>...
Permit me start with the second question.
It would be easier to be more...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |