update client MDB

[94Sport5sp]

Hi:

I have developed an Access database and now I would like some ideas on how to update the clients version of the database.

Some background. The database was developed with A2003 and is being run on A2000. The database is only used by one user on a standalone computer (not networked).

Now that the user is using the system I need to find some way to make changes (to forms, queries, reports, etc) on the clients computer.

Any suggestions?

Thanks

 

[RainLover]

What have you tried.

My thinking is simply Email or perhaps a "Dropbox Account"

I need to know more as this at first appears to be a simple problem.

 

[94Sport5sp]

Hi:

So far I have gone to the clients site and updated their system. I would either create an empty database (that is all the forms, queries, reports etc without data tables) and then imported their live tables or if it is a small change I would delete the item(s) being changed and then imported the desired item(s) into their system.

What I was wondering is the what tools (code) has been used to automate the process. The system is not networked nor does the system have (need) access to the outside world. So, whatever means I come up with will be delivered (sent) to them on a CD.

I have been thinking of writing some code (in an database ) that would auto run off a CD which would open up the clients database and delete the forms to be changed and then import the new forms into their live system. I would of course have a backup of the live system first.

I have been researching this issue for a while on here and on the net and the solutions usually involve placing the new code on a server for the user to get but since they are a standalone system that does not work well for me.

Thanks for your thoughts.

 

[MarkK]

A typical database system is "split," which describes an architecture in which all the data, so all the tables, are in one file called the 'back-end', and in which all the user interface elements and business logic are in a separate file called the 'front-end.'

In this design the FE contains links to the tables in the BE file, and all the user interface elements and business logic operate on the linked tables.

Also in this design it is a trivial matter to replace the front end with the next version by simply overwriting the old FE file with the new one.

 

[jonathanchye]

It is really easy to split a database if you are using AC 2010 but not so sure about 2003 as I've never used it much.

I would think of an approach to create an MDB file with links to tables (backend) and leave all the forms on the client (file you want to update)

 

[Pat Hartman]

If you haven't changed the tables upgrading won't be too hard. Split your version of the app using the built in database splitter.

On the user's computer, back up the application. Then use the splitter on it. Replace the old FE with the new FE from your computer. Use the linked tables manager to link the new FE with the user's old BE.

Once the db is split, you only need to send the user a new copy of the FE for him to link to his existing BE. You should probably add code in your startup form to verify the links and if they are broken, open a dialog to relink them if you think you will be doing this with any frequency.

If you did make schema changes, the problem is more complex. To handle this, create a separate database. In this database create a function or sub that runs the DDL queries necessary to modify the old schema to make it look like the new schema. If you prefer, you can write DAO or in some cases ADO code to make the schema changes. You then send this database to the user and have him run it against his BE (after backing it up first of course).

I have a couple of databases that are sold to others so I don't have access to their computers and I have used this update database technique several times. My apps have swapable BE's and can swap between SQL Server and Jet/ACE so I always use DDL. I have some tools that will compare two SQL Server databases and create the DDL to make one look like the other. I send these scripts to the client's DBA to run for the clients that use SQL Server and I convert the DDL to Access syntax and put them in a database that the Jet/ACE clients use to upgrade their BE. Because my schema was properly normalized to begin with, I haven't had any problems with this method. The changes are pretty much relegated to add a new column/expand the length of a column/add a new table/add a new relationship/add or remove an index and all of those are pretty easy to do.

 

[94Sport5sp]

Hi:

I have read of splitting databases and the context was usually in a server/network environment. Just to be clear this is not the case for this client as there is no network nor is there a server environment nor is there in internet connection.

Also, I am trying to see how it solves my update question. If my understanding is correct, I would have two databases. One which is tables only and one which has all the modules, forms, queries, and reports. The table section would be links to the tables only database. When it comes time to update I would delete the modules, forms, queries, and reports database and copy over the new modules, forms, queries, and reports database. Is my understanding is correct? Would I need to re-link to the table database?

Thanks for the thoughts

 

[jonathanchye]

