Primary Key Problem

[spikepl]

Does it matter? The issue is unique identification of records. So you can mess with the PK's or stop half-way, and just provide means to uniquely identify the records by some fields such as the inspector and time of creation.

 

[nanscombe]

Yes it does matter.

If no new records are generated there would be no new Primary keys to be generated.

Or

If any new records were created by the central database, then exported and amended by the inspectors, there would still be no need to generate a new Primary key.

Either way - No new key = No problem.


(Edited because typing on an iPhone, whilst moving, is not infallable)

 

[spikepl]

And why are the PKs a concern at all? Just let the central DB have its own, and the local dbs their own. Simply forget about them. (OP stated previously that data traffic is one way, and records are created or amended locally).

 

[Aristillus]

It may be that I am missing something fundamental here.
Let's imagine for simplicity that there are just 2 inspectors, A & B. Each has a laptop loaded with a brand new empty copy of the database.
In a central location on a server there is a third copy of the database, again brand new and empty.
(A) enters 2 records, lets call them A1 and A2
(B) does likewise and creates B1 & B2

These records now need adding to the database. (A) clicks a button labelled "EXPORT" which I have set up to create a CSV file. He emails this to the central database admin person. He "appends" A1 & A2 to the empty database. These records have PK=1 & PK=2 on both (A)'s laptop and the main database.

(B) exports his records and the data admin person appends these. The main database now contains A1, A2, B1, B2 with PKs = 1,2,3,4 respectively.

(B) now gets some feedback from his record B2 and updates it on his laptop. He also does another check and adds it to his laptop. Call this B3. He exports again.

Data admin appends. Becasue the PKs don't match, the main database will now contain A1, A2, B1, B2, B1, B2, B3. The two B1s will be duplicates, the two B2s will be the original and the updated record. A complete mess!

What needs to happen is that when the data admin person appends the 3 records from (B)'s laptop, the main database should ignore B1 (it's the same and unchanged), update B2 (it's been modified) and add B3 (it's a new record).

In my inexperience, I assumed that to do this I would need to make sure that ALL PKs were unique so that, for instance, (A)'s records would have a PK of A01, A02 and (B)'s records would have PKs of B01, B02 and B03. The main database would not then need to re-create PKs and when (B) gets his export inputted to the main database, the main database records could be over-written because they can be automatically identified.

Does this make sense? I'm not sure anymore lol.

no-one will use the main database to add or change any records. This will only be used for reporting. consequently the laptops will always contain the most up to date versions of each record. There is no point (and it would be too messy) in sending the main database to all the laptops.

hope this helps

thanks for your interest

Dave

 

[spikepl]

Forget PK's - unneccesary complication. Each inspector has an id. Each record has a datetime stamp when created. If not then add one. The two together are unique. Thats enough to find the record locally or centrally. What each db uses as PK is then irrelevant, so just leave them as autonumbers.

 

[Aristillus]

Spikepl,
I hear what you're saying but I'm on a steep learning curve. I was getting hung up on PKs because this is what Access uses to automatically append and/or update records. I assume that if i used your method of Inspector ID plus datestamp I would have to write some VBA to work out what to add / update. Surely this is more complex than getting the PKs unique and using the build in append query??

I'm not being awkward, I'm just not experienced and don't know how to go about doing what you suggest.

 

[spikepl]

You just need two queries with criteria One update, that updates records already existing in central db, ie possessing the values of inspectorID and datestamp that are in your local db, and then an append query, that adds all records that do not have a matching record in the central db. All this without worrying about any PKs.

 

[spikepl]

On reflection - the local autonumber and the inspectorID form, too, a unique id, as you sussed out from the outset. There is no point in concatenating the two - the records in the central db just need a field for each of these values and that's it. So even the datestamp could be skipped.

 

[Aristillus]

Ok, I'm getting a bit bogged down here and can't seem to make headway. What seemed like a simple task is proving a nightmare!
Can someone tell me if I'm doing this the wrong way:

1... An inspector enters his records into his local database on his laptop
2... He clicks an "export" button which creates a csv file which he then is shown how to locate and email to the guy who inputs to the main database on the internal network.
3... The inputter runs an identical database (ie, the design is the same, obviously the records are not) and clicks an "import" button.

