Topics Search

Import and overwrite?

Import and overwrite?
Views: 146
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:

Similar posts...

 

Import from Excel and overwrite primary key records

Import from Excel and overwrite primary key records Icon
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....
 

Export data to Excel : overwrite data instead of file

Export data to Excel : overwrite data instead of file Icon
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.
 

Property not found, error occurred while trying to import file

Property not found, error occurred while trying to import file Icon
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.
 

DoCmd.TransferText acImportDelim is appending data

DoCmd.TransferText acImportDelim is appending data Icon
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?
 

Question on TransferText Action in a Macro

Question on TransferText Action in a Macro Icon
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?
 

Import multiple tables with VBA - overwrite originals.

Import multiple tables with VBA - overwrite originals. Icon
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
 

Overwrite files without the promt if you want to overwrite.

Overwrite files without the promt if you want to  overwrite. Icon
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.
 

Importing data with leading zeros

Importing data with leading zeros Icon
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?
 

Overwrite on existing Record

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

what should I do? how can I get.
 

Finding Median in Query

Finding Median in Query Icon
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