Updating Tables Constantly

[anotherlevel]

I will be updating a table in my database every week, via imports from a xls file. What I have going on now is a very inefficient way of updating. What I do is import into a table (x) and just keep adding to the table through the import wizard saying add to exsiting table. Then do a make-table query where I do "group-by" to get the unique ones and then run it to get the new table. I am sure there is an easier way to do this. Possibly, find duplicates in the import and then just append to the main table? Don't know how to do that one? Also I wanted to create a button that will ask for the xls file and then do the appending and clean up once the file has been asked for...any help please!

 

[pdx_man]

Link the Excel workbook as a linked table and then create an Append Query where you get the unique values.

File ... Get External Data ... Link Tables ... File type = Excel

 

[anotherlevel]

Thanks, but this is something that is going to be past around so linking the table only means that i will be using it at this specific location. I need it where I can have a button that someone presses and it will input the external data from a xls file. The xls file gets renamed incrementally, so i just need to take the information that is new.

Also, how do you do an append query for unique values? Is there a way to compare two table and just export the ones that don't match?

 

[pdx_man]

Even better. Update the link to the location of the new file. That way, you can have a static name within your Access DB and have queries written to reference it. There are many posts on this forum to get a filename, so search for that function.

Public Sub UpdateLinkLoc(FileLoc As String)
Dim TblDef As TableDef
Dim DBRefLoc As Integer
Dim dbs As DataBase

    Set dbs = CurrentDb
    Set TblDef = dbs.TableDefs("Trade_Log")
    
    Debug.Print TblDef.Connect
    DBRefLoc = InStr(TblDef.Connect, "DATABASE")
    TblDef.Connect = Left(TblDef.Connect, DBRefLoc + 8) + FileLoc
    TblDef.RefreshLink

End Sub

(past = passed?)

 

[anotherlevel]

Thanks for the help...will look for it!