VBA updating & moving records

Posted on 2003-03-28
Medium Priority
Last Modified: 2010-05-01
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

would be grateful if someone could help
Question by:Pungwick
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 44

Expert Comment

ID: 8225648
is there a question in here somewhere?
LVL 44

Expert Comment

ID: 8225673
sorry, but what I saw first had no question.

Author Comment

ID: 8225715
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?
Technology Partners: 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!

LVL 44

Accepted Solution

Arthur_Wood earned 80 total points
ID: 8226720
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?


Author Comment

ID: 8237314

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???

Author Comment

ID: 8237364
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.


Author Comment

ID: 8237367
Answer gave me many ideas and was infact spot on. Excellent. Thanks for your help.

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question