Multi user database with no network? (1 Viewer)

[Addyman]

It's easier than you think. I will post up the code etc. you need in a little while (just heading out of the office for a bit!).

 

[YNWA]

It's easier than you think. I will post up the code etc. you need in a little while (just heading out of the office for a bit!).

Ok thanks.

I wil hopefully be looking at trying something on Saturday (if I get the NAS in time)

Can you recommend anywhere or any models and drives to buy? Maybe some links.

 

[Addyman]

OK, code and process for updating User FE databases:

In your BE database, create a table called tbl_VersionServer, with a text field called VersionNumber. This is where you enter the latest version number of your application.

In your FE database, create a local table called tbl_VersionClient, with a text field called VersionNumber. This stores the version number of that particular FE database.

On your Server, or NAS, place a copy of the FE. This will be the 'Master' copy and will be the one you modify.

I use a 'Splash' form for my applications which opens up, displaying the current Version number and which runs the version check, but you can implement how you want.

The code I use is:

Dim strVerClient As String, strVerServer As String

strVerClient = Nz(DLookup("[VersionNumber]", "[tbl_VersionClient]"), "")
strVerServer = Nz(DLookup("[VersionNumber]", "[tbl_VersionServer]"), "")

If strVerClient <> strVerServer Then

'This copy of the FE is out of date and we need to get the latest version

Dim strSourceFile As String, strDestFile As String
Dim strAccessExePath As String, lngResult As Long

'Create the source's path and file name.

strSourceFile = "\\YourServerOrNasPath\FolderWhereMasterFEisKept\NameOfYourDatabase.mdb"

strDestFile = CurrentProject.FullName

'Determine path of current Access executable

strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "

If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.

MsgBox ("Please see system administrator")

Else 'copy the new version of app over the existing one.

lngResult = apiCopyFile(strSourceFile, strDestFile, False)

End If

'Modify strDestFile slightly so that it can be used with the Shell function

strDestFile = """" & strDestFile & """"
    
MsgBox "Application Updated. Please wait while the application restarts.", vbInformation, "Update Successful"
    
'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
    
DoCmd.Quit

End If

In a new Module, enter the following and save the Module (I call mine mod_UpdateApplication)

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

So, to update everyone's FE, make your modifications to the Master FE, update the Version Number in both tbl_VersionServer and tbl_VersionClient and that's it.

One thing, make sure in your Master FE the Version Numbers are the same in both tables or your users get stuck in a loop!

 

[YNWA]

OK, code and process for updating User FE databases:

In your BE database, create a table called tbl_VersionServer, with a text field called VersionNumber. This is where you enter the latest version number of your application.

In your FE database, create a local table called tbl_VersionClient, with a text field called VersionNumber. This stores the version number of that particular FE database.

On your Server, or NAS, place a copy of the FE. This will be the 'Master' copy and will be the one you modify.

I use a 'Splash' form for my applications which opens up, displaying the current Version number and which runs the version check, but you can implement how you want.

The code I use is:

Dim strVerClient As String, strVerServer As String

strVerClient = Nz(DLookup("[VersionNumber]", "[tbl_VersionClient]"), "")
strVerServer = Nz(DLookup("[VersionNumber]", "[tbl_VersionServer]"), "")

If strVerClient <> strVerServer Then

'This copy of the FE is out of date and we need to get the latest version

Dim strSourceFile As String, strDestFile As String
Dim strAccessExePath As String, lngResult As Long

'Create the source's path and file name.

strSourceFile = "\\YourServerOrNasPath\FolderWhereMasterFEisKept\NameOfYourDatabase.mdb"

strDestFile = CurrentProject.FullName

'Determine path of current Access executable

strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "

If Dir(strSourceFile) = "" Then 'Something is wrong and the file is not there.

MsgBox ("Please see system administrator")

Else 'copy the new version of app over the existing one.

lngResult = apiCopyFile(strSourceFile, strDestFile, False)

End If

'Modify strDestFile slightly so that it can be used with the Shell function

strDestFile = """" & strDestFile & """"
    
MsgBox "Application Updated. Please wait while the application restarts.", vbInformation, "Update Successful"
    
'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
    
DoCmd.Quit

End If

In a new Module, enter the following and save the Module (I call mine mod_UpdateApplication)

Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" (ByVal lpExistingFileName As String, ByVal lpNewFileName As String, ByVal bFailIfExists As Long) As Long

So, to update everyone's FE, make your modifications to the Master FE, update the Version Number in both tbl_VersionServer and tbl_VersionClient and that's it.

One thing, make sure in your Master FE the Version Numbers are the same in both tables or your users get stuck in a loop!

Thanks very much.

Any luck with the NAS box recommendations and any other bits I need for it? Links to any products?

 

[Addyman]

I'm not really up on NAS Hardware etc, to be honest, i'm more the software side of things.

Maybe Rodmc will come back with some more information.

 

[rodmc]

You need to make sure the NAS can be accessed like a normal windows share (ie you can access via Windows Explorer and can map it as such). I bought a Zxyel 320, most of them do appear like a windows share, but make sure before you purchase it. Some have an application to access files on the NAS which is not what you want.

 

