I have a database it contains data more than 4 million line items but while i try to run macro or any query the database reaches the maximum limit of 2 gb , then the particular query not functioning and then i need to compact and repair. After compact and repair also the same issue is continuing.
View RepliesCan anyone tell me if it is possible to update an Access Database by reading a .txt file in an Access program and then using SQL in a DAO mode to actually create new rows on the Access Data Base?
Specifically, I wish to read a .txt file in an Access program, extract specific bits of data from the .txt file, and then create new rows on an Access Table.
I have been able to do everything that I needed to do except that I just can't figure out how to write the code to create to create the rows on the Table. I know that I could do it using ADODb except this entire application is written in DAO.
Can anybody give me some guidance?
Many Thanks
I have a query in acess which brings data from different tables. Each month I print this query and I send to a person that will modify the numbers (manually - yes with a pen) and give it back to me. Then I update it manually (in the computer) and print it back to verify.Once I have the final version done, I have to put the updated data into access again. And this is the part I would like to automate (since its not possible to automate the first step because the other person is a retarded on computers and want to use a pen).
So how can I import the data I updated in excel to access? As far as I saw I can only import data into a table, not update using a query. Plus while we are making this manual updates, the data in access is changing. So copy and paste is not an option since the order of the records in the query will be heavily different.
I need to create a database use access that the access can update the database based on a schedule, like daily.For example, in the table there are several columns, and each column stands for a meter point, and in real world, each meter will have a reading every 5 mins. Now the database consists of all the readings till yesterday 11:55pm, by right, tomorrow for example at 8am, the database will update the readings till tonight 11:55pm, today's readings will be placed right after below yesterday 11:55pm row in the same table. The data source will be generated in CSV file every day 8am and put into a specific folder. What I need to do is to update them into this database.
View 8 Replies View RelatedHello All,
I am using Microsoft Access 2000 and wanted to know how to import data from another Access database that was done in Visual Basic. I know how to import flat-file database such as Excel but how do I import data that has a
One-To-Many Relationship? The end user will need to transistion into the new database so I would need to update it once awhile. Thank you very much for your time and assistance.
I have a workbook which has links to access to import data. I have to have the information in Excel for a couple reasons:
1.) Many of our customers don't have Access.
2.) The customers use a "discount calculator" to apply their own discounts.
They also use it to then apply their own margin mark ups to be able to then use to quote to their customers. So a static report/pdf for them to look at doesn't work.
I've begun to create links from a number of queries in Access, which work fine. My problem is when I go to update the data. I'm not getting the following error coming up:
The database definitely hasn't moved. And I've run the queries in q, and there aren't any problems there. I've looked this error up and I'm seeing that it could mean that I've got some corruption going on.
Before I hit my main q, I should also note: I'm running all of this on a Citrix network. I should also note that it's a consulting gig. Once I'm done, I won't be available to fix major issues like this.
Is it possible to update the data for a chart graph in a word document from Access using VBA?
View 4 Replies View RelatedI have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
View 1 Replies View RelatedMy membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
Browsing the all of the existing records is fine.
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
Thanks in advance
Wayne
I have 2 tables:
"LookupDates" which has
ContractYear
YearStart
YearEnd
and "Staff" which has
Name
StartDate
The tables are not linked in anyway as the LookupDates table is used for referencing.
What I want to be able to do is add another column to the staff table that shows what contract year they started in.
I have tried DLookUp but I have read that you cant use update queries with DLookup in the way that I want to...
DLookup("[ContractYear]", "LookupDates", "[StartDate] Between [YearStart] And [YearEnd]
If I create a form and cycle through the records I can get the DLookup function to work via VBA but this isn't very practical
Can anyone help with this problem?
P.S. I didn't put this post in the queries forum as I think it falls under the General section.
Thanks for any help
I have an Access file with one data table and several linked (read only) tables from a mainframe database. I run an Append Query to import certain data from the linked tables into my data table. Periodically the data in certain fields in the linked tables will change. I want to refresh my local data to reflect this. So I tried to write an Update Query, but it keeps telling me that "The Operations is not an updatable query". I assume this is because the linked tables are read only even though I am trying to update from the linked tables to my local data table. So I am not trying to modify the read only tables.
Assuming I am correct, what is the best way to accomplish my need to update certain fields in my data table with the information on the linked tables?
Thanks for your help.
Jim
I have a query to calculate YTD donations. I group on the customer ID where the date is within the current year and sum on the dollar amount.
Since this is not an updatable query, how can I update another table with this calculated dollar amount?
Thanks for any suggestions!
Hi all,
I've tried searching the forum, but no luck.
I have a Field say Field1, with data like this:
AB, AC
AB
AC
AB, AD
AA, AB
Where the field contains "AB", I want to replace the data with say "AB - Alpha Beta". But I do not want to change any of the other data. So the outcome should look like:
AB - Alpha Beta, AC
AB - Alpha Beta
AC
AB - Alpha Beta, AD
AA, AB - Alpha Beta
I'd then go on to change AC to say "AC - Alpha Charlie".
All help appreciated,
Jempie
I have a table that I need to update existing data and append new data. All of the new data is in the same file. Is there a way I can update and append at the same time?
View 1 Replies View RelatedForgive me if this has been posted else where - but i am new to forums so ill try to pick it up quickly ok.
I gave an access 97 'menu' form called projects. When i click on the projects button, it opens up my projects form, and everything is fine and works ok.
On this projects form, I have a drop down showing all my contacts, so that i may select one to associate that person with that project on my projects form.
Here is the 64k question... if the contact is not in my drop down, i can click a button called edit, and it opens my contacts form where I can add a new contact. When I close the contacts form down, the projects form is now visible again. However my drop down menu does not show the new contact added. I even added a refresh button and still no joy.
Any suggestions - would be very much appreciated.
Jim.
How do we update a data from a form into a field?
I have a form call member. In the member form I had created a membership Id combine with several code which can shown in the form. But this is only shown and I need this combine memberID to be update into another field.
Anyone have better idea which I just need to add it into controlsource ?
i want to update data fields (time fields) in a table based on a number input by user (so it will search for record in table using the number and then update the relevant fields)....How can i do this am quiete confused there PLEASE DO HELP
View 1 Replies View RelatedI've got one table, let's call it "data", that has a set of data with a Measure Name, and the corresponding monthly data in columns. For example:
Measure Name Jan 2014 Feb 2014 Mar 2014
Calls Answered 10 15 25
I have a function that takes the data from the above format, and puts it in the following format:
Measure Name Period Score
Calls Answered Jan 2014 10
Calls Answered Feb 2014 15
Calls Answered Mar 2014 25
Here's the function I'm using (I've left out a lot of the fields I'm really using just to make the example easier.
Function TransposeSLA()
Dim rsMySet As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'Open the original matrix-style dataset
Set rsMySet = CurrentDb.OpenRecordset("SLA_Data")
[Code] .....
The function above puts the data in a new table called "Transposed" in the format I need. The user accomplishes this by clicking a "button" on a form. The button also deletes the existing table / records from Transposed. I now need to change it so that only NEW data from the "data" table is transposed by the function INTO the table "Transposed". The reason for this is that my client decided they want to do some additional data entry in the transposed table. As it is right now, they can't, because the table is deleted each time they click the button on the form to add new data.
Hello,
Firstly, I am very much self-taught on access, so there are several gaping holes in my knowledge, and my database structure is probably not wonderful! I have been learning as I go along, so in the initial stages I have built in several problems which I am now discovering!
My db is designed to track suggestions - i have a table where the idea, progress etc is stored [tblIdeasBank]. This contains an 'originator' (person's name) which is linked to a second table, where the names are stored [tblPeople].
Originally I set up tblPeople with the Primary Key as FullName (e.g. Joe Bloggs) (guaranteed to be unique with the small number of people that would be involved), and a second field called InitialLastName (e.g. J Bloggs). It is this second field that is stored in the tblIdeasBank. Data verification came from the user having to select a name from a drop down box, so there is no actual 'relationship' between the tables. (Didn't realise how useful they were at the time!)
The db has been so successful :confused: that it is now going to be rolled out across the company, which gives me two problems. To populate all the names, I need to import them from Excel, where I will have FirstName and LastName. I have now realised that it would be more sensible to just store these, and create InitialLastName where needed. Also, I may well have duplicate names, so I need to create a unique ID, and a proper relationship. (Yes, I know I should have done that in the first place).
So, I have:
tblPeople:
.FullName (Primary Key)
.InitialLastName
tblIdeasBank:
.Originator (stored as InitialLastName)
And I would like:
tblPeople:
.PersonID (Primary Key)
.FirstName
.LastName
tblIdeasBank:
.IdeaID (Primary Key)
.Originator (stored as PersonID)
With a One-Many relationship from PersonID to Originator
Any ideas how I would go about doing this and changing the Originator for each idea from InitialLastName to the relevant PersonID number, without corrupting the data?
I have searched through Google and various groups, but cannot find a useful answer, so any pointers gratefully appreciated!
Thanks,
Nick
I want to say update set field1 = field2
But field1's data type is Number and Field2 is Text. (Field1 is fed by a lookup table). Am I doomed to go through the 700 or so by hand, gurus, please?
I have two tables
table1 his a master list of companies that I have already delt with
table2 is a list of companies that I have delt with as well as new companies and I allow for multiple instances of company data to be in table2
table2 my have several records refering to "ABCD Inc" but table1 will only have it once
If new companies show up in table2 how can I get a mass copy of all the companies that are in table2 but not in table1 into table1?
thanks,
honor
I would like to run a query and then be able to edit that information. I want the data I change to be put into the table the info was gathered from. Right now when I edit the query data it only saves it there, not on the other table. I tried to do the copy to table but the original table was going to be erased and then written over. What should I do? thanks
View 3 Replies View RelatedHello All,
I currently have a macro that imports data from a spreadsheet and then a query that adds the data into the main table.
But when I want to import new data it deletes the old data out of the table and inserts new data. How can I adjust the query so that it "updates" the new data into the table instead of deleting and then adding?
Another problem is empty records, is there a way of importing data where field 1 has data?
Any help would be great.
Thanks.
I have a problem....
How can I copy data from one column in a table to another colunm, with WHERE and UPDATE?
And how can I find the data, which is the closest, for example using LIKE '%'
UPDATE tag_info,tag_info_kilde SET tag_info.TAG_DESCRIPTION = [tag_info_kilde].[TAG_DESCRIPTION_KILDE]
WHERE [tag_info].[TAG_NAME]=[tag_info_kilde].[TAG_NAME_KILDE];