Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

VBA updating & moving records

Hi there,

I was wondering if anyone could help me. I am importing data from an excel sheet to a temp table. From there the other tables will have information inserted into them from the temp table. Th eproblem I am having is that duplicate data is not being updated into original tables after being inserted into the temp table.

For example if I insert an excel sheet and update the temp table and then the tables I want the information to go into it works perfectly, but if I try to use the same excel sheet and try to carry out the same process the temp table updates but the other tables dont. Duplicate data shouldnt matter and new rows show automatically be created but it doesnt seem to work here is my code:

  DoCmd.RunSQL ("Delete * from temp")
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Temp", Sfile, False
   
    Dim rs1 As Variant
    Set rs1 = CurrentDb.OpenRecordset("SELECT F1,F2,F3 from Temp")
         
Do While Not rs1.EOF
        SQL = "Insert INTO ManagedElementFamily (Name,Acronym,Description) VALUES ('" & rs1![F1] & "','" & rs1![F2] & "','" & rs1![F3] & " ')"
        MsgBox SQL
        DoCmd.RunSQL SQL
        rs1.MoveNext
    Loop

would be grateful if someone could help
0
Pungwick
Asked:
Pungwick
  • 4
  • 3
1 Solution
 
Arthur_WoodCommented:
is there a question in here somewhere?
0
 
Arthur_WoodCommented:
sorry, but what I saw first had no question.
0
 
PungwickAuthor Commented:
hehe
sorry the question is that the code I have written works but when i Have duplicate values it doesnt work

For example if I insert an excel sheet and update the temp table and then the tables I want the information to go into it works perfectly, but if I try to use the same excel sheet and try to carry out the same process the temp table updates but the other tables dont update.

the question is:

my code only works for data that is not duplicate.If I had duplicate data. I want to insert data that is duplicate ie in the excel sheet even if 1 field is the same as another the data doesnt insert ,understand what I mean?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Arthur_WoodCommented:
it is possible that when you use the DoCmd.RunSQL, if an error occurs (for instance, does the table ALLOW duplicate rows? - is there a Primary key Contraint on the table, or a UNIQUE constraint on any of the fields?), it may be the case that the error message is not captured in teh calling code, but since an error occured in the table, the INSERT did NOT get completed.  Just a wild guess.

Why would you wnat ot have DUPLICATE entries in the first place?  Once the record has been inserted, why do taht AGIAN for EXACTLY the same data?

AW
0
 
PungwickAuthor Commented:
Hi,

I am inserting the data into a temp table and then inseting it into the reevant tables to stop confusion. The temp table is deleted every time it is created.I create it on the fly and after using the data from it I delete it. The primary keys are auto numbers so when I insert into the other field of the table the primary key automatically increments. I know think there are any constraints on any of the fields.

Any additional ideas???
thanks
0
 
PungwickAuthor Commented:
hi there,

Thanks a lot for all your efforts you were correct there was a field constrant. The field was'nt allowing in duplicate values. Thnaks for your help much appericated.

M.Ahmad
0
 
PungwickAuthor Commented:
Answer gave me many ideas and was infact spot on. Excellent. Thanks for your help.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now