Generic Access VBA Code wont transfer to another database

[NDayave]

ive managed to piece together this code that creates a backup folder and then copies the (Access 2000) database into it under the new name "Backup YYYY-MMM-DD HH.MM".

It worked fine in the database I created to practise and create the code, but when i copy and paste it to the main database none of it works, and i have no idea why.

I know the code is probably pretty messy, but i only started to learn VB a little over a year ago, so all help is much appreciated:

Private Sub cmdBackup_Click()
'Create the new folder "Backup" to store database backups
Dim objNet, objFSO, objFolder, strDirectory, test
 
On Error Resume Next
 
Set objNet = CreateObject("WScript.NetWork")
 
Set objNet = CreateObject("WScript.NetWork")
If Err.Number <> 0 Then 'If error occured then display notice
MsgBox "Don't be Shy." & vbCrLf & "Do not press ""No"" If your browser warns you."
Document.Location = "UserInfo.html"
'Place the Name of the document.
'It will display again
End If
 
Dim strInfo
 
strInfo = objNet.UserName
strDirectory = CurrentProject.Path & "\Backup"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(strDirectory)
Set objNet = Nothing 'Destroy the Object to free the Memory
'End of make folder
 
'*****************************************
 
'Make a backup of the database into the newly created folder
On Error GoTo Back_Database
Dim fso, fs, Src, Dst As String
 
Src = CurrentDb.Name
 
Dst = CurrentProject.Path & "\Backup\Backup " & Format(Now, "yyyy-mmm-dd hh.mm") & ".mdb"
 
Set fso = CreateObject("Scripting.FileSystemObject")
Set fs = fso.GetFolder(CurrentProject.Path & "\Backup")
C_Backup:
fso.CopyFile Src, Dst
MsgBox "Backup Complete", vbInformation
Exit Sub
 
Back_Database: If Err.Number = 76 Then
MkDir CurrentProject.Path & "\Backup"
GoTo C_Backup
ElseIf Err.Number = 53 Then
fso.CopyFile Src, Dst
MsgBox "Backup Complete", vbInformation, "Backup Complete"
Else
MsgBox Err.Description
End If
End Sub

Like i said all help is much appreciated

NDayave

[MMcCarthy]

At what line is the code stopping?

[MMcCarthy]

At what line is the code stopping?

I've tested the code and outside of the document link it seems to be working fine. Have you got write privilages on the network in which you are trying to create this file.

Mary

[maxamis4]

10 bucks says that you need to include a reference from your library. Check out the old database and look at the references you had checked off. Make sure that they match what you currently have in the new DB. Your code looks solid so I don't see the problem.

[tehgreatmg]

I agree with that guy ^^^^ check your references.

[NeoPa]

On a side-issue, can I suggest you use "Backup yyyy-mm-dd HH.nn" for your filename instead. That way the month will be numeric and sorted in correct version order in Windows Explorer. I'm unsure if the .mm would work at the end anyway as the format function often guesses correctly, but nn is the correct string for two digit minutes.
NB. The case is important for HH.

PS. Please let us know exactly where the code is highlighted in yellow when it stops.

[ADezii]

I've tested the code and outside of the document link it seems to be working fine. Have you got write privilages on the network in which you are trying to create this file.

Mary

Mary:
Just a little side note relating to the posted code. The Variables fso, objFSO, objFolder, and fs are all declared as Variants. Shouldn't the declarations be:

Dim fso As New FileSystemObject
Dim objFSO As New FileSystemObject
Dim objFolder As Folder, fs As Folder

NOTE: I'm not sure whether it has a bearing on this discussion but just thought that I would mention it.

[MMcCarthy]

Mary:
Just a little side note relating to the posted code. The Variables fso, objFSO, objFolder, and fs are all declared as Variants. Shouldn't the declarations be:

Dim fso As New FileSystemObject
Dim objFSO As New FileSystemObject
Dim objFolder As Folder, fs As Folder

NOTE: I'm not sure whether it has a bearing on this discussion but just thought that I would mention it.

