Results 1 to 3 of 3
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Refreshing and updating tables every day from Excel

    Hello,

    i have table like below which i am importing from Excel to tbl_temp_Dokumenty:

    Numer Dokument Obowiązkowy
    1 Wniosek o zatrudnienie TAK
    2 Kwestionariusz kandydata NIE
    3 Kwestionariusz pracownika TAK
    Number is a number of Document, Document is document name and Obowiązkowy is isNecessary.

    Now my update query is:

    SQL:

    Code:
    UPDATE tbl_slow_Dokumenty INNER JOIN tbl_temp_dok ON tbl_slow_Dokumenty.Numer = tbl_temp_dok.Numer SET tbl_slow_Dokumenty.Obowiązkowy = [tbl_temp_dok].Obowiązkowy, tbl_slow_Dokumenty.Dokument = [tbl_temp_dok].Dokument;
    Picture:
    Click image for larger version. 

Name:	Bez tytu?u.png 
Views:	13 
Size:	15.9 KB 
ID:	29780


    So i am updating my tbl_slow_Dokumemty using Number as common field within inner join.



    It is okey... but problem is that users can change manually Numbers in table in Excel and if they write for example 3 times the same number : my update query would take the last Dokument name (it is appropriate working, my query will work here fine).

    So question is how to prevent duplicates inputed by user?

    Using update query only using text field and delete numbers? (it is dangerous way because users can have duplicates document names (spaces or strange sings in the end of cell)
    or maybe check by Access if there are duplicates in tbl_temp_dokumenty and if yes exit macro with msgbox message?

    Thank you for your help,
    Best Wishes,
    Jacek

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You cannot prevent users from changing Excel files. And it would be very difficult to try and match up the records, as you say the users can change the document name or anything they want. The only way to close this up is to replace the Excel with the database and don't allow the users to use Excel for this. Or else write a macro in Excel to put the rules in there.

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you aytee111.

    Uff i thought about creating this table in Access. Maybe i will change a little my idea and replace Excel with Access ;-)

    I am losing the topic,
    Jacek Antek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-23-2015, 06:59 PM
  2. Linked tables and refreshing
    By offie in forum Import/Export Data
    Replies: 1
    Last Post: 06-20-2013, 01:42 PM
  3. refreshing tables?
    By danbo in forum Access
    Replies: 1
    Last Post: 12-23-2011, 03:05 AM
  4. Refreshing Linked Tables Hangs
    By blamb in forum Access
    Replies: 0
    Last Post: 12-05-2011, 12:36 PM
  5. Link Tables Not Refreshing
    By goodguy in forum Import/Export Data
    Replies: 14
    Last Post: 01-04-2011, 12:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums