Very Large Database, Not Working

Jun 25, 2005

Hi
Many thanxs for replies, Like the idea of compacting.
We are traveling down the path of setting up a delete query.
This will hopefully delete the records but not the structure.
We are networked and a computer Warp2, writes data at Midnight to the sever, and then we use access on Windows NT to view the data.

We can then compact to maintain the database and not allow it to grow to 1.6 GB again.

Any ideas on the delete query thingy would be greatly appreciated.
:cool:
Many thanks for reading this post from a new starter.

View Replies

Working With Large Data Sets.

Mar 31, 2008

Hi guys,

I have a query that returns a large data set (~100k rows/month).

I am currently inefficiently exporting week-by-week to Excel to carry out some manipulation (mainly a set of "if" statements to calculate differences between records that have the same site id).

Is there a way to write a function in Access to carry out the calculations? I'm not too familiar with Access VBA apart from coding functions for forms etc.

View 12 Replies View Related

Large Database Query

Jun 1, 2006

Hi,

I am currently using a large Access 2002 database in order to generate various reports.

My two main tables are despatches and returns from which they hold around 1,200,000 records and 100,000 records respectively.

The problem I have is that the reports use various expressions within various queries to generate a single result (percentages per channel etc.)
This is obviously very time consuming and it may take up to around 10 to 15 minutes to get a result from a chain of around 5 queries.

Can anyone suggest alternative methods to generate similar results in quicker time?
(Please note that the tables can not be downsized and records can not be archived)

Many thanks
:)

View 6 Replies View Related

Large Database Problems

Dec 10, 2006

Hi All, please forgive me if I am in the wrong forum.

I have a pretty good size (~6400 table and 700 Mb) single user application. It runs on XP home with office 2000.

It is a financial application (stocks and mutual funds). Each symbol has it's own table. The app ran fine when we were monitoring about 1800 symbols. Now that we are up to 3200, I am getting some odd messages from Access. It can't find tables and also says that tables are opened exclusively by other processes.

Although I do not use explicit transactions, it is like I need a "commit" or refresh of the user table catalog.

