Import and overwrite?

I want to be able to import data from excel into a table from which a report is generated. However, quite often, the attributes within each record will change (obviously excluding the primary key). What I really want to be able to do is import the data and have any duplicates overwrite what is currently in the table as I am always interested in the most recent data. Is there any way to do this?

If not, one way I'm thinking I could possibly get around it is to change my primary key to an Autonumber field (currently I don't use any autonumbers in the table) and then run a find duplicates query on the field I am currently using as my primary key. Firstly, would this work? Secondly, having done this, how could I get the least recently acquired part of the duplicate to be ignored in the final report

Sponsored Links:

Related Topics

Import from Excel and overwrite primary key records
I have a database that assists my understaffed office with reports, memorandas, and other types of helpful forms because the current Army systems do not have exactly what is required. (Like automatically put names into pregenerated forms, or memos).

What I do is download a report from the Army database into an excel format and then I upload it into the access database. This brings in along with their name, unit, training info and information that may change weekly. Now the problem I am running into is having the data from the excel import replace other records. I have a primary key in the table it goes to because that table is linked to another table where another record for the soldier is located (this has information that is not in the army databases that we want saved when we do an import).

Now if the record is not in the access database it will add that one, but will not overwrite the records already there. It really doesn't help so much because then we would have to manually do these changes (like last fitness test results and date, etc) which when you have 400+ soldiers that can be very tedious....


Importing data with leading zeros
We recently upgraded from Access 2003 to 2010. I have a table that I overwrite each month with new data from a tab delimited text file. There is a field in the table that I have formatted the data type as text. In the previous version of Access I could import the data and it would keep any leading zeros during the import. Now it is dropping the leading zeros when I import the data even though I have specifically formatted the data as text.

Does anyone know how I can import my data and keep the leading zeros


DoCmd.TransferText acImportDelim is appending data
I am using DoCmd.TransferText acImportDelim to import a number of text files. I thought this command would overwrite current table data with new, but instead, it is appending.

How do I get this command to overwrite the current table with new data each month?


Property not found, error occurred while trying to import file
I'm trying to import a tab delimited text file into Access and I get this error Property not found.
An error occurred trying to import file [blah]. The file was not imported.

I imported this exact file yesterday without a problem. I was importing it again to overwrite the first one because I wanted to change some of the Long Integer fields to Text.

so I tried to just import the file again from scratch, not changing anything (leaving the Long Integer fields as theyare), but I still get this error.


Import multiple tables with VBA - overwrite originals.
I'm currently working on an ongoing project that requires regular updates to the forms and code of a database.

I would like to be able to click a button and import all the tables from the old version to the new one, and OVERWRITE the data.

I may add new tables in between versions, so deleting all tables before import is not an option.

my current code, does almost what I need, but it adds a number on the end, and with 50 or so tables, it's just as time consuming as the manual method.

my current code:

Dim db As Database Dim tdf As TableDef
Set db = OpenDatabase("C:\Users\userdir\Desktop\stuff\BACKUPS\main backup.mdb")
For Each tdf In db.TableDefs
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Users\userdir\Desktop\stuff\BACKUPS\main backup.mdb", acTable, tdf.Name, tdf.Name
Next tdf
Set db = Nothing

I am assuming I'll probably need to index the table names in a variable somewhere and delete tables mentioned in said variable


Question on TransferText Action in a Macro
I have an existing Macro that contains the following action:

TransferText
Transfer Type: Import Delimited
Specification Name: STAT01 Import Specification
Table Name: 01 Data
File Name: E:\HNA Shared Other\New IT folder\I90 STATS\
Database\STAT01.tab
Has Field Names: No
HTML Table Name:
Code Page:

My question is:

When you import data to the specified table does it always append
the new records to the table OR does it overwrite all the transactions in the table?


Overwrite files without the promt if you want to overwrite.
Im having some problems with my macro's.
I've used the "output" macro to output some tables from the database into a windows file directory. Until then it works.

But I want to disable the prompt which asks me if I want to overwrite the excisting file and I want to disable this prompt and that the answer is always yes.


Overwrite on existing Record
Now I want to do overwrite on existing data in access table.

what should I do? how can I get.


Importing data with leading zeros
We recently upgraded from Access 2003 to 2010. I have a table that I overwrite each month with new data from a tab delimited text file. There is a field in the table that I have formatted the data type as text.