[YNWA]

You need to make sure the NAS can be accessed like a normal windows share (ie you can access via Windows Explorer and can map it as such). I bought a Zxyel 320, most of them do appear like a windows share, but make sure before you purchase it. Some have an application to access files on the NAS which is not what you want.

Cheers. What abou the drives and any other bits?

 

[rodmc]

just standard SATA drives, although I'd stay away from Seagate drives right enough (And Samsung as its now Seagate that own Samsung's HDD division as far as I know)

 

[YNWA]

just standard SATA drives, although I'd stay away from Seagate drives right enough (And Samsung as its now Seagate that own Samsung's HDD division as far as I know)

Western Digital ok? or Seagate?

And you say 2 x 1 TB drives?

 

[YNWA]

This ok same as you - http://www.dabs.com/products/zyxel-...le&utm_medium=product+search&utm_content=Q200

Plus maybe 2 x 2TB SATA as its got a 4TB capacity?

Maybe 2 x http://www.businessdirect.bt.com/pr...mb-7200rpm-hard-drive-7XD7.html?refs=52770000

 

[rodmc]

WD are ok, but Ive always had problems with Seagate drives. Even when I worked for IBM, we would stay clear of seagate drives unless we couldnt get IBM, Maxtor or WD drives.

If your getting 2 x 2TB drives, remember to set it up as RAID1 on the NAS, that gives you fault tolerance to a certain degree, but whatever you do, dont use RAID0, RAID0 is a hell of a let faster seek and read times but if one of the drives fail, you lose the data on both the drives.

Yep that link to Dabs is the same one I got, not to shabby for that price I think you'll agree!

As for the drive, maybe this one would be a better choice, a few pound dearer but I think WD drives are most reliable than Seagate.

http://www.dabs.com/products/western-digital-2tb-green-sata-600-64mb-3-5--7G12.html?src=2

 

[YNWA]

WD are ok, but Ive always had problems with Seagate drives. Even when I worked for IBM, we would stay clear of seagate drives unless we couldnt get IBM, Maxtor or WD drives.

If your getting 2 x 2TB drives, remember to set it up as RAID1 on the NAS, that gives you fault tolerance to a certain degree, but whatever you do, dont use RAID0, RAID0 is a hell of a let faster seek and read times but if one of the drives fail, you lose the data on both the drives.

Yep that link to Dabs is the same one I got, not to shabby for that price I think you'll agree!

As for the drive, maybe this one would be a better choice, a few pound dearer but I think WD drives are most reliable than Seagate.

http://www.dabs.com/products/western-digital-2tb-green-sata-600-64mb-3-5--7G12.html?src=2

Cheers. Would you rate Maxor and IBM over WD?

As its the first time doing this for an external firm, just don't want it to go wrong.

 

[rodmc]

IBM drives are very hard to get, Im not sure they even still manufacture them. But to answer your question I'd have a WD before I'd buy a maxtor, Ive had some maxtors brand new with bad sectors, so I tend to avoid them as well.

 

[YNWA]

IBM drives are very hard to get, Im not sure they even still manufacture them. But to answer your question I'd have a WD before I'd buy a maxtor, Ive had some maxtors brand new with bad sectors, so I tend to avoid them as well.

Cheers mate. Last question before I buy stuff. Do I need any quality cables or anything?

 

[rodmc]

no, wouldnt think so unless your fitting the gear in somewhere with a lot of RFI or EMI (radio freq interference or elecroto magnetic interference) like a hospital setting or such.

 

[YNWA]

no, wouldnt think so unless your fitting the gear in somewhere with a lot of RFI or EMI (radio freq interference or elecroto magnetic interference) like a hospital setting or such.

Nope just a standard office block.

Would you mind giving me an idiots guide on how to set it up, get the database on it etc...?

I go away end of the week, so hoping someone might be able to set it up while I am gone.

Thanks

 

[rodmc]

yeah no probs, might be thursday or so though as im flat out today and tomorrow

 

[YNWA]

No worries mate. Thanks very much.

 

[rodmc]

my apologies YNWA, I got sidetracked with a problematic firewall on Thursday which had me going in circles for 2 days, if you still need a quick "how to" I can do it for you now the pressure is off

 

[hardyd44]

Can I put my 2 penny worth' in

bit of topic I know - but bear with me

I set up a small network using a switch, cat 5e cable, a HP microserver (£250 with a£100 cashback) and a copy of windows home server 2011 (a very underated product) @ £39, - total final cost with 2 extra 1tb HD = around £350, I know hard drives prices have gone up since but are on their way down.

This gave file and print sharing, a secure place for their DB backend and also auto backup of all the client PC's (you can have up to 10 clients) and remote access through a website if needed

It took me a weekend to get it all running just by following a £25 book - this network is rock stable and has run after a few intial teething problems for 12 months without a peep.

we looked at a nas device but thought for the extra £100 or so it was worth the effort

just my view thats all