Hello
I have a database which i am constantly updating and improving. I have a few friends who use the database in a current form. If i made an update say to a form or to some of the coding to make the DB more efficent, how can i update the dBs my friends are using without damaging or changing the existing data, or migrating it to the new design.
I.e going from version 1 to version 1.1
Any advise would be great?
I have searched the forum but have failed to find the answer to my problem. I have a front end ms access 2000 solution that I distribute to user PC's with an MDE back end data base on a server.
I now need to release a new version that includes changes to forms, queries and tables.
However there is data in the original mde data base I need to retain. Is there an easy method to migrate that data to the new data base. I have changed some relationships but this should not affect data integrity - most change is related to adding new fieldsto existing tables or new tables (no previous data).
If I create a new empty mde will I be able to import old data into it from previous mde?:confused:
I currently have a database that is missing various pieces of information, we are getting a temp in to complete this. I don't want the temp adding data directly into Access so I have exported the table that needs updating into Excel.
How can I then import the new data without altering current data.
I know this is no problem for new records, but if I have a current record with a few fields of information missing how can I just import information into the blank fields. This can't be a manual process as there are hundreds of records.
Any comments would be much appreciated!
Chris
I'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and how to code it and is it similar to my find data code below.
Here is my save function code:
Function SaveData() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Skips Delivered")
[Code] .....
I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).
View 1 Replies View RelatedI have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.
Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?
If I run an update query I will have to manually add each field to the query.
The update query will not add 'new' records. If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).
If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset
Then it uses the rs.Findfirst method
It checks if rs.Nomatch is not true (so the records exists!)
Then it starts updating the record with
rs.edit
rs!value1 = myvalue1,
rs!value2 = myvalue 2
rs.Update
There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I have checked for typos.
Hello Tech gurus,
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
Hi,
I have been assigned to "improve" an Access database that has been created by a user. However, i do not know where to start. One of the things I have to do is write a stored procedure so that when the user runs the queries, the user is prompted to enter the criteria (for instance, a data range) to narrow down the number of records retrieved from the SQL backend. I have been told to improve the database. However, I do not have any experience in this and don't know how to start. The database has been created by a user so there's obviously lots of room for improvement. For instance, there are a million queries and I don't think that many are really needed. The goal is to minimize the size of the database. Can someone please guide me as to how I should go about starting to improve this database?
Thanks.
is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people...
i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible
what do u think?
*j
We are a SME with a 4 year old access database which is in need of updating with new tabs and various other new spec. We are struggling to find an access programmer who is right for this project.
Does anyone know someone who could have a look at this project??
We are in the Greater Manchester area at present but will be rolling out on a regional and then national scale with this in the next 12-18 months
I've been racking my brain the last couple of days trying to figure out how to solve this problem, and I believe I've reached the end of my rope. I have a feeling that this isn't very difficult to a more savvy Access person, but I am at a loss.
Here's the situation. I have received a fairly large DB containing CCTV data for sanitary sewers. There are primarily two tables I'm dealing with, one lists a number (auto-number) for each pipe that was televised. Simple enough. The other uses this legacy number to show all deformities or service leads within a particular length of pipe. For example, for run X, there may be 7 rows in the table with X as the ID, one for each service lead along that length of pipe (I hope this is making sense :()
Ultimately, we need to tie this database into our GIS theme. To do this, I will need to add to the PipeID number from our GIS theme to the access table. What I've done so far is to create a new table in the DB with the number for each pipe televised, and I've manually added the corresponding PipeID number from the GIS in the second column. What I'm hoping to do is add a new column to my occurrence table so that for each occurrence X, I can add the GIS PipeID number. Perhaps this would make more sense:
Run # PipeID
1 S143
1 S143
1 S143
1 S143
2 S231
2 S231
2 S231
2 S231
3 S543
3 S543
3 S543
I've gone ahead and created the relationship between the newly created table and the existing table based on that auto-number field, and I've made the new PipeID column a combo box. This shows all of my PipeID numbers, which is a good thing. I'm hoping there's a way for it to automatically recognize the auto-number field and populate the PipeID field accordingly.
Have you ever known what you want to say, but not quite understood how to say it? That's kind of how I feel about this question, and I do apologize if I've made no sense. But if I have, and anyone has any suggestions for me, they would be greatly appreciated.
Cheers,
Azimuth
We have a shared personnel access database for our department. Another department is wanting to use the same database. Is there a way to go about deleting all the info and making a template to give the other dept to use?
View 5 Replies View RelatedI was tasked by my CFO yesterday to add a new element to my existing database. I will try to keep what I am trying to do simple.
Basically we do finance for multiple projects. each project has employers.
My database records these employers names, SSN, dates registered, program they are in, and a few other elements as well.
What I have been tasked to do is create a new table with the project managers in that table. this way in a query or report i can pull "sally sue's" projects or whoever I needed to. There are many PM's who have more than one project.
Here is what I have for relationships:
On the Project Managers table I have a PK for ID and then the PM Name as a second field. the ID field is linked to the Programs table which has a PM ID and Program name
The Program table is also related to the employer table which has all of the employers and their info. the relation between the two tables is the Program Name.
So when I run a query or report my goal is to be able to pick any project manager and get the associated count of employers under all of that projects managers programs. I have gotten errors when running queries. Perhaps I have the relationships set up wrong?
I have an existing contact information database which consists of a number of tables, the main table has a primary key which is currently set to NUMBER.
There is a relationship between it and 2 other tables via that key (ContactID).
I would now like to change the primary key (ContactID) to AUTONUMBER, so that any new records added, have a number assigned automatically. I also need any newly created primary key number to be used in the child tables created as a result.
Is this possible ? (without renumbering my existing tables/records)
many thanks
Using access 2010; anyway to create an er diagram from an existing database? I have inherited a fairly large database and need to make changes to some field properties and need to find out where these fields are located. I know I can go into tools and run the documenter but doesn't give me an easy format to look at.
View 5 Replies View RelatedI have table1 in my (Access 2010) database that has exising records. I have another table2 that after I run a query, it first deletes the data in table 2, then imports new records into that table. I run the import into table 2 on a semi regular basis but have yet to copy those records into table 1.
With that said, using either a query or VB, how do I copy all the (new) records from table 2 into table 1 without altering any records that already exist in table1?
BTW, Table 1 has a main form with a subform within it
I have to design 2 databases base on some data that was given by my instructor but I feel like I'm missing something on both specially the 2nd database. I have attached my work so far as well as the instructions and the existing data..
View 2 Replies View RelatedHow can I overwrite the existing Access database with another Access database without the need for the end user to get involved in the process.
I want the code to copy a new mdb file over the database I am using (!) and then automatically start the new mdb file...
I am trying to add a new email field to my existing database of 900 employees. I have imported an excel worksheet of email addresses into a new table that has lastname, firstname, employee#, email. The primary key on the new table is email. The primary key on the employee table is employee#, and there is a new field titled email.
The email field in both tables are text fields. In running an append query, it either adds 900 to 27000 additional fields of email only or it says cannot complete because of key code violations. On the join properties I have clicked on #3 all records from source to only equal fields of destination.
Database: Access 2007
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
What is the best way to do this?
Adding a field in Access 2007.
I am adding a exisiting field which is already available on a Global Table and would like to add it on a local table within the same database. Also bearing in mind the db contains main objects - Tables/Queries/Sharepoint lists/forms/reports
Whats the process in doing this? Once added how does the data get populated?
Basically it's trying to take all the files in a given directory and add them into a existing database.
I have a database called Permit. the database has a field called "SEAL" and they will be creating an additional field "SCART" to hold the contents of the file that exist in the directory. Each image file, in directory, is named the same as a unique record in Permit.
example:
Permit-SC might have unique record 532442
An image file would be name 532442.
If the image file matches a record in Permit the image should be added to permit in a field called "SCART". At this point I'm not certain if I want a link or the actual image included in the record. since there's a possibility of including the permit file on the website it might be cleaner to have it included.
I'm altering a database to have certain fields be recorded monthly.For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".
I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).
I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.
the company doesn't track when the transactions occurred; I'm unable to group them along those lines.I'm new to Access & don't want to make a mistake.
I was asked to create a table within an existing database using these SQL commands;
CREATE TABLE Vehicle (reg_no TEXT(10)
CONSTRAINT VehicleKey PRIMARY KEY,
type TEXT(10),
purchase_date DATETIME,
last_service_date DATETIME, mileage_at_last_service INTEGER);
I was told to save the query and check the new table had the required records in it.
I had huge problems when I tried to save the query and kept getting error messages like: "query must have at least one destination field" and "syntax error in field destination". This was strange since I was sure I typed the query exactly as it had been written on the worksheet.
Having not changed the SQL command at all it eventually worked when I skipped the 'save query" stage and just went to the "run query" stage. I still don't know why it actually worked in the end and why I kept getting error messages.By running the query am I supposed to just click on "RUN" or can I check the information first by looking at datasheet view?
I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".
A visual of my tables:
tblLabels (Main table)
- LabelID
- BaseProduct (old field with text data)
- LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)
tblBaseProduct (new table)
- BaseProductID (PK and FK to tblLabels)
- BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)
I tried to design a query using design view of the query design and this is what I have:
Code:
UPDATE tblLabels, tblBaseProduct
SET tblLabels.LabelBaseProduct = [baseProductID]
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));
When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.