Each table has 312 rows (52 weeks / year * 6 years of historical data). So, for half the tables (3200) I do 312 Inserts ("Insert into tablename (col1, col2, etc) values (val1, val2, etc)".

Is there a transaction log that needs clearing? Is there a setting in Access that I need to change?

Any thoughts on my situation?

Thanks in advance

View 8 Replies View Related

Organising Large Database

Jul 12, 2005

Hi
I have a large database with many tables, forms, queries repost etc. These are stored named and displayed alphabetically, is there a way to place them in folders within access so it is easier to organise and locate as i am developing. Or do i just need to rename them all with a section title as the first part of the name?

Thanks

View 1 Replies View Related

Copying Down A Column In A Large Database.

Jan 5, 2005

Is there a simple way of copying the same data down a column in Access similar to Excel with its Fill Down Column. I have tryed copying down a column and it only goes one cell at a time or if I copy 5 cells with the same data, I can copy it to 5 empty cells.

View 2 Replies View Related

Changing Field Length In Large Database.

Feb 6, 2006

I am trying to change a field lenght of a large database (access 97) It is at 50 right now and want to change it to 25. When I try to change it and save the table I get a error when the bar is about 3/4 the way accross the bottom.

Microsoft can't change the data type - there isn't enough disk space or memory.

then

not enough space on temporary disk

This is error 3183. In the help it says that the TEMP DOS enviroment variable location doesn't have enough space. (summerising). Now I have a 100+gb drive that isn't close to being full. I also changed the MaxLocksPerFile registry dword to 8,000,000 as my table has around over 3 million records. I have tried this on a xp and 2000 machine same results. Next I am going to try it on a 98 machine. I could do it by breaking up the table into 2 differnt ones and do each one individually but there is an autonumber field used as a reference number (I know I know). So that would screw up the numbering scheem (Yes I know there are ways arund that also). this database is about 400MB

Couple of questions
1. Is there a way around this? I have not found a solution on line yet.
2. Will lowering the field length make the database smaller (the data that is in there now is less than 25 charecters and the field is set to 50.) I have gotten mixed info on this. there are a few fields I would like to reduce.

Thanks
sam

View 2 Replies View Related

Problem With Large Database & Pivot Tables

Apr 12, 2007

Hi there,
I've developed over the past 3-4 years a database holding data relating to workload figures for my place of work. It has grown to a large size (eg. one table holds 1-2 million records). I've been trying to run a particular pivot table based on the sql query below:

SELECT Tests.Test, Year([DAUTH])+(Month([DAUTH])<4) AS FYear
FROM Sets INNER JOIN Tests ON Sets.SET = Tests.[Set Code]
WHERE (((Sets.DAUTH) Between #4/1/2003# And #3/31/2007#));

When I try producing a pivot table using the above query it takes forever - has it running for over 8 hours last night - then access closed down with a runtime error. Does MS access have a problem with large databases? Is there any way I can improve the efficiency / speed of access with a download add-on? Or, will I have to resort to using something like MySQL on a LINUX OS which has fewer demands on system resources when using large databases.

Any help would be much appreciated. Thanks for reading.

View 1 Replies View Related

Trouble! Replacing Characters In A Large Access Database

Jun 1, 2007

Howdy,

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

View 4 Replies View Related

Modules & VBA :: Optimizing Tree View Of Large Database

Oct 24, 2014

I have a form with a Treeview in. I have it populated from a self-referencing database using the following code.

Code:
Private Sub Form_Load()
Const strTableQueryName = "SELECT * FROM tblHierarchy ORDER BY tblHierarchy.Function_Parent;"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)

[Code] ....

The database this is referencing is about 30000 lines and it takes ~4 minutes to populate this way. I know Treeview isn't really supposed to be used in this way however it's what is required.

Now I have come up with the theory that I will populate each node with children as its clicked to be expanded.

Uploaded my current treeview example ....

View 4 Replies View Related

Large Field Size = Larger Overall Database Size?

Aug 29, 2005

Will keeping your field size shorter result in a smaller MDB file?

Or does Access only use as much space as there is real data in its fields.

Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.

Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...?
Thanks............
..dc

View 3 Replies View Related

Database Not Working?

Jun 23, 2005

Our database is 1.6gb in size.
It has stopped working, is there a way to delete old records?
We are told it will crash the computer, because access needs the same space as the data its self.
Any ideas?
Thank you for your time in reading this post

View 2 Replies View Related

Seeing Who Is Working On Database

Nov 3, 2004

Hi,

I have a database in company network which all users can reach. Can I see who is working on the database as administrator?

It is possible in excell, how it happens in access?

Thanks
Ilkem

View 1 Replies View Related

Working Access Database

Apr 26, 2005

Hi.

I am having a hell of a time trying to make my order and invoice processing database to work properly with useful functions. Is there any online resource from which I can download a working example of such a database in order to use as a case study and examine how everything relates and how complex order forms are created? :confused:

Thanks.

View 2 Replies View Related

Database Not Working For Some Users

Aug 7, 2007

I am having a problem that I have never encountered before and thought I start here. Using Access 2003
I have a database that resides on a main computer and my users connect via the network to this database. On my forms I have command buttons that run simple macros to copy and paste data from one field into another. The macro contains a GOTOCONTROL, to copy the contents and then moves to the next field. Then I have SEND KEYS command and wait is set to yes, then PASTE. When they press the command button it seems like the SENDKEYS is not waiting then the macro halts becuse it is saying the COPY command is not available. This happens to all users except for one. That other user is also connected to the database via the network. I checked the OPTION for all the computers that are connected and they all match to the one computer that works. The one computer that does work is the one that was used to write the database but the databse is now on another computer that is always on. The funny thing is that if I were to change any option under Forms/Reports, the macro's will work for a while then stop. I did do a compact and repair several times but this has no effect. Sorry for rambling but I am just frustrated because this databse worked fine at another account of mine and I just can't figure out why this is happening.
Thanks in advance for your reply.

I now haw 2 laptops that work and 2 desktops and 1 laptop that don't.

VAD

View 13 Replies View Related

Working With Linked Database

Feb 6, 2007

I am slowly but surely learning more everyday about Access. I inherited a database system from a gentleman who set up a lot of great stuff but did not document anything! I am working with a linked database in Access. I am trying to add information to a look up wizard. I am unable to de-select the "Enforce Referential Integrity", so I deleted the one to many relationship in hopes of being able to add the information. However, I am still not able. A message pops up stating it is a linked table and some properties can not be modified. My look up wizard Row Source can not be modified.

Any tips on how to get around this?

View 1 Replies View Related

Troubleshooting Split Database That Was Working Before

Oct 1, 2004

I created a database which had to be split to allow multiple people to access it with three 'front-ends' so that different people had different levels of access. Due to security problems this database had to be moved to another location on the network. Now I have used the linked table manager to update all of the frontends so they can locate the data. Previously users would just copy their own version, appropriate to them, and use this to update; now though only one person can open the database at a time with the backend database being opened and hence locked (if that makes sense).

Any suggestions as to why this has happened, or solutions would be appreciated.

mjc65

View 2 Replies View Related

General :: Possible For Database To Get Working With Voice?

Dec 27, 2013

I have an asked and i want to give the answer , when we create a database application it is possible the database working with voice ? for example when i tell the databse login form username is Admin and password is 12345 the database was opened ? if it is possible, how to do it because before i see it but i don't no how to do it .

For example like this code

Dim message, sapi
message=InputBox("What do you want me to say?","Speak to Me")
Set sapi=CreateObject("sapi.spvoice")
sapi.Speak message

View 1 Replies View Related

Request: Database Sample With Working ListView

Jun 27, 2005

Hello all,

I finally got TreeView to work, by downloading the example database from this (http://www.access-programmers.co.uk/forums/showthread.php?t=80805&highlight=treeview) thread. I haven't had as much luck with ListView, though. Does anyone have a database they could post that has a working listview? I'd greatly appreciate it!!

Thanks,
Eric

View 1 Replies View Related

Multiple DataBase Working On Local Network

Jan 14, 2008

Hi! I work at a company where a same DB is being opened by more than one user, is there any way to allow many users be working on a same DB?

Actually I cannot even open it if its already opened. It appears another file with a "lock", which seems to be protecting the DB. :confused:

View 5 Replies View Related

Access Database Quits Working Correctly

Mar 26, 2008

I am working on developing a database that develops requests to send to another department. There are a lot of forms, macros, reports and queries. A user inputs information into a form, and then selects a command button that is attached to a macro that builds the request. Midway through the build, another form comes up to complete missing information. I was going along just fine - at the testing stage for this part of the process - when the database stopped working like it should. Some of the issues I am seeing:

1) If you open a new query and try to close it without saving it, it just keeps asking you if you want to save over and over even though you select No.

2) If you open an existing query and make a change, it closes it and saves the changes without any prompting at all.

3) Macros and forms do not work right. I have a macro that deletes the records from one table. The form for that table now shows #Deleted in all the fields instead of blanks.