In the previous version of Access I could import the data and it would keep any leading zeros during the import. Now it is dropping the leading zeros when I importthe data even though I have specifically formatted the data as text.

Does anyone know how I can import my data and keep the leading zeros?


Linking Workbook and Overwriting Excel File
for taking the time to read this.

I use an online software to process customer transactions. I export the transaction data to an Excel file, import the file to Access, and then use Access to create reports and receipts.

Here's what I would like to do:

Instead of importing the file to Access, I would like to create a link to the Excel file. Creating the link is no problem. The problem comes in when I want to overwrite the Excel file with a new file. Even though I name the file the same name, etc, Access make me create a new link to this new file.

Is there a way to overwrite the linked Excel file without creating a new link from Access


Export data to Excel
I have a macro that exports data to an excel spreadsheet.

When I run the macro I receive a prompt saying 'The file filename already exists. Do you want to overwrite?'

I dont want to overwrite the entire file but what I would like to do is overwrite the data held in sheet one of the excel file. Is this possible?

I am using the OutputTo action for the macro export.


Table Overwrite
If I overwrite table A with table B, it has the same fields/structure as table A, and name it the same as table A, will existing queries that read from table A work correctly?


Overwriting tables
I have drawn a blank.each week I need the same queries and reports the only difference is the week date range.I import from an Excel file.How can I overwrite the table completely to get the data using a date range?


Finding Median in Query
I currently have a Query set to give me the Average of # Days, and I am needing to change it to show me the Median and not the Average.

How would I do this?

I have included my SQL below.

SELECT [1 All Import].MSTSF, [1 All Import].[TOS Code], [1 All Import].[Rendering], [1 All Import].[ Name], [1 All Import].Net, Count([1 All Import].[Ref #]) AS [CountOfRef #], Count([1 All Import].[Apt Date]) AS [CountOfApt Date], Avg([1 All Import].[# Days]) AS [AvgOf# Days], Avg([1 All Import].[Distance]) AS [AvgOfDistance] FROM [1 All Import] GROUP BY [1 All Import].MSTSF, [1 All Import].[TOS Code], [1 All Import].[Rendering], [1 All Import].[Name], [1 All Import].Net


Update Table
Using Access 2000. I have a fairly simple database which is updated with data weekly through an import routine, however, one table in the database needs to be updated with some new records which isn't included in the weekly update. I could speak to the 6 other users which use a copy of the database to talk them through the changes needed but they aren't very experienced with 'behind the scene' workings.

My ideal would be to export my table that I update and have them import the table into their database and thus, overwrite their original table. I looked at sending them the whole database less the data but they can and have created their own queries which would be lost.

Is there a way to copy and replace a table in another database?


Transferred spreadsheets do no overwrite
I have an Access application where a macro transfers two spreadsheets to a designated directory. This works fine if the directory is empty but if old files are in the directory they are not overwritten. Is there a way for the transferred files to overwrite the existing files in the directory?


Import from Excel using Import Spec
Is it possible to import a spreadsheet into Access using an import specification? I've created import specifications to successfully import spreadsheets into Access and I would like to automate those imports using VBA. But, I cannot find the 'DoCmd.TransferSpreadsheet' syntax that'll work. I do not want to convert the spreadsheets to .txt or .csv


Overwrite existing tables
I took a copy of an Access 2003 .mdf and made several changes to forms and code. However I did not realize that some users were still entering data into the 'old' database. What I need to do is overwrite the tables in my modified mdf with the most up to date data held in the old database I.e.without altering the forms/code/reports etc. Is there an easy way to do this?


Overwrite existing tables
I took a copy of an Access 2003 .mdf and made several changes to forms and code. However I did not realize that some users were still entering data into the 'old' database. What I need to do is overwrite the tables in my modified mdf with the most up to date data held in the old database i.e.without altering the forms/code/reports etc. Is there an easy way to do this


Filling a table with a count
I have a big table of source data 422k lines that I want to move into an existing database, I want to overwrite all 451k lines in the existing database.

new table has this format:
Column 1; Column 2; etc; Column 13

The existing table I want to replace has this format:
Count Column; Column 1; Column 2; etc; Column 13

So I assume I need to add a Count column to the new table, then fill it with the numerical count, then use the Get External Data, Import function to import the new data.

My question is: How do I fill that new count column I need to create?

and/or Is there a better way to do this entirely?