Proper Deployment Practice

[Stang70Fastback]

Hello All,

I've done a little bit of Googling and some searching here, but still feel as though I don't have a firm grasp on this subject.

This is my first "real" database that I have built. It is going to be deployed to many users, at many satellite locations across the city. Prior experience has been limited to a couple folks in the same office. This is MUCH more intimidating and I'm... nervous... about this deployment because it is going to be a huge shift for everyone and I want to make sure I do things properly.

Can someone point me towards some good blogs/threads/posts/etc... for a beginner that could get me going in the right direction? Some of the questions/concerns I have are listed below:

What does it meant to "Package and Sign?" Do I need to do this? Currently I just create an ACCDE file which has code in it to disable the Shift key on startup, and more code to hide the background Access window.

What is the best way to distribute the file? At my previous location everyone worked off the same front end, but I soon realized how dumb that was, so for recent test deployments here, I've got everyone using their own version of the front end.

I have split the database into one back-end, centrally hosted. I'm pretty sure this is the right thing to do...?

What do I need to know before officially deploying this so that it doesn't all blow up in my face? Thanks!!!

 

[pbaldy]

Network no big deal, satellite locations might be troublesome:

http://www.kallal.ca/wan/wans.html

I've done it with SQL Server as a back end and an app carefully written to minimize data being transferred. There are numerous ways to handle updating; I write my own little exe to do it using a version table in the front end. Here's one (used to be free but I don't think it is now):

http://autofeupdater.com/

or the first section here:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

or the first here:

http://btabdevelopment.com/free-access-tools/

 

[Stang70Fastback]

Thanks for the reply.

Currently some satellite locations do experience significant lag, however all of these locations are being upgraded to fiber, so the network speed issue should hopefully be resolved.

As far as updating, I'm actually not too terribly concerned with that at the moment. For our trial runs, the simple method of emailing a new front end version out to everyone worked well enough. It will suffice in the short term, at least. In the long run, this is something that won't be changed very often - maybe once every two weeks or once a month.

I'm more concerned, really, with just knowing if an ACCDE file is the proper file I should be distributing. Everyone talks about packaging and signing the file. Does that result in a different file format? Is it better? Or is an ACCDE all I really need to worry about?

 

[Galaxiom]

You need to take a step back before the deployment issue.

Access back ends do not work well with slow connections (WAN or Internet) between the front end and back end. The back end eventually gets corrupted.

This happens because the local database engine on the front end machine is used to manage the back end.

You need to use a database server for the back end. In this scenario the command is sent to the database engine on the server so the file is not pulled apart and sent over the slow connection as it is with Access.

 

[pbaldy]

I just distribute accde files.

I personally wouldn't run an Access backend in a WAN scenario, for the reasons discussed by Albert and reinforced by Galaxiom. It's your call, but there is a free version of SQL Server if you go forward with it and have issues.

 

[Stang70Fastback]

From what I understand, I could use SQL Server as the back end, but there are two problems with that solution:

1.) I've been told that converting an Access Database to use SQL Server tables, while not impossible, does require quite a few changes to a lot of code - especially if you use VBA, which I do.

2.) I have literally NO experience with SQL Server. All I know about database design, or any sort of programming, is two years of self-taught trial-and-error and lots of Googling. I know enough VBA to do what I need to do, and nothing more, lol.

For those two reasons, I don't think SQL Server is a realistic solution for me. I might be able to say "let's hold off for 6 months while I go learn SQL and rebuild things" if I worked in the IT department here, but I'm actually not related to them at all. I'm just a guy in another department who "seemed to know what he was doing" enough for some higher-ups to ask me to build this database, which I've put together over the past few months. God only knows how long it would take me to figure out how SQL works and get everything converted, and I also have no idea what would be involved in actually getting everything running. I know our company uses SQL Server for stuff, but I wouldn't even know where to begin to get it set up!

However, it's possible what I said above isn't true. What would be the best approach? I'm off to Google about converting Access DBs to SQL.