Technically yes they should. However, declaring any variable as a variant will usually allow it to be used as any data type including object. Not that I advise doing so on a normal basis.

The Variant Data Type (Definition)
The Variant data type stores numeric and non-numeric values. This data type is the most flexible because it stores very large values of almost any type. Use it only when you're uncertain of the data's type or when you're accommodating foreign data and you're not sure of the data type's specifications.

The Variant data type is VBA's default, so the following code interprets varValue as a Variant:

Dim varValue

Although the Variant data type is flexible, VBA processes these data types a little slower because it must determine the most accurate data type for the assigned value. However, most likely, you'll never notice the performance hit.

The biggest disadvantage is the data type's lack of readability. By that, we mean that you can't easily determine the appropriate data type by viewing the code, and that can be a problem.

[maxamis4]

I still think its his reference. I am sure many of us in the past have declared variants. Not to mention he said that the old version works and this one doesn't. If he made absolutley no changes I want to say that he is missing one of his references from the database.

Go to code view and under tools you will see references. Compare the old and the new and make sure they both include the same references.

Good luck

[NDayave]

Thanks for the response guys, i appreciate it.
#2: The code stops at 'End Sub' on the bottom line
#3: Im testing this on my own computer so i have full write priveledges, like i said it works fine with one database, but not the other
#6: Thanks for the format, i took a guess at the minute representation, What does the HH.nn return thats different to all lower case?
#4, 5, 9: I checked the references, and there are more selected on the actual database, including all those selected on the practise database:

Practise Database
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Actual database
Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Office XP Web Components
wiaview 1.0 Type Library

Is having the extra references selected a problem? Like I said, I'm quite new to this.

Thanks again,
NDayave

[NDayave]

I unchecked the extra references on the actual database:
Microsoft Office XP Web Components
wiaview 1.0 Type Library
and it worked fine, thanks a lot.

Will having these two references unchecked have any negative consequences on the database though?

Thanks again,
NDayave

[NeoPa]

This may sound like a lame answer, but "Only if your database is using anything provided by these libraries."
There shouldn't be anything standard affected by losing these two though I don't think.

[maxamis4]

It can be, what references do is import built in functions that allow Access to communicate to other devices. I would match them up identically and see what results you get.

[NDayave]

With regard to the references, what determines which are selected and which aren't? Would there be any reason for the extra references to be checked in the actual database, or is it just a default setting?

[MMcCarthy]

With regard to the references, what determines which are selected and which aren't? Would there be any reason for the extra references to be checked in the actual database, or is it just a default setting?

It's not a default setting only the first four are. The others are checked so that they can be used by the designer. Run the compiler and see if any errors pop up since you removed these references.

Mary

[NDayave]

I went into the code window => Debug => Compile Booking System

im guessing thats what you meant, there was a loading bar visible very briefly on the toolbar and then nothing else happened and now i cant compile again. Would i be right in thinking this means there are no problems?

[MMcCarthy]

I went into the code window => Debug => Compile Booking System

im guessing thats what you meant, there was a loading bar visible very briefly on the toolbar and then nothing else happened and now i cant compile again. Would i be right in thinking this means there are no problems?

That would be my interpretation.

As a test, type could something and then delete it, the compiler should reset.

Mary

[NDayave]

That would be my interpretation.

As a test, type could something and then delete it, the compiler should reset.

Mary

Yeah it reset the compiler and again nothing but a glimpse of the loading bar and not being able to compile till something is changed again.

Thanks for that, appreciated

NDayave

[NeoPa]

I went into the code window => Debug => Compile Booking System

im guessing thats what you meant, there was a loading bar visible very briefly on the toolbar and then nothing else happened and now i cant compile again. Would i be right in thinking this means there are no problems?

You certainly would be.
The compiler is only available when there is changed code to compile. If any (compiler level) problems are detected they will be reported and the compile will fail.
NB. Some problems cannot be detected by the compiler so it's not proof of perfect code - it's a very good check to do first though.