Multi user database with no network?

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!).
 
Addyman said:
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!).
Click to expand...

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.
 
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:

Code: 
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)

Code: 
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!
 
Addyman said:
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:

Code: 
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)

Code: 
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!
Click to expand...

Thanks very much.

Any luck with the NAS box recommendations and any other bits I need for it? Links to any products?
 
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.
 
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.
 
rodmc said:
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.
Click to expand...

Cheers. What abou the drives and any other bits?
 
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)
 
rodmc said:
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)
Click to expand...

Western Digital ok? or Seagate?

And you say 2 x 1 TB drives?
 
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
 
rodmc said:
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
Click to expand...

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.
 
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.
 
rodmc said:
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.
Click to expand...

Cheers mate. Last question before I buy stuff. Do I need any quality cables or anything?
 
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.
 
rodmc said:
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.
Click to expand...

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
 
yeah no probs, might be thursday or so though as im flat out today and tomorrow
 
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
 
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
 
You must log in or register to reply here.

Similar threads

G
Steps to deploy database for users.
Replies
9
Views
291
GPGeorge
GPGeorge
N
Using MS Access with WAR File
Replies
2
Views
257
sonic8
S
C
Database slow on terminal server running Access Runtime
Replies
13
Views
594
Clem Fandango
C
B
Multi User Database with Record Locking
2
Replies
29
Views
1,314
The_Doc_Man
The_Doc_Man
Kayleigh
Performance Issues
Replies
4
Views
405
Kayleigh
Kayleigh

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom