How to get a large .txt file into Access. I know it has too many columns so I selected about 30 columns that I don't need to be 'skipped'. However it is just giving me the error that my file has more than 255 columns - with the 30 selected for skip - it should have about 230 columns.
View RepliesI am writing a vba procedure to updating some records in another Access database.
rsAccess.Open "SELECT * FROM AI_Table",conAccess, adOpenForwardOnly, adLockPessimistic
rsAccess!OCRExist = "Exist"
rsAccess.Update
it has about 3 millions of records in that AI_Table. In the procedure, I perform some calculation and put the result into a TEXT(50) field in the AI_TABLE. As it was updating the records, I could see the size of the Access database file (the one contained AI_Table) grew very quickly, almost 1 MB/sec. I am pretty sure I am not adding that much data. If I stop the procedure and packed the database, it shrunk a lot.
I am just wondering if there is anything wrong with the way I am locking or updating the records.
Thanks,
pggsB
I hope someone can help with this.
I have a large file, more than 2 million records. I am accessing it from a form using parameters supplied from a combo box. There are 79 different parameters in the combo box that each normally access their proportionate number of records, about 40,000 each. This works well. With the table properly indexed, I get the 40,000 records selected within two or three seconds.
However, sometimes I want to access all records. In this case the operation takes forever. So, if I use the criteria in the query:
[Forms]![CriteriaPassingForm]![Criteria] the records are returned very quickly.
But, if I use the criteria:
Like "*" & [Forms]![CriteriaPassingForm]![Criteria] the return of records takes minutes instead of seconds.
Within the combo box I have one criteria which is 'null'. This does not match anything in the query, so according to the 'Like "*"' all records should be returned, which they are. But why does it take so much longer?
I'm thinking it has something to do with the operation of the index on the field I am querying.
Any ideas?
I have a large .dat file which is run through an Access macro to produce reports. After a recent system change at work the format of the .dat has changed and now includes an additional bit of data which disrupts the macro.
I tried changing the extension of the file from dat to mdb to see if I could remove the additional column in access. I also tried changing it to a csv file as well but the file has a few hundred thousand lines and the csv file cuts most of it out.
Are there any other ways I can open this file in Access to remove this additional column of data?
I am importing a delimited .txt file that has a number field. A value for a record coming in is 36,767 and Access is not accepting it. If I redefine the field as long integer or as double, I can manually update the record, but as soon as the file containing the record is imported, the field reverts back to integer.
How do I format the field with VBA so that Access will accept the value and not revert to integer?
Morning all,
I'm having a problem with mdb file size. I'm importing a large amount of data from a number of tab delimited text files via a simple transfertext function. The process goes: empty the tables in the database, then import the data into the tables.
All this works fine, but the file size rockets to over 1.5Gb. When I then compact and repair, it goes down to 420Mb. I'm not deleting and recreating the tables, and at no point is there 1.5Gb worth of real data, so what's causing this?
N.B. I realise I can call compact and repair following the import, but this is going to take too long as they are user-initiated imports.
I am attempting to create a metrics analysis table from another table. What I would like to do is copy the structure (only) from table 1 into a new table. Change all the fields in the new table to text (except for an ID field which would be an autonumber). Then run a seperate group by query against each column, counting the values in each group (i.e. first query would have two fields The grouped column and the column count.
Once I have these values I would like to concatenate them (with the count in parens) and then push these values back into the new table under the appropriate column.
My code does this. I basically loop through a recordset that runs to each column/field groups and counts and then Edits the new table with the concatenated data.
My first table is 170 fields and 38K records. The issue is that it's too much for Access to handle and it blows up (on field 123) Telling me the File is too large. The file does explode to 1G. Then I can shrink it back down to 67mb by running a repair and compact... and then run the the data for the rest of the fields in that table. When I compact again I get about 80Mb.
So now I have two tables, both with an ID field... so I try to link them together (via a make table query) and meld them into one table... but it keep running into that "File Too Large" issue.
How can I have two tables in a database file with a combined size of 80Mb, but when linked together are too large for the database file? Does it have something to do with having all text fields?
I looked up the limits to MS Access and the field count doesn't appear to be an issue since it's nowhere near 255... So what's the problem here?
The file was converted from excel. It is in Datasheet view. I select the first column and clip on the Ascending choice under the Home Tab. It works but leaves a large gab of blank rows. I go to the Database Tools tab and check Compact and Repair Database. The file returns to the original unorganized list.
View 1 Replies View RelatedI am trying to find a way to extract an email from a large text file that is an output from our email system. I would like to be able to extract the email address using a query or collection of queries. I have been able to extract all of the text that contains the @ symbol. From their I created a query expression:
Mid([field1],InStrRev([field1]," ")) that captures some but not everything I need.
I have two datasets that I am using. They start off with similar information: sitename, siteprovince, sitecoordinates. They also have 5 more fields that have the same type of information. After that there are about 10 more fields with no overlap.
In the original dBase program they came from they were treated as one dataset.
The current structure I am using is Company, CompanyContact, Transaction, SiteDetails (the dataset I am asking about).
Is it better design to breakup the SiteDetails into SiteTypeA and SiteTypeB? I have everything working in one table, but I thought it might be more effecient to have two.
I have a split database, the front end is showing to be 20.5 megs in size but there are only 4 forms and about 6 querys. Can someone tell me how to find what is making it so large?
I did a compact and repair but it did not reduce the size??
Thanks..
Fen
I'd like to create a table with 240 fields. I know that the max is 255, however, I'm getting a message "property value too large" after I've created 114. Any ideas? All the number fields are byte size. Thanks!
View 10 Replies View RelatedI am currently operating queries in my A2K DB that are 35458 characters (~11 full sheets of A4 paper with 12pt).
It takes roughly ½ second for the form to load due to the heavy query that is also calling functions (calc'd fields) from within, but it works fine.
Are there any problems in this?
I think I heard somewhere that queries have a max length of about 2000 characters or so ... :o
I guess I have too many columns in my database and I'm getting the error message "Property value is too large" when trying to open the database table. When I was using Access 2000, I was still able to open the database but using Access 2003, the database will not open. Is there a way around this so I can open the table to fix it?
View 14 Replies View RelatedHi
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.
Hi all, apologies for the long post but this is a long nightmare!
Using A2k on Win2k...Due to roll out a DB today, just made my final tweaks and decided to compact/backup as have done many times before. It's not a split DB but it is secure so I copied the mdw onto my desktop and accessed it using a special compact shortcut which points to the desktop copy DB not the one on the network. When I compacted though Access went about it's business for a while and then threw up the dreaded "Network connection may be Lost" error message (roughly translates as "your database is nicely corrupt now") which I have had in the past when I tried to compact on the network. I've never had this error before compacting locally but anyway I tried again and same error message. I tried opening some forms and stuff and sure enought the DB was corrupt. No probs I thought, I can just go back to the original and start the process again. I used the normal shorcut to open the original DB just to check everything was ok and the same error message appeared with same problems. Minor panic ensued and I thought i'd got the shortcuts mixed up or something... I hadn't, Both DB's were affected.
Next option. Create a new DB and import all the objects. I did that and re-set all the permissions and the "Network Connection Lost" message dissapeared but some really strange things were happening. Forms opened but the buttons on them would do nothing. Then the forms Close button didn't work and the database wouldn't close.
I forced my way out of the DB and re-started my machine. Couldn't even log in to windows. Now at another machine and can log in to windows but DB is still having same problems. My instict tells me that the problem is something to do with the workgroup file becuase how else could compacting a copy corrupt the original? The only common link is the mdw.
Should I re-create the mdw and then try opening/importing the DB objects again? Or is there any way I can un-secure the DB and then re-secure it later?
Any thoughts would be much appreciated.
Thanks, Tom
I have a database that keeps track of training hours for each employee. The Training Length is formated as Short Time. I just figured out that short time can only go up to 23:59:59. Some of my trainings will be over that. Does anyone know a way to get around other than splitting up my hours and Mins in the table?
View 1 Replies View RelatedHi,
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
:)
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
I have a large search form, in which I am building a query in the code.
I went to compile the code, and was suprised to receive the error "Code too long for procedure". I had no idea there was a limit, but now I know.
So, I believe I will have to break this code up into chunks, stored on at least one if not more code modules, and call the functions.
To do this, I would have to pass the values entered into the form, to the code module. Build that portion of the query Where Clause, and then return that string value back to the code on the Search form.
Has anyone attempted this before? If so, could you give me a small example of how to pass a value from a form, to a code module, process it on the code module, then pass a resulting value from the code module back to the code in the form.
Thank you for your time!
T.J.
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
I am using MS Access(2000) as a front end to a MS SQL2000 DB. I set up a table link to one of the tables in the SQL server. The table I am linking to has 242 Fields in it. The table shows 21888 rows of data.
In Access, when I set the record source in a Form to this linked table and go to run (Form) view, I get a "Record to Large" error.
In the SQL table - there is one varchar field that is 17 in length. There are about 5 char fields and the rest are numeric or date.
My questions are:
What is causing this error?
What would be a good work around or other possible solutions?
Thanks.
This number is too large [220020220020] for a field in my table. I currently have it set to Long Integer. What's the proper setting for a number this large?
Thanks
Hello. My database has around 6000 products, and as time has gone by, the database has got bigger and bigger, more and more text - its now a rather large 16MB. Now, everytime i make a small change to it and upload to the server, it takes me about 8 min uploadeing it. Not that bad, but if i have to change 3-4 times a day? Also, it wipes out the website during the upload, which is not that great. Is there someway to compact the access database somehow? Thanks.
View 5 Replies View RelatedI have a table with 140 fields (I know, this is too many). I have a date field that intermittently will not allow data to be entered. There is a pattern to the data it will not accept, but it seems to only occur in certain records and what it will or will not allow seems different in each case. The error I get when I try to save a record is: The search key was not found in any record. I've isolated the error to the level of the table. Have tried compact/repair, removing the index on the field, deleting and recreating the field. Nothing works. Help! :confused:
View 5 Replies View RelatedHi,
To avoid the mind-numbing tedium of have to use make-table queries loads of times, is there a quick (probably VBA-related) way to split a large Access table, of about 350000 records, down into 93 smaller tables, based on a key code field that identifies each group of records e.g. GBW102, GBE999, etc?
Any help much appreciated.
thanks,
Alex