Access vs Filemaker add/update records

[ReAp]

I'm not trying to bash Access, but can someone tell me why after all of these versions of MS Access, that is doesn't have a update/add records feature like Filemaker Pro does?

I have an employee access database at work, the usual employee info, with the primary key of the ssnumber. A long time ago someone who was a VB programmer made me a program that updated my access database with a csv file. The primary key was used to update or add new record. If the SSNumber was found it updated all info in the record. If it wasn't found it added a new record.

Of course when switching to a new PC the VB program ceased to work anymore, the guy is long gone from the company... I was messing around with Filemaker the other day and saw how completely simple it is to do this task, as they have an automated feature to do this (in at least the last 3 versions).

Why oh why hasn't MS access added a feature like this? It is such a basic everyday function needed in a database. I know I can append records, but that will only do the add records portion and to cull out only new employees out of a csv file that has over 10K employees that is not an easy task.

Does anyone know of any add-on components that would do something like this?

 

[Banana]

While there isn't a button to do it or even a SQL statement to do it, it is doable to set up a small function that will process the additions as both update and inserts. This can be done by doing two queries and having it executed in a single call to function, perhaps by passing an argument for file path if so desired.

The first query would be an update. Something like this:

UPDATE tblTarget t 
INNER JOIN tblSource s
   ON t.ID = s.ID
SET t.ColA = s.ColA,
      t.ColB = s.ColB,
      t.ColC = s.ColC;

Second would then add any missing records:

INSERT INTO tblTarget t (ColA, ColB, ColC)
SELECT s.ColA, ColB, ColC
FROM tblSource s
LEFT JOIN tblTarget t
   ON s.ID = t.ID
WHERE t.ID IS NULL;

Let's call those two queries qryUpdate and qryAppend. A VBA function can call them both in one go:

Private Sub ProcessIt()

DoCmd.OpenQuery "qryUpdate"
DoCmd.OpenQuery "qryAppend"

End Sub

Of course, you may want to work out the exact process of how to import the csv file, or perhaps use it directly in a query or import it raw into Access and process the merge within Access. It really depends on how csv file would be structured.

Yes, I do agree that this would be a handy feature, but we just have to suit up with what we have.

 

[boblarson]

And remember - Not all programs are the same and not all who develop those programs can put all features that are in another program into their program. There are limited dollars for development available and, while you feel this feature is a "no brainer," it would appear that it is not the case for others. So, suggestions are good. Asking why this program doesn't do what that program does isn't really all that helpful.

 

[LPurvis]

Without getting into the whole development platform argument (it's an old one - whatever the "opposition" software happens to be at any given moment)...

Yes the process if importing record as you want isn't necessarily built in.
Yes you can't import the data in a dual update/insert role directly.

However taking Banana's suggestion for a moment, the assumping of a tblSource of imported records is a vital difference. If you can either import first to this (as a holding or "work" table) then indeed you have great flexability to maniuplate the data first. (You could have this a linked table - but you would lose some of the options then).

Specifically I'm thinking about how your PK is an SS Number, then it must be editable (as opposed to an Autonumber).
Imported, Uniquely indexed and editable?
Then you can do this with just a single query.

Adjusting Banana's update query just slightly (in blue):

UPDATE tblTarget t 
[COLOR=blue]RIGHT [/COLOR]JOIN tblSource s
   ON t.ID = s.ID
SET [COLOR=blue]t.ID = s.ID,[/COLOR]
      t.ColA = s.ColA,
      t.ColB = s.ColB,
      t.ColC = s.ColC

Existing rows are updated, new rows appended.
It's merely a nice little result of Jet's own Update syntax. Not such a bad little engine.
It ships with a decent little application too. ;-)

Cheers.

 

[Banana]

LPurvis,

I bow to your brillance.

Whodunthunkit?