Split Table, Now Slow?
Mar 9, 2006
Hi all
I was hoping to get a little advice as to my problem. I have a database with many, many reports, forms and queries. I wanted to split the database to secure the tables and important data. So I use the database splitter wizard and ever thing went just fine. So now my database1 looks into database1_be for all tables.
My problem is: my program now runs very slow. The slowest seems to be when I am adding a new record. I can live with the slowness of retrieving an existing record. I do not have a server nor can I afford one at this time, so currently I am running a peer-to-peer network with the database1_be located on one of my computers second hard drive.
Is there something I can do to help speed things up?
Thanks Enviva
View Replies
ADVERTISEMENT
Apr 29, 2005
Hi all
I have an Access 2003 db that works extremely well as a stand-alone application but I am keen to “split” it so that many users can operate it together.
Accordingly I did the split and placed the tables on our server, and tried running the front end locally.
Much to my horror, it takes about a minute to open, and many seconds to do anything after a button press etc.
I have been testing it on my wireless home network and the same thing happens, except now I can monitor network activity from the “network working” light.
What I see baffles me and would welcome comments, in particular, what I can do to speed it up by a factor of 100!
1.It takes forms about 50 seconds to open; however, this is due to about 40 seconds of network activity BEFORE the form’s load event fires!
2.In DESIGN mode, any minor change to a control for example, results in about 40 seconds network activity.
3.All queries upon which forms are based take well under a second to run over the network.
4.Making an mde file has no effect.
I thought the idea of splitting was to keep the forms/queries/code etc, local to a machine so I don’t understand what all the network activity is about, particularly in design mode. Also, given the actual queries are so fast, just what is Access doing??
Any comments please?
Skeletal
View 3 Replies
View Related
Nov 14, 2012
I have a database i have been working on (access 2007). In the past I have been using a list box to select records. Now I would like to use the datasheet view or split form view. Everything seemed fine until I moved to linked tables.
My problem is, with the same query, the listbox is lightning fast at changing the recordsource.
When I change the recordsource on a datasheet view or split form, it takes... ages and shows "Calculating.." On larger recordsets it freezes access.
View 3 Replies
View Related
Dec 14, 2007
Hi All,
I'm having trouble with access. Basically it has been very slow to load
(over 30 seconds when not opening a database) and when opening a database it just crashes.
Has anyone any idea what could cause this as it worked fine before ?
also would a reinstall help ??
-Elfman
View 3 Replies
View Related
Feb 17, 2005
Hi,
I have developed a small database with 3 or 4 forms which works ok on most PC's. But when I put it on a certain PC, it almost works properly except for one form which is very slow accessing the table!
Eg. It could take 2 minutes to use a combobox.
All PC's have Access 2000.
Would there be a reason for this?
Is this an access problem or a PC problem? Are there special settings that I'm unaware of?
Thanks in advance
View 1 Replies
View Related
Feb 7, 2006
Ok my database tracks escalations through my team, I have a main table that stores the unique ID's from the other tables I use in my Combo boxes, this part works really well, no problems.
My issue is with a tableI have claled "TBL_EscJournal", this table has the following fields:
JournalID (Unique Ref, generated by autonumber)
EscID (the escalation Id that this journal is relevant to)
Journalcreator (captures name of person who as entered the journal)
JournalNotes (memo field where you enter your update)
JournalDate (Date/Time the journal was entered)
So typically when viewing the main detailed form for a particular escalation I have a subform that shows all the journal entries relevant ot that escalation.
This table is huge, about 70% the total size of my database, partly because of the number of journal entries and partly because it is a memo field and a lot of data is required sometimes.
Up until now th edatabase has been located on a local server and has been fine for local users (2-3 of us) however there is a requirement for another office to use this database.
I am now experiencing massive performance issues, whereby the data is tkaig a long time to refresh on the other sites.
I have migrated the DB over to a SQL back end but still finding performance issues, which further testing has shown that the TBL_EscJournal is the cause.
So a coupel of things really, is there another way I can layout this table to improve performance or should I be uerying the data from this tabel in another way, would it be better for me to split TBL_EscJournal in two, the first part keeping the date/time and person who entered and the second part keeping the notes. Possibly increasing the query speed by carrying out the query on the first part of the table and not on the notes (memo) part ?
I hope this makes sense, if not feel free to PM me,
Appreciate any help or assitance you could offer
MattP
View 4 Replies
View Related
Mar 21, 2006
I have a database with a table that contains 360,000 rows. I built a form with four boxes where a user can specify values to limit the result set. And instead of having a new window open with the results, I built a subform and placed it on the main form to display the results.
Here is how it flows:
Main Form -> user enters search criteria
Search Criteria -> feed as criteria in query
Query Results -> display on subform
Subform -> shows on main form
The query is setup to take the values from the main form and either use it if it's not null, or return all values if the field is null.
The problem I am having is that on opening the main form, Access is taking the four null values from the main form search fields, feeding them to the query, which is then feeding the subform. So 10 minutes later when the main form finally opens, I have 360K records displaying in my subform.
What I would like is to be able to open the main form instantly, specify my search criteria, then run the query, then have the query results populate the subform.
What do I need to do?
View 3 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Dec 10, 2007
I have one table - 40 fields, 40000 records. Iwant split this one table up to 4 different tables (keep data). Is there an easy way, pls?
View 6 Replies
View Related
May 25, 2015
I have a table which contain around 10 Lacs records, now i want to split the same in to 10 or more table with 1 lac records.
View 1 Replies
View Related
Feb 6, 2013
I'm gonna get right into it; here are two tables I have in my database.
tblAccession
-Accession (PK)
-State
-Test Type
tblSamples
-Accession (FK)
-Sample
-Additional
-Cassette
-Tissue Type
-Comments
-Trim initials
-Trim date
-Section initials
-Section Date
-Control (FK from a third table)
For tblSamples, the fields:Accession, Sample, Additional, Cassette is the same for each accession and is entered when an Accession is logged in at the beginning of the process. The rest of the information (Tissue Type, Comments, etc.) is entered when various people completes those steps throughout the testing (fluctuating people and days). Should I have have the fluctuating information (tissue type, comments, etc.) as a separate table? With a one-to-one relationship? Would there be an advantage/disadvantage to splitting tblSamples into two different tables?
View 2 Replies
View Related
Oct 20, 2005
I am using a Table to store Parameter Values for a Split Multiuser DB. Question I have...Can I have this Table on the Front End while all the others are on th back end? The use for the table is to store parameter values for multiple queries without the user having to reenter the parameters.
I've tested this and it seems to work...but would like to know if there may be complications.
FYI. I am a Novice VBA user.
Thanks,
Felix
View 1 Replies
View Related
Feb 5, 2008
Hi there!
I'm currently working on a malformed Access database, not normalized at all.
It only consists of one table and is designed like this:
(table compartment)
#ID#Name#Member1#Member2#Member3#Member4#...#Membe r20#
So all the members are realized by single columns.
I created a new database, consisting of the main table, the "people" table and a relation table, connecting the people with the compartment (n:m relation).
It works great but I have to get the old data into the new tables. The old main table consists of more then 100 columns, and most of them are now hived off by using relations.
Now I'm looking for a smart solution to convert the table.
Do you have any idea? I tried to use a Query, but I couldn't merge two or more columns and put them in a single one on a new table.
Thank you in advance!
View 13 Replies
View Related
Aug 18, 2014
So I split my database into a front and back end, now i wanted to add a table to the database. I created the table in the backend but I am not sure how to make it appear in the front end. I am using access 2010. I tried the link table manager but the table I created doesn't appear there.
View 4 Replies
View Related
Apr 8, 2015
I'm working on recreating my church's records in access and was originally wanting to take people from the Visitors table and automatically move the to the Members Table (and same with Deceased & Moved), but after some searching found that's not really do able. So now my question is, can I have a master table that has everybody (visitors/members/deceased/moved) and then split that into different tables while keeping the main table (that way I can go into the main table and reassign the person)?
View 7 Replies
View Related
Aug 18, 2014
So i split my database into a front and back end, now i wanted to add a table to the database. I created the table in the backend but im not sure how to make it appear in the front end. Im using access 2010. I tried the link table manager but the table i created dosent appear there.
View 2 Replies
View Related
Nov 21, 2013
I have an Access 2007 file that is 'split' so the tables are centrally stored on the server.
How do I update one the linked tables stored in the back-end file with a table from another Access file?
If I import the 'new' table it is not 'linked' - it is embedded with the Access file I am working on.
What is the correct way of doing this without corrupting the data?
View 3 Replies
View Related
Oct 27, 2014
My boss made a satisfaction database that tracks satisfaction in 2 ways, so we have:
Table
ID
SatisfactionAType
SatisfactionAScore
SatisfactionBType
SatisfactionBScore
I'd like to make a query that would split these into two lines, one for A, one for B and then export it to excel.
View 4 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)
Value1(Double)
Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 3 Replies
View Related
Mar 30, 2007
Hi All,
I've developed a database that was working fine until I split it to use in a network enviroment. The problem is a make-table query that now make it's table in the front end instead of the back-end. I need to run a make-table query because the query uses a custom function which then causes errors when the query is accessed by a web form that I use.
The only way I thought of getting round it was to create a permanent table in the back-end which is cleared every time and an append query used to populate it.
Anybody any other ideas?
Cheers
View 3 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)Value1(Double)Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 11 Replies
View Related
Dec 3, 2013
I've created several Split forms that have the data input fields in the form with the relevant query datasheet shown below. As you tab through the form fields, the various cells in the datasheet are highlighted and move across the data row (as one would expect!). I want to put a particular field / Column at the start of the datasheet so that it's always available for view, but it seeme that what ever I do the column ends up back at the very far end of the data row!
I've sussed out the "Freeze Fields" facility which will keep the first column visable whichever cell is highlighted across the data row.
The column I want as the first column currently sits at the far end of the data row. So far I have dragged the row to the first column position; I've arranged the Query driving the form so that the column is at the front of the row, both in design view and in datasheet view, but to no avail. Everything I do to put the column at the start of the row in the datasheet shown below the form ends up with it back at the far end of the row the next time I open the form.
View 1 Replies
View Related
Sep 17, 2014
My db is split into front-end and back-end. In the f-e's Relationships schematic, I can see the relationships as they were defined at the time when the db was split, complete with the one-to-many symbology.
I can add a new table to the b-e and set its relationship as one-to-many, enforcing referential integrity and cascading as I wish - and the schematic (in the b-e) reflects that.
In the f-e, I can then use the <Get External Data - Access Database> function to link to the new table, and I can add the new table to the relationships diagram in the f-e. I can also drag and drop to link primary and foreign keys (within the f-e), but cannot select one-to-many. I'm OK with that, as I understand that the relationship is within the b-e, and this is just a diagrammatic representation.
But I can see the one-to-many relationships between the tables which existed when the db was split, and I would like to be able to see the new table's relationships in a consistent fashion. Updating the linked tables via the Linked Table Manager does not do the trick.
Surely I don't have to split the database again in order to achieve a consistent diagram - do I ?
View 3 Replies
View Related
Aug 20, 2013
I have elected to have tables inserted into my forms. For example on the School Information form, I have the Employees table inserted at the bottom so that it will show the employees that are assigned to that school when it's pulled up.
Before I split my database into a BE/FE situation, I could expand the items in the tables to other related items, but now that option is gone.
Is there any way to get that back with a split database?
View 7 Replies
View Related
Mar 24, 2014
I'm having a problem with a database that i had split. While creating teh database, i had it stored on my personal drive at work, but wanted to move it to the shared drive so the data could be stored there. this is to deal with cross site network connectivity issues that we encountered with another database here at work.
When splitting the database, i split it on my personal drive and then dragged the back end to the common drive. I realized my error, but when attempting to correct it, i somehow have two of the same file, and no back end?
I receive the error on the attached document when attempting to access any of the tables or information in either of the files, the "back end" that i placed on teh drive, and the "front end" that i have on my personal drive.
I also tried to relink the tables through the linked table manager, but receive the same error when attempting that.
Is there a way to recover from this? I had backed up my file by making a copy prior to splitting, but somewhere in this process, this became linked to these and is having the same issue. Is there a way to recover the old "unsplit" version? I cannot copy over all of teh tables, etc. because i get the same error as earlier.
I can attach a copy of my database for reference...
View 5 Replies
View Related
Sep 14, 2012
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
View 2 Replies
View Related