Hmm... if there's no network and not even internet connection how do you plan to update the front end on client PC when needed? Onsite visit?

 

[94Sport5sp]

Hmm... if there's no network and not even internet connection how do you plan to update the front end on client PC when needed? Onsite visit?

Hi:

Since I am from the computer days that preceded the internet I do not see this as a obstacle. Back then we used the Sneaker Net as our means of distributing electronic files. By today's standard the Sneaker Net is slow but back then it was as fast as you could run. Was extremely reliable and never suffered from power outages.

Sorry, I could not resist a little humor.

Distribution would take place via copies on CD.

Thanks

 

[MarkK]

Yeah, that's right.

What I do is map a drive on the client's machine, say drive Z:\, which is the location of the data file. Link your FE to that. Then, during development I just map a Z:\ drive on the development machine too, grab a working copy of the BE tables (so I can do development) and make changes as per bugs etc that the client reported. Then when I deploy the new version at the client, I delete the old file, copy in the new version, and all the links are already working to Z:\. Makes sense?

You want to de-couple your data from your functionality so that you can swap in and swap out functionality more easily.

 

[Pat Hartman]

Once you split the database, it is split. You don't need to do it again. All you need to distribute is a new copy of the FE (forms/reprots/queries/code). The new FE will need to be linked to the existing BE. You will ALWAYS need to relink unless the path to the BE is identical on your client's PC and your development PC.

 

[94Sport5sp]

Hi:

Is there any constraints I need to be aware of when the client uses the front end to use the back end database. I am thinking here of the code I created to get this database working. Like, one form where I uses querydef to get the sql from a query, modifies it, and then saves the query to produce the records the user wants.

Also, on occasion I have copied my version onto the client computer and then imported their data into then new version (just because there was lots of changes) and access produced an error about missing a reference. I determined that the problem was due to the fact that I developed using access 2003 and they had access 2000 on there computer. I fixed the problem by changing the reference on their computer to use the older reference. (That is access 2003 was using version 11 of the reference and their system had version 10 of the reference.) I assume I will have to find a more permanent solution to that problem.

Thanks

 

[RainLover]

If you are changing the Front End then simply replace the old with the new. Teach them how to relink the tables or add some code.

If the change is in the Back end then you need a Back End updater. I believe Bob Larson has one. I don't know how good it is. Search this Forum.

You can place the Back End anywhere on the Clien't C:\ Drive. I would suggest that it should go into a Sub Folder to the Front End. Be sure that they have different names.

Do not convert the Back End to an MDE or ACCDE ?
But Convert the Front end.

Could you not email the new DB to someone else in the same organisation.

 

[Pat Hartman]

As long as you are developing in a different version, you will run into reference issues. Access cannot automatically downgrade references although it can upgrade them. The best solution is to use Virtual PC to maintain a "client" environment and move the database into that and compile it so you can fix the broken references before distribution.

 

[94Sport5sp]

Hi:

After some trial and fix I split my database. Now I just have to test to ensure all is working as intended and then deploy to clients location.

Just for completeness the offending reference is OWC11.DLL and I fix it with OWC10.DLL An interesting aspect to this problem is that it showed up part way through my development. I had a fairly stable version of my database and took it to the clients site to demo and BAMM. up popped this error. Something I added to my code between demo 11 and 14 triggered the reference. Is there any list anywhere which identifies what is contained in OWCxx.dll?

How about I copy the OCW10.DLL from the client site (or off the net) and use it in my development?

Thanks

 

[Pat Hartman]

Simply copying the .dll won't solve the problem since .dll's need to be "registered" in order to work. From what I find with Google is -

Microsoft Office Web Components [owc11.dll] (Is not supported in Access 2007 or 2010 )

Try deleting the reference. If the app still compiles, it is likely you don't actually need it. You may have tried out some ActiveX control that created the reference but never used the control in the final version.

 

[94Sport5sp]

...Try deleting the reference. If the app still compiles, it is likely you don't actually need it....

Hi Pat:

Good call. I have removed the OCWxx.dll from my development system and so far all is well. Now I just need to try it at the clients site.

Thanks for the thought.