I 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.
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?
This really isn't a large table by Access standards, as it has only about 1 million records, but nevertheless it's taking a long time to run the update query. Here's the query:
UPDATE [Db] INNER JOIN [Small Table] ON [Large Table].text1=[Small Table].[text1] SET [Large Table].[text2] = [Small Table].[text2];
[Large Table] has about 1 million records and [Small Table] has 10 records.
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.
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.
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?
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.
I 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.
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.
I have a data where I want to create a query fulfilling the below conditions. Suppose I have two table: Table 1 and Table 2 If a value ex.98 (Table1) matches with the value with 98(Table 2),it should pick up my second higher value 103. suppose 103 is the next high value of 98 . Please see the data value.
misprepaid.asmvalue from Table2 Required Result Con 989898 then 103 if value of table1=98 then 103 from table 2 (next large number) 103103103 then 149 if value of table1=103 then 149 from table 2 (next large number) 149149149 then 175 if value of table1=149 then 175 from table 2 (next large number) 175175175 then 198 if value of table1=175 then 198 from table 2 (next large number) 198198198 then 199
I'm trying to import an Excel file into access as a table so I can use the data in other tables I am building. When I try to import to file, I get an error message telling me that there are over 255 columns and not all my data will be imported. The file is a report I pull from another system at work and it is very large, is there a way to get past the 255 column limit?
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:
I am currently in the midst of conversation over at utteraccess.com about a large database project I am working on.
I will post the most recent updates here, but if you want to download the database for your own review, you'll have to go over to the other forum. By the way, this is NOT an advertisement. I am a real person with a real, significant, and immediate need of as much assistance as possible for this project.
Before I go on, a few things about me. Though I am in the legal profession, my technological background is very strong, including a high level of proficiency with MS Office apps with the exception of Access. I know my way around the program (the result of its homogeneity with the rest of Office), but have not made a real database in the past. I also do not know any programming languages.
I have recently indulged in an Access crash course of sorts, including some book and online study on things like planning, design, and normalization, but I am still having difficulty wrapping my head around making it work.
That said, the link to the other discussion is: [W W W DOT]utteraccess.[ADD DOT COM]/forums/showflat.php?Cat=&Number=1590364&page=0&view=collapsed&sb=5&o=&fpart=all&vc=1
Feel free to gloss over it to see how things have progressed. Below is a paste of my most recent substantive post. Any help anyone can give me is of great value to me and I really, really appreciate it. _____________________
Hi Everyone,
Attached is the most updated version of my database project for your review. I have also attached a sample of the output we would like to have for each product. This sample is not based on actual data, but it clearly shows what we are trying to achieve via a form of some sort. More on this in a minute.
The following changes have been made to the DB:
-Changed tblTrustProspectusVersion to include the appropriate data, based on our business model. -Added descriptions to all non-PK fields. -Created relationships to illustrate how things fit together. Note that these are NOT the actual relationships, but are for illustrative purposes to help everyone here (including myself) further understand how things fit together.
Our Business:
I am part of my company's legal department. Our team handles a number of different things primarily associated with Securities and Exchange Commission (SEC). filings. We have two major product lines. Everything that happens with one in this DB happens with the other as well.
Each product within a product line has certain features and other necessary information we need to see when doing our filings (see the attached sample output). These features and necessities include, but are not limited to, various statuses, various numbers associated with the SEC, various important dates, etc.
In addition, each product has a certain "fund lineup" associated with it. These funds are made up of two components: the name of the fund (aka "portfolio") and the name of the subadvisor to that fund.
Futher, each of these funds is associated with a certain Trust. The SEC requires us to send prospectuses to clients based on these Trusts, which, as I mentioned, are comprised of the said funds.
Basically, we need to be able to select a product from a drop-down list and have all of the aforementioned information populate instantly.
Before I close, one question with respect to my "tblProductFeatures". Like I mentioned, each product has a certain set of features associated with it. Each feature has a certain fee associated with it. These features come in four basic categories: Living Benefits + fees, Death Benefits + fees; Maintenance fees (just short list of the possible fees); and 12b-1 fees (another short list of fees).
Since the features can be so easily broken down, should I add them to their own tables?
Please consider this as you give your advice on how to acheive my desired goal.
I hope this makes sense.
And again, thank you all so very much for your help thus far.
I have 250 separate worksheets with a lot of data to put into Access. Problem is the data is 120,000 rows in each worksheet and a lot of duplicate date eg..DATE, NAME, TIME,are some of the column headings and there are multiple rows with same DATE or NAME. That is just how I received the data. I would like to transfer all records into Access as quickly and efficiently as possible.
I have a data file I am importing into MS Access 2010. One of the fields is a large text field. When i import that field into Access the text is getting cut off. How do I get the full text field to import without cutting off?
I have created a rather large data entry form for one of our departments which will be used to run a mail merge document. The merge is run off of a query of the form, rather than the tables themselves...I think that is what I've read is the right thing to do?
Anyways, most of it is working great so far but I've run into an issue where a few of the form fields are combo boxes. In access and in the query the data looks correct, but when you look at it in word (in edit recipient list) and after the merge, it will have a file path name instead.
So a combo box that has "Medical Plans and Visual Plans" in that field in the query, comes over saying "c:Users ameAppData
Ex: New Hires are provided information about Medical Plans and Visual Plans. Ex: New Hires are provided information about MeC:UsersbrooksAppData
It always shows the first two letters before putting the filepath name in there.
I have tried doing it multiple ways...a lookup to another table, lookup right in the field itself....get the same results.
Have an MDE file in our equipment, it's a language data base one of the languages isn't fully translated so I did a query to pull out those records that equaled the English field which brought the 1800 lines down to 218 to go over and edit.
BUT
What I am worried about, well our Software Engineers are nervous about is my trying to udate those records via an update query and then putting the MDE back into the machine's computer.
Is there something that I should be careful about while doing this? I know that I shouldn't try to repair and compact as MDE just goes bye bye when that is done, changes into an MDB file.
I have recently had to add a new field to a FIRM table in our contact database, Firm.PhoneNumber (please don't ask why it wasn't there before!). The primary key in this table is Firm.FirmID.
I have a list available to me in .csv format that contains all the phone numbers I need, it has two 'columns', labelled FirmID and PhoneNumber.
The process is surely a simple one, I just need to update/copy/insert the corresponding phone numbers from the csv file into the Firm.PhoneNumber field. I won't bore you with what I've attempted thus far but could someone please provide me with the quickest possbile method for achieving this task?
I have created a database that tracks the locations of all the faxes we receive in a day. We have a dedicated employee that enters them into the database as well as who they are assigned to and other pertinent information. At the end of the day, I monitor the network folders where the faxes are saved to change the status of the fax in the database to done, still needs processing, or awaiting approval. Is there any way that I could set up some VBA that would automatically update the status column for the record based on the location of the file? The file name is recorded in the database so then if it sees that that file is in the end folder it could automatically update the status to finished.
The response I got on another forum is as follows:
-The impression I'm getting is that you've got a database of information over here. -And it lists the file name, but not location. -And then you have a physical folder structure with the fax files in it. -And there are folders for done, awaiting approval, or processing. -So what you're looking for is something to read those folders and update the status column based on which folder it is in? -So at the start of the day we have:
New -Fax1.tiff -Fax2.tiff -Fax3.tiff -Fax4.tiff Finished - In progress -
And at the end of the day the folders look like this: New -Fax1.tiff Finished -Fax3.tiff -Fax4.tiff In progress -Fax2.tiff
And you want your code to browse those folders, and update the status of each filename based on it's folder?If the filenames are unique, you can just use VBA to open the folder, create an array of the files and then set the status. Create two separate functions, one for in progress, one for completed and call them one after the other. There'd be no need to check the current status, but you could store the wrong status if a file is duplicated, or a filename repeated.
I'm currently working on a database to track and inventory documents in my organization similar to a library catalog, but with a digital archive as well. I'll need the ability to "attach" documents to records in the main inventory (though using links instead of storing it in the db) and have the documents live in a centrally accessible location on our shared server.
I will need to have this function work through a few different tables and in a few different iterations, but I'm trying to work on the simplest part first, which is attaching a thumbnail/preview of a given document to the record for that document.
I've got some VBA working well which opens the file picker and copies the file to a directory relative to the DB. It also creates a folder with each employee's ID number if it doesn't exist already (which it gets from the Windows log in) and adds "Copied_" to the front of the file.
Now I'm trying to get the VBA code to interact with the record. Presumably I need to tell the form button I'm using to launch the VBA code to pass the primary key of the record being displayed to VBA. Then, at the end of the VBA code I need to take that value and find the record again from VBA so I can update the MediaThumbnailLink in the original record.
Does this order of operations make sense? If so, how should I go about passing these values back and forth and writing the new link value?
Here's the VBA I'm using so far...
Code: Option Compare Database Public Sub GoCopyThumbnail() Dim fDialog As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) Dim varFile As Variant Dim LUser As String
I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please download the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I wish to use VBA in access file to automatically perform the steps that I have outlined.
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference). Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
I have a button in the form of the current access file. What I want to do is when the user click on the button, triggered the vba to backup current access file. But since the file is currently open, is this possible? If yes, may I know how to do it with vba?
I have tried two methods but failed: 1) Use copytofile method, but I get permission denied due to file open; 2) Use dbengine.compactdatabase method, but it also has problem if the file is open.
I'm experimenting with MS Access, and I've successfully split an MDB someone created into two files. I can access the database from 2 computers at once using the front end file connecting to a file server which hosts the _be.mdb file.
Could the _be.mdb file theoretically be on a NAS without any computer attached, or is there some runtime or full version of Access that needs to run on the machine with the back end file?
