I would like updating existing table in ACCESS, with CSV file automatically using VBA code, but I do not know how because I only bases knowledges in VBA programmation .
I do not want to create a new table every time, I want the record to be added to the existing table if they are not present in table or I want that if there was a change of value in a field of a record already present, the value is updated.
A new record should be included in the table when the pNumber is not present, because it is the primary key of my table. If pNumber exists, then it is necessary to check if all values are the same. If so, do nothing and skip to another record in the CSV file, otherwise change the value of the field (s) concerned. Thank you very much for your help, I really need someone give me a VBA code for this because I am currently blocked. With this code I can develop in VBA that I want to do, with my basic knowledges.
I put an attachment an example of a CSV file. I put the name of fields, the sames I used in the table ACCESS, on the first line of this file. The fields is integer or floating or just text.
In general, because you want to do either an INSERT INTO or an UPDATE, the easiest way in my mind is to do this in multiple steps. Besides which, "Divide and Conquer" worked for Julius Caesar, it ought to work well for you as a matter of principle.
Also, I am talking in terms of macros because you have expressed some lack of experience with VBA. Using macros now lets you get this working now and then lets you retrofit the code later
Besides which, there is an option to convert a macro into VBA, which you would use once you were happy with what the macro sequence did, and then you could have your VBA code generated for you. Granted, doing it this way means no global optimization, but at least you would have a starting point for VBA.
First, import the CSV file to a temporary table. You might wish to save the Import steps when you do this because after the first import, you can just tell Access to import using your saved import scheme. Be sure to NOT allow Access to declare a primary key for this import. There is a macro action for importing a spreadsheet to an arbitrary table with a fixed name that you supply as part of the macro.
Second, if the input key field is blank (and one assumes that the key is auto-numbered in your main table), you can write two bulk queries such as:
Code:
INSERT INTO MyTable (Fld1, Fld2, Fld3, ...) SELECT Val1, Val2, Val3, ... FROM TempTable WHERE Nz( PNumber, 0 ) = 0 ;
DELETE * FROM TempTable where NZ(PNumber, 0) = 0;
If the names are always the same, you can make this a stored (and thus NAMED) action query and in that case, a macro action exists to run an SQL action query. The first query inserts the unnumbered records, which works if the PNumber field is autonumbered. The DELETE query eliminates those records for the next step. This method would NOT work as written if the table's PK is not auto-numbered. See also the comments below about date fields.
Third, you can try to write another couple of queries based on the input that is again runnable by a macro action. You said PNumber was your primary key but that on first import, PNumber would be blank. The implication is that a change record WOULD have the PNumber field matching the entry in your table.
Code:
UPDATE MyTable INNER JOIN TempTable ON MyTable.PNumber = TempTable.PNumber
SET MyTable.Fld1 = TempTable.Fld1, MyTable.Fld2 = TempTable.Fld2, .... WHERE
(MyTable.Fld1 <> TempTable.Fld1) OR (MyTable.Fld2 <> TempTable.Fld2) OR ... ;
Now, why does this work? Well, the previous step's DELETE gets rid of any records that were just inserted because they had no PNumber values. What is left HAS a PNumber, so the INNER JOIN in the UPDATE should work with one exception (see below). The query updates entries matching extant entries in the table where the fields that you test have at least one difference. If there are fields that should NOT be updated (like, say, an original date) then you don't SET their values. If there are fields that should NOT participate in the to-update-or-not-to-update question, leave them out of the where clause.
The only fly in this ointment is if you ever can have any imported records in the spreadsheet that make it into TempTable and they have a PNumber that doesn't exist in MyTable. (You don't say whether that is a possibility, but if it ever DOES happen, you run into a big headache.)
When all is done, the last step is to delete the temporary table, and yes, there is a DeleteTable macro action. So you could end up with a macro that is just a series of actions such as the ImportTable, RunSQL, and DeleteTable actions. Plus, of course, the EndMacro step.
The idea is to take the simple way first, then start developing more complex steps when you are ready to tackle them. Starting with a macro and THEN developing it into VBA code is the easiest way to do that and still get the code up quickly.
Thank you very much for your response and your help. However, I still have another question for you. How can I compare all records in the temporary table to all in the table that I want to update? How do I program the loop to pass all the records? Thank again
I forgot! How to program this loop to read all records from the temporary table and insert them into the table if the record does not exist or update if it exists and first in this loop of course, remove all empty records ?? In a While loop? For? What is the equivalent of an EOF for a table?
I tried to set an example of VBA code but I still have a compile error in this line ****. Can you help me please?
Code:
where tmpPRODBR : My temporary table
tblPBRUT : My existing table
Dim db As Database
Dim rs, rs2 As Recordset
i = 0
tmp = 0
Set db = CurrentDb()
'Set rs = db.OpenRecordset("INSERT INTO FPRODBR(PNUMERO) SELECT champs3 FROM tmpPRODBR")
Set rs = db.OpenRecordset("tmpPRODBR")
rs.MoveFirst
Do While rs.BOF = False And rs.EOF = False
'tmp = Val(rs.Fields("champ1").Value)
****Set rs2 = db.OpenRecordset("SELECT * FROM tblPBRUT WHERE PNUMERO = rs.Fields("champ1").Value")
If rs2.BOF = True And rs2.EOF = True Then
'insert tblPBRUT
'db.Execute ("insert into clients(nom_client") values ('" & Nom_client.Value & "'")
MsgBox ("Insert")
Else
'update tblPBRUT
'db.Execute ("update clients set statut_client = '" & stat & "'")
MsgBox ("Update")
End If
rs.MoveNext
i = i + 1
Loop
MsgBox ("La valeur de i est:" & i)
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
The SQL I presented DOES NOT NEED a loop. It is a bulk operation. It does ALL RECORDS AT ONCE (for programming purposes) in a single query execution. The loop is already implied in the fact that you are using SQL on a table. The trick then isn't the loop but rather is what you specify in the WHERE clause so that each particular update affects only the desired records.
In general, because you want to do either an INSERT INTO or an UPDATE, the easiest way in my mind is to do this in multiple steps. Besides which, "Divide and Conquer" worked for Julius Caesar, it ought to work well for you as a matter of principle.
Also, I am talking in terms of macros because you have expressed some lack of experience with VBA. Using macros now lets you get this working now and then lets you retrofit the code later
Besides which, there is an option to convert a macro into VBA, which you would use once you were happy with what the macro sequence did, and then you could have your VBA code generated for you. Granted, doing it this way means no global optimization, but at least you would have a starting point for VBA.
First, import the CSV file to a temporary table. You might wish to save the Import steps when you do this because after the first import, you can just tell Access to import using your saved import scheme. Be sure to NOT allow Access to declare a primary key for this import. There is a macro action for importing a spreadsheet to an arbitrary table with a fixed name that you supply as part of the macro.
Second, if the input key field is blank (and one assumes that the key is auto-numbered in your main table), you can write two bulk queries such as:
Code:
INSERT INTO MyTable (Fld1, Fld2, Fld3, ...) SELECT Val1, Val2, Val3, ... FROM TempTable WHERE Nz( PNumber, 0 ) = 0 ;
DELETE * FROM TempTable where NZ(PNumber, 0) = 0;
If the names are always the same, you can make this a stored (and thus NAMED) action query and in that case, a macro action exists to run an SQL action query. The first query inserts the unnumbered records, which works if the PNumber field is autonumbered. The DELETE query eliminates those records for the next step. This method would NOT work as written if the table's PK is not auto-numbered. See also the comments below about date fields.
Third, you can try to write another couple of queries based on the input that is again runnable by a macro action. You said PNumber was your primary key but that on first import, PNumber would be blank. The implication is that a change record WOULD have the PNumber field matching the entry in your table.
Code:
UPDATE MyTable INNER JOIN TempTable ON MyTable.PNumber = TempTable.PNumber
SET MyTable.Fld1 = TempTable.Fld1, MyTable.Fld2 = TempTable.Fld2, .... WHERE
(MyTable.Fld1 <> TempTable.Fld1) OR (MyTable.Fld2 <> TempTable.Fld2) OR ... ;
Now, why does this work? Well, the previous step's DELETE gets rid of any records that were just inserted because they had no PNumber values. What is left HAS a PNumber, so the INNER JOIN in the UPDATE should work with one exception (see below). The query updates entries matching extant entries in the table where the fields that you test have at least one difference. If there are fields that should NOT be updated (like, say, an original date) then you don't SET their values. If there are fields that should NOT participate in the to-update-or-not-to-update question, leave them out of the where clause.
The only fly in this ointment is if you ever can have any imported records in the spreadsheet that make it into TempTable and they have a PNumber that doesn't exist in MyTable. (You don't say whether that is a possibility, but if it ever DOES happen, you run into a big headache.)
When all is done, the last step is to delete the temporary table, and yes, there is a DeleteTable macro action. So you could end up with a macro that is just a series of actions such as the ImportTable, RunSQL, and DeleteTable actions. Plus, of course, the EndMacro step.
The idea is to take the simple way first, then start developing more complex steps when you are ready to tackle them. Starting with a macro and THEN developing it into VBA code is the easiest way to do that and still get the code up quickly.
You have presented a solution which seems to address my desired result: updating an MS Access table with data from a csv file. I have thoroughly read your post and I do not understand it So if you can put aside your views on my "attitude" I will try to check same at the door as you have advised.
As I said, I want to update MS tables with data from a csv file. I have been working for hours on simply opening a recordset, updating it, but it seems mighty cumbersome. Your solution seems cleaner, maybe?
Don't understand your first step: you say use a macro to create a temporary table in access from the csv file. I don't find a premade macro for that. I don't see in you post the specifics of how to do that.
Then the next step is simply SQL for an UPDATE query? Simply updating the target MS access table?
Thank you very much sir for your attention and time.