Here I'm stuck! This is where I've got to in my head! Remember that some records in the csv will have matching records in the main db and will need updating whereas some will be new and will need appending.

4... The csv file is imported to a new "holding" table. (Can't import to the real table because updates won't work, only appends (?))

5... Run an append query to add the new records (the matching records will be ignored(?))

6... Run an update query to update the matching records (the new records will be ignored (?))

I'm struggling with this because I think I might be on the wrong track. I can't believe that it can be this complicated just to add and update records to a central database!!

Cheers

Despondent dave

 

[spikepl]

What is the specific problem? You failed to mention that.

Also, in principle the Update should be run before Append, otherwise you'll just be updating just-added records for no purpose.

 

[nanscombe]

It would be very handy if you could post a database with the main data and import tables in. It doesn't need to have real data or forms in, just a couple of dummy records.

It's much easier to solve a problem if you can see it.

 

[spikepl]

Hmm maybe - but specifying the issue is always a good start

 

[nanscombe]

If we can see some dummy data we may be able to spot fields, other than the Primay Key, that would be suitable for identifying unique records rather than guessing.

Just trying to make solving any problems a bit easier.

 

[spikepl]

I don't get it. I thought that issue has been debated and settled. But let's see what OP's opinion is.

 

[spikepl]

tblGlobal (table in main db)
---------
GlobalID (autonum)
InspectorID (whatever ID for inspector)
LocalID (Long, ID for the record from local table for given inspector)
other data in the record

tblLocal (table in local db)
---------------------------
LocalID (autonum)
InspectorID (id for the inspector)
other data in record, same as in tblGlobal


with the above you have enough to id what already is in global db and what isn't, and update/append as needed, while still keeping things simple by not messing with PKs.

 

[Aristillus]

Hi,
The issue is that each time I resolve one part I come up against another obstacle. Never sure whether to plod on or start again!
I have a button which exports the code to a txt file in csv format, using some VBA code. That works fine because I can open the txt file in notepad and check it.
On the main db I then have an "import" button which has the following code:

Function obsimport() As Boolean
CurrentDb.TableDefs.Delete ("observation-transfer")
DoCmd.TransferText acImportDelim, "Observation-transfer Import Specification", "observation-transfer", "c:\otl\observation-transfer.txt", True
DoCmd.OpenQuery "NewUpdate", acViewNormal, acEdit
DoCmd.OpenQuery "NewAppend", acViewNormal, acEdit
End Function

The idea is to create a table called "observation-transfer" in the main database and use the import to get the exported txt file into that table. That works fine.
Then I want the update query to update all the records that match the records in the main table called "observation". This is where it goes wrong. I created a select query using all the fields from the table "observation-transfer" and converted it to an update query. I then manually typed in the destination in the format [observation].[field1] (field1 representing the actual field name of course).
but when I run it, I get a requester "enter parameter value" observation.id. If I press enter then it goes through all the fields.

That's where I'm at and thoroughly stuck. By the way, I guess I could have exported a table and imported the table rather than convert to csv and back but that was done for other reasons which are no longer valid, but no point changing it yet as that bit is working.

thanks
Dave

 

[jdraw]

Show us this query and adjustments

I created a select query using all the fields from the table "observation-transfer" and converted it to an update query. I then manually typed in the destination in the format [observation].[field1] (field1 representing the actual field name of course).
but when I run it, I get a requester "enter parameter value" observation.id. If I press enter then it goes through all the fields.

The enter parameter value sounds like a typing error (Access can't find one of the fields that was in the query and assumes it is a parameter). When you just hit enter, Access assumes a NULL value (my guess) or assumes no filtering required and gives back all the data.

Also

Function obsimport() As Boolean

typically indicates you are going to use a returned value for something. Since you don't assign a value to obsimport, you could use( and perhaps should use) a Sub rather than Function.

Just my $.02

 

[Aristillus]

Thanks, I agree about the function. Shouldn't affect the operation though, I don't think.
I also assumed a typo, but I have checked and everything is as it should be. Every field comes up as a required parameter though, not just one or two.

 

[jdraw]

Can you show us the query SQL and the adjustments?

 

[Aristillus]

Where would I find that? I just created the query using "create query in design view", used "convert to update query" and then manually typed in the destination fields.