Relationships. 1

[tricky29]

I have setup a master database with satelite databases running off satelite machines. The idea is that the satelite machines will complete a record and then export that record to floppy to be then imported to the new machine.

The make up of the database is as follows. Consultant ID in Consultant Table linked by a a one to many relationship to Consultant ID in Illustration Table. The Illustration ID in the Illustration Table is then linked with a One to Many to 3 sub tables.

If I export all 4 tables stored on an individual Illustration ID as separate files then when this is imported into the master database this will cause me a problem as the satelite machines will also have the same Illustration ID numbers and therefore the master data base will not be able to distinguish which records in the subtables belong to which record. can anyone help ?

 

[JimHorton]

Tricky,
First, if you're using autonumber for any of these id's, a first suggestion is to stop at once and use a straight long integer instead, and create you're own sequenced ID's. If this ID is text, this isn't a problem. What you need to do now is give each satellite machine a range or prefix, ie machine 1 doles out ID's in the 1,000,000 range, 2 in the 2,000,000 range, and so on.

If you can't change the satellite ID's, or need to keep them autonumber, or want to make this change only on the main machine, then just add 1,000,000 to these id's when they're from machine 1, 2,000,000 to these ID's from machine 2, etc. Be sure to cascade these updates. If your ID's are already in this 1,000,000 range, then up the prefix accordingly. Also, if you feel you'll have more than 1,000,000 ID's in the life of the db, then separate ranges, by 2,000,000, etc.
--Jim

 

[tricky29]

Jim thanks for pointing me in the right direction. I have an idea of how to solve this whilst leaving the whole system automated, however my knowledge is limited.

The master database (Consultant ID) is in sequential numbers 1-60 and may well be added to. Therefore taking your second paragraph suggestion of only altering the data on the master table is there an easy way to take the Consultant ID (say 2) and multiply that by 1,000,000 and then add that to the Illustration ID thus creating an automated Illustration ID dependant on the Consultant ID value ?

If so at present I extract the info using a query which sets the id number and then use a module as follows

DoCmd.TransferText acExportDelim, , "Illustration Query", "a:\illustexp.csv", 1
DoCmd.TransferText acExportDelim, , "Life Assurance Query", "a:\lifeexp.csv", 1
DoCmd.TransferText acExportDelim, , "Loans Query", "a:\loanexp.csv", 1

To import to the master I use the equivalent import commands.

 

[JimHorton]

The illustration ID is the one I was thinking should be the one prefixed with the 1 mil, 2 mil, etc, since it seemed that was the one that would be added to, where consultant ID's would be relatively static. However, yes, the same logic can be used for new consultant ID's added at the satellite machines, but you may want to reduce the prefix to 10,000; 20,000 etc, assuming the number of possible consultants is well within 10,000.

This assumes the purpose of the satellites is for *adding* new data. If you're going to be updating existing records, and all satellite machines are 'refreshed' with the master data every day/week/month/whatever, then you're getting into a replication/synchronization situation. So, if satellite 1 updates consultant ID 5, or any existing illustrations in satellite 1, but satellite 2 also updates the same record, now there is contention. This is best handled by the Access replication functionality, which is a relatively large undertaking. You could also attempt to replicate by hand--with only 3 tables this might not be too difficult.
--Jim

 

[tricky29]

Jim . I also mean't the Illustration ID as the Consultant ID once set will never change. But what I thought was that as the Consultant ID runs from 1-60 or so then could a routine be written to take the consultantid number multiply it by 1,000,000 and then use that number as the starting point for each satelite Illustration ID i.e if consultantid=39 then 39x1,000,000 = 39,000,000 and this number is then the starting point for autonumber for illustration id. Therefore if a new consultant is added i.e number 61 then the software automatically starts the illustration id from 61,000,000. Whilst i have the idea , i have no idea of how to do this or wether or not it is possible ?

 

[JimHorton]

tricky,
To do this--start out the Illustration ID's at a set number, you need to abandon Autonumber. Make the IllustrationID a Long Integer. Then create a table, called, say, tblLastID. This contains 2 fields, Key and LastID. Key is just a field to help the table stay with only 1 record. Make this field Integer or byte, set value to 1 and validation rule to 1, and make it PrimaryKey, this way the table can only have 1 record. Now, when you move the data to the satellite machines, you set the LastID field equal to the highest ConsultantID + 1 mil, 2 mil, etc, according to machine.

Now, when the satellite machines add to the illustration table, you must have them do this via a form (not entering directly into a table-datasheet). In the form's IllustrationID Default value property, you set that to =GetNextID(). The GetNextID() is below:
Function GetNextID() as Long
dim rst as recordset
'we do this in a recordset to lock it, guaranteeing that no one else gets same #
Set rst = currentdb.OpenRecordset("tblLastID",dbopendynaset,dbDenyWrite,dbPessimistic)
rst.movefirst
rst.edit
rst!LastId = rst!LastID + 1 'increment ID
GetNextID = rst!LastID 'return incremented ID
rst.Update
Exit Function

The above could alternatively be called in the form's BeforeInsert Event, or at any procedure where an AddNew is called for a field needing an 'autonumber'.
--Jim

 

[tricky29]

Jim Thanks for that. 99% there.
I have slightly adapted what you sent me so as to automate the Illustration ID starting point . This is because when a consultant is given the software he selects his name thus setting the Consultant ID. I have therefore taken the value of the Consultant Id multiplied it by 1,000,000 and then set that value into LastID . It works fine !!!!


One problem remaining . At the moment using a macro from the main menu you would select New/Edit Illustration on a command button and this then opens the form which is used to input data into the table.

The form displays the first record and you can use page up or dowm to scroll through the records. At the moment if you then hit a further command button called 'New' it then runs a further macro that tells it to 'Go to a New Record'.
This clears the data on the form ,ready to input data under the existing 'AutoNumber Illustration ID'. Now I have made the changes yopu advised me of .How do I adapt the procedure so as to use the ID number generated in the procedure and start a new record ? I hope my explanation is clear enough for you to understand

 

[JimHorton]

tricky,
To get the new ID into a new record, you set the DefaultValue property (in the properties box) of the ID field on the form to
=GetNextID()
The ID field can be hidden or visible, but you should set it to Locked (in the properties box as well). When you click the button to go to a new record, the next ID should be showing in the field already--the function runs when you start a new record, before any data is inserted.
--Jim