I have info in Excel workbooks coming from various individuals. I take bits and pieces of data from each and the update an Access DB. Is there a way to simply replace the table in Access without affecting the relationships that the current version of the table has?
View RepliesI need to import an excel sheet containing updated tickets to my access ticket database. They have unique IDs in the form of a "Ticket ID" field.
I could just use an append query to add the new tickets to my database, but there are some changes on the excel side to tickets that already exist in access as well. What would be the easiest way to facilitate this import and overwrite all tickets with matching Ticket IDs to the new ones contained in the excel file?
I already have the excel file imported into it's own table, so it's a matter of updating my main table off of this temporary table containing the excel tickets. The field names are the same across both tables, it's just a matter of updating the info contained in them.
This will need to be repeated for multiple different excel files, so the easier the better!
I have a main table which contains data on Oilfields. This is set up with an auto number for ID and a name of each oil field. I have other tables which contain things such as Pipelines, Wells and Platforms, which are linked back the the main table.
The problem is that the database was given to me with the links being between the Oilfield_Name, and not using the Oilfield_ID (which is not present in the 'satellite' tables). How can i add the oilfield_ID column to the 'satellite' tables so i can link them properly?! (i can obvioulsy physically add an 'Oilfield_ID' column to the 'satellite field', but how do i populate it with all the correct IDs from the main table, to suit what is in the 'Name' column of the 'satellite' field- does that make sense?). I suppose I need to replace all of the Names with the IDs?
This has been giving me such a headache. The Oilfields table has 2500ish records, with as many as 1200 in the other tables linked to it.
OK, here is my situation. I have an access database which is updated automatically every night with new data. My companies solution to this is each morning, the entire database is downloaded to my computer to be used by another application.
In one of the tables, there is a field that doesn't work for my purposes, so each day I have to manually open the table, and change the contents of that field for every record. For example, lets say I have the following table:
Field1 Field2 Field3 Field4
Rec1 Wigets Faucet Model1A
Rec2 Wigets Faucet Model1B
Rec3 Wigets Sink Model1A
Rec4 Wigets Sink Model1B
Rec5 Wigets Toilet Model1A
Each day I manually change Field2 so it reads "Total" and whatever is in Field2:
Field1 Field2 Field3 Field4
Rec1 Total Faucets Faucet Model1A
Rec2 Total Faucets Faucet Model1B
Rec3 Total Sinks Sink Model1A
Rec4 Total Sinks Sink Model1B
Rec5 Total Toilets Toilet Model1A
Each morning when I receive the new database download, they all go back to widgets.
Is there a macro or module I can create to automatically look at Field3, and then assign a new value to Field2 based on Field3's value?
Any help would be appreciated!!!!
Hi, can anyone tell me how to replace the data in one table with data from a backup table? I've tried:
Insert into Customer
Select *
From CustomerBackup;
but I keep getting key violations.
I have a table (which has a few relationships) that contains 10.000 records up-to-date, but I've been making major changes to my database working on an old version from early June, when it had +9000 records.
What I'm planning to do to update the table:
export those 10.000 (up to date) records to a Excel file
delete those outdated +9000 records manually (select all + supr)
import the data from the Excel file so my new database is up-to-date again .
Is this acceptable or could lead to weird errors in the future?
My table (excel spreadsheet) sits on another directory from the DB. If I delete the table on this directory and immediately replace it with a table with the same filename, format, etc (only the data has changed) will each database user have to relink the table on their desktop DB? Or will the forms/subforms/reports still maintain their relationships/functions and just display the new data?
View 2 Replies View RelatedHowdy,
I am trying to clean up one field of a large database. Currently this field has many records that are listed as "XXXX, Inc.". I am trying to find a way to change all of those to "XXXX Inc" in the entire database.
I saw raskews code snippet on changing multiple characters in a string, but I have to admit I have only use VBA in Excel before and having a hard time with the basics.
Does anyone have some code or ideas on how to do this or something similar?
:confused:
Thanks,
Will
Can I use the append query to update a table by replacing all existing information with updated information while at the same time adding all data that isn't currently in the table?
Right now every time I run the Append Query it just adds the same information as a new row instead of replacing the existing row with the updated information.
I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
View 1 Replies View RelatedWhen I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
I have a database with existing tables.I now want to add a whole bunch of records into my access tables.A lot of the data is repeated, and lends itself to my doing it in excel with it's better copy and paste abilities, and it's ability to easily increment alphanumeric fields.I can't seem to succeed in append importing to the bottom of my access table from my excel spreadsheet. All columns are the same.Can this be done, and if so how please?I'm on XP Pro with Office 2003 Pro.I'm also very much a newbie in Access.
View 2 Replies View RelatedDear All
I want to connect an Excel table with an Access table so when I make changes to excel table these changes to appear in the access table and use the access table for my web site.
The excel table looks like:
Room Type01/05/06-06/06/06 07/06/06-15/07/06
21/09/06-30/09/0601/10/06-31/10/06
* Standard Single 26 34
* Standard Double 44 59
* Standard Triple 60 80
When I try to connect this table with access I am getting an error reffering to the second line of the excel table where there are not numbers but dates.
The access connected table looks like:
Room Type01/05/06-06/06/06 07/06/06-15/07/06
#Number! #Number!
* Standard Single 26 34
* Standard Double 44 59
* Standard Triple 60 80
Any idea how I can solve this problem?
Hi,
Does anyone know how to export a pivot table to Ms Excel without using the specific button in the Pivot Table View of the form?
Thanks for the help:confused:
I'm trying to link a spreadsheet as a table in Access 2002. The link wizard is importing my number columns as numbers, even though I defined them as text. I need for them to be text in Access. How do I force this?
Thanks
Hello guyz,
With the help of 'Import External Data' wizard, my import did not happen. My datatypes and Col. Headers in excel are matching for what I have in access table. Alternatively, I selected to import the data into new table and all of the data got imported. My question, since I already have some data in table I want data to be imported, how can I do about bringing all the data from new table I created during import to the existing table. Please suggest. Thanks in advance.
hi
I need to move some data from excel into access table, can I do that
thank you
Hi!
I was importing the large array of accounting data into Access from Excel through File - Get External Data - Import. Several columns contained tangible accounting data (e.g. registration numbers) that had different formats like "23423 34", "32-3545" and in some cases even contained letter constants. Therefore, I have assigned TEXT type to this column while importing and was sure that all values are going to be stored as text. However, what has happened is: 18307769 turned into 1.83078e+007! this means tangible accounting data that was used as a key field for further linking turned into bull&&$%! :-(
I know this problem is very simple, so please, can you give me a hint as to how do I solve it in the future.
Thank you in advance.
--
Regards,
vb707
merchant credit card processing
I have an access database that is used to store records of requests for for items. Example, ID, WhoRequested, CustomerName, Date, address, phone, WhatRequested, amt. I take this data and export it to an excel spreadsheet and send to another area for processing.
I then get the sheet back with the orderdate, ordernumber, and shippingnumber. I need to update the original table with this new information. it is not bad when there is only a few, but Ihave had 100 or so lately, and updating each record manually is time consuming.
I'm having trouble appending an excel sheet into my access table.
I have 8 columns in my excel sheet:
1- full_name (short text)
2- alias (short text)
3- email_address (short text)
4- user_ID (short text)
5- Location (which is annotated using numbers 1,2,3,4 etc.) (number)
6- active (yes/no)
7- TRI (yes/no)
8- Another user ID (Short text form)
I am trying to append this data to an existing table in excel with the EXACT same column names.
I can append each column from my excel sheet separately and they will appear on the access table, but once I try to append all of the columns at the same time, so the records are not placed in different rows, I get a message saying "Script out of Range"
I cannot seem to figure it out since they can be uploaded individually.
Just to clarify, the excel columns are formatted to the access columns.
Im familiar with Ms Access, but have never used VBA or Scripts. I have 37 Excel files with the same data and would like to import into one file. Data will be received on a monthly basis into the same directory and I would like to automatically upload the data into the same file in Access.
View 1 Replies View RelatedI have another request for you. I did all the analysis. My final query looks as follows
Jan_N-1, Feb_N-1, Mar_N-1, Jan_N-2, Feb_N-2, Mar_N-2
0.98......... 0.90..... 0.88....... 0.95....... 0.88...... 0.70
but I need Access to create a excel table like output. which looks as like
.......Jan. Feb. Mar
N-1 0.98 0.90 0.88
N-2 0.95 0.88 0.70
Hi there,
I'm pretty new to Access so I hope this question is not to simple/stupid ;)
Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:
- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.
- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)
Any idea how I should procede so that I can import my data into Access?
Thanks!
Steve
Hello,
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.
thanks
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
Hopefully someone can help me this (and hopefully the solution isn't staring me in the face)
I am trying to export data from a cell in a worksheet to a field in an Access database that I am building. The database has two tables, tblCompanies and tblQuotes. It works through a macro accessed by a command button, which should export the total of a quote into the field Quote_Price in the tblQuotes table, which the users will access through a form. The problem is that if I export the data from Excel, I get an error message saying, "You cannot add or change a record because a related record is required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:Documents and SettingsDJEdwardsMy DocumentsTest Foldermarketing.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 55 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote_Price") = Range("I" & r).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I can import the cell contents from Access with no problems, but I would like the user to be able to do it from Excel to simplify things.
I'd be grateful for any help!
Cheers
Dave, England