Database Splitting (XP)

Topic

New Reply

I am planning to use the Database Splitter Wizard to separate the tables of my application from the forms, queries, etc. and store both resulting DBs in the same folder. Then give a copy of the folder to the user. When I need to change a form or query, how should I deploy the revised form(s)/quer(ies) to the user? Will I need to “re-link” to the tables in the “Table Database”? The goal is to update the “processing” without impacting the data. If I need to change a table definition, how can I deploy a new definition without disrupting the data? Do I need to access the “production” tables? Thanks.

Reply | Quote

Replies

Well you certainly have the right idea in splitting the database into FE/BE.

When you make changes to the FE (queries, forms,etc) you then need to re-link the FE to the BE before copying it to a place where users can then copy it to their own PC (the FE should always reside on the usersPC).

If you need to change table design you will need to get everybody out of the database, make a copy of the current BE (just in case), make your changes to the BE then let everybody loose again.

Others will undoubtedly have more info regarding this.

Reply | Quote

Thanks for the quick response.
I see how to do “importing”; but once the link is defined, do you “re-link” the same way?
Also, am not sure that the user intends to distribute the forms, queries, etc. to mulitple PCs; seems like they intend to maintain it on a server (at least until they have performance problems). Do you recommend they do this sooner? Are there considerations other than performance? Thanks again.

Reply | Quote

[indent]

---

I see how to do “importing”; but once the link is defined, do you “re-link” the same way?

---

[/indent]You can, but you can also use the Linked Table Manager under Tools/Database Utilities.
[indent]

---

. . . they intend to maintain it on a server (at least until they have performance problems). Do you recommend they do this sooner? Are there considerations other than performance?

---

[/indent]Performance isn’t likely to be an issue unless you are running on a slow LAN or have very large, complicated forms. But there are other considereations such as the fact that you cannot make design changes in 2000/2002 databases unless you have exclusive control. You can of course have a development copy, but in order to deploy changes you have to kick all the users out, and then replace the current version with the new one. There are a number of other issues – we have a tutorial on splitting on our website that you might find userful. (Our ISP has been a bit flakey lately, but it is up at the moment.)

Reply | Quote

Thanks. Really appreciate the guidance. I keep coming up with new problems. E.g., my first attempt to deploy the split application on the user’s system failed…sort of. On the user’s server and one PC, the files appeared as “read-only”, yet on another user PC, the application basically worked fine (however, could not reestablish some of the mail merge links).
Has anybody seen this sort of inconsistent compatibility? Could it be due to differences between XP/2002? Or between 2002 and 2000 databases? Or between XP Pro and Home? Or is it something else entirely…like the split DB?

Reply | Quote

I suspect your differences stem at least in part from the OS involved. In particular, with an NTFS file system under XP, your user may not have write priviledges for the database. Another possibility is that the files were copied to a CD-ROM, which automatically sets the ReadOnly flag. When you copy them back to another PC, the flag has to be removed, or the file will still be ReadOnly. As far as the mail-merge links, those need to be in the Front-End, and you may have to recreate them, particularly if the path to the main merge document has changed in any way. You may also have to change the data source to look at the C: drive (or whatever local drive the Front-End database is stored on. Hope this get you further down the road.

Reply | Quote

Thanks very much for your help. Sorry, I don’t fully understand a few points:
1. I used a CD-RW. Why would the ReadOnly flag get set and how can I remove the it?
2. What are the “mail-merge links” that need to be in the Front-End, and how do you recreate them? When I try to change the data source, I get the message: “Unable to obtain list of tables from data source.”
I apologize if I’m being a pest. I’m running out of hair to pull out.
Thanks again.

Reply | Quote

Let me see if I can offer some assistance…

1. I am not sure why the ReadOnly flag is set, but I do know that it has happened to me when I’ve copied to CD’s…
To remove the flag, open up Windows Explorer, browse to the folder where the file(s) were saved… select the file(s)… right click… select properties… and remove the check from the Read-Only box… click OK…

Wish I could help with 2, but I know virtually nothing about mail merge links…

At least that’s a start…

Reply | Quote

The read-only setting happens b/c the CD medium is read-only.

Reply | Quote

Uhhhh… Wellll…. I guess I shoulda known that one…. Thanks ksnyder!

Reply | Quote

Is there a cure if the user has an “NTFS file system under XP” (and doesn’t have write priviledges for the database)?

Reply | Quote

Sorry – I’ve been out of touch all day – your question on ReadOnly has already been answered, and your question on NTFS and its permissions issue is readily solved by someone with network admin permissions. They can change it to read and write – they should probably do that for all files in the folder, or the folder itself. The reason is that databases do need to be written to in most situations, and if it is multi-user, they need to have permission to create the lock file.

As to the mail-merge links, I presume you are referring to the path to the database that the word document thinks is the data source. As long as the drive and path mappings are exactly the same on the new PC as they are on your PC, that should work. However if the path is only slightly different, Word can’t find the database and starts giving you problems. In addition, there are some changes in the way Word merges work in XP – that could also be giving you problems.

Reply | Quote

When you split a database, the result is:

• A backend database containing only tables and no other database objects whatsoever.
• A frontend database containing links to all the tables in the backend, and all other database objects (queries, forms, …)
  [/list]If you need to change a query, form, report, macro or module, do so in a copy of the frontend database, then give all users this copy to replace the original frontend. No need to change anything in the backend for this.
  If you need to change the definition of a table, the backend is the only place where you can do that. Whether you will need to modify the frontend to comply with the changes in the backend, depends on the nature of the changes. If you just increased the size of a text field, the frontend should take that in its stride. If you added or deleted a field, chances are you will have to adapt queries, forms and/or reports.

Reply | Quote