If you compact/repair the database is good for one run through but then it doesn't work correctly again. I have had this issue in the past but have no clue how I break my databases! I have a general knowledge of Access, I am not really a VBA coder if I can avoid it at all costs. There must be something in the relationship between all my tables, queries, forms, etc but I have no clue even where to start to troubleshoot. HELP!!

View 3 Replies View Related

Forms :: Multiple Users On LAN Working On Same Database

Jan 21, 2015

How multiple users can work on the same database across LAN, without using share point.

View 6 Replies View Related

General :: Access 2010 - Database Only Working In PC?

Aug 27, 2014

I am Using Access 2010 Version. I have created a Database which consists of Linked Tables (from remote Server), Queries and a Form.

I am using Form to search the required Data. Every thing is working in My PC. But, when the Database file is trying to open from my colleague PC. It is not working. I mean, The Form is opening but when I click the Button nothing is happening. My Colleague is using 2007 Version of MS Access.

View 5 Replies View Related

Database Not Working After Adding Attachment Field?

Aug 5, 2013

I created a database a while ago which has been working just fine, until I wanted to make one addition: an attachement field to one of its tables.

If I do so, some queries don't work anymore and as a result of that, some (sub)forms do not work. The only error I get is: The search key was not found in any record.

When I add an other kind of field (like a text field), the problem does not occur..

When I remove the attachment field again, everything works fine again...

View 8 Replies View Related

General :: Dial Button Not Working In Standalone Database

May 23, 2014

I have an Access 2007 database which has a form on which is a button to dial the currently selected phone number. All works fine. My intention is to send out partial databases to people not having access installed and they will use the access runtime module.

When the same database is run using the runtime it does not dial and does not give an error message. It just stays the same as though I didn't press the dial button.

View 10 Replies View Related

General :: Access 2010 Split Database Hyperlinks Not Working / When Copy Of Front End Moved

Jul 14, 2014

I have built an Access 2010 split database on my computer and it functions as built. My next step was to copy it to the server at work and test it. I discovered the tables had to be re-linked and so did that. As I have read in the Access World Forums in order to function as a multi-user database a copy of the front end must be placed on the individual workstation with the back end residing on the server, however the hyperlinks in the back end will not function when the copy on the workstation is run. I simply get an unable to open "filename" error pop-up. I can run the server copy and everything functions as built. I know that the hyperlinks require all the files to be in the same folder (relative vs. absolute), but if the back end contains all the hyperlinks why does the location of the front end seem to affect the operation of the back end?

View 3 Replies View Related