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.
I have just made a change to one of the forms by adding a button (by copying the only other button on the form) to cancel any changes and close the form. However, as soon as I added it I started getting the error message in the title. Please attachment LA Err1 for the full message. I also changed the caption on the other button on the form from "Close Form" to "Save && Close Form" this button is now giving the same error.
I have Compacted and repaired the DB on several occasions to no avail. I have deleted the procedures from the module and recreated them using the properties window - still get the error. I have deleted the buttons from the form and recreated the both via the object wizard and without it. Nothing I have tried has made any effect.
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??
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!
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?
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.
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.
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?
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?
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)
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?
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?
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?
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?
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.
Hi guys. i guess i am half way through. just like final step left still trying to figure it out how to create a VB procedure. anyways this is my theory and this is how much i have achieved. my God i have searched i guess every forum trying to get an answer it should'nt be that hard. I have a database. what i am trying to do is that i have a button for view reports which prompts the user to another form with 3 buttons 1) Issue(based on my query) 2) Issue Resolved(based on my query) 3) Issue not resolved(based on my query) i am using this onclick command Private Sub Issue_Resolved_Click() DoCmd.OutputTo acReport, "Issue resolved", acFormatXLS End Sub same thing for my other options. works fine but when i open the report in Excel the whole formatting is bad so with my research i found this code very valuable. a standard module code which is as folllow.
' // Generically formats a worksheet Private Sub GenericXLFormat(ByRef xlApp As Object, ByVal strDescription As String) On Error Resume Next With xlApp .Rows("1:1").Font.Bold = True .Cells.EntireColumn.AutoFit With xlApp.ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "" .CenterHeader = strDescription .RightHeader = "" .LeftFooter = "&""Arial""&8WF COF/SIP Database " & APP_VERSION .CenterFooter = "" .RightFooter = "&""Arial,Bold""&8CONFIDENTIAL" .LeftMargin = Excel.Application.InchesToPoints(0.5) .RightMargin = Excel.Application.InchesToPoints(0.5) .TopMargin = Excel.Application.InchesToPoints(0.8) .BottomMargin = Excel.Application.InchesToPoints(0.75) .HeaderMargin = Excel.Application.InchesToPoints(0.5) .FooterMargin = Excel.Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed End With End With End Sub i tried to debug it and compile it no errors. now this is what i want and i will be done. what i want is when a user clicks on my report button which is (Issue) i want this Xlformat module to be run with my onclick button. i can't find or cannot think of any procedure since my skills in VB is not that very good. all i wanted to do is create some kind of procedure with this Xlformat module so then a user will be able to save the report as an excel file and then when he opens it the report in excel should be in perfect format. columns, width should be alligned automatically. i have been searching days and night with different answers to create a template or do this and that but nothing works plz help guys. i really appreciate it. Thanks
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 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:
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?
I have developed a Tax Assessment solution using Access 2003, I already have in excess of 150k records, I have 7 tables; 55 queries; 30 forms.
My main table "Transact" has over 175 fields (yep I know its large, but I need all these fields and dont want to create 100's of tables). Since last week I cannot save any definition changes to my table, I keep receiving error 3309. I have searched all discussion groups but cannot find any decent solution. 1. I have not changed the index's, of no duplicates 2. I do not have more than 255 columns in table 3. I have increased the maxlocks substantially 4. run a compact & repair succesfully
What else can I try, as mentioend I am reluctant to split Transact into smaller tables. Regards Gto
I've been given a file with 165,000 records that was saved as an .xls file. I'm trying to import it into Access so I can view all of the records. When I try to import the file I get an error that "C:AllFiles.xls" is not in the expected format. I've tried the external data wizard and transferspreadsheet method.
I can open the file in excel and see the first 65,000 records. If I copy them and paste them into a new .xls file, I can import them just fine.
I have over 5,000 customers in a simple database. For each customer, I'm only displaying the account number, name & class of trade. Each customer represents a record and there is also a sub-form below displaying each customers product template - so each customer has different items displayed and this changes as I switch from record to record or customer to customer. (sorry if this is too much info)
I want to take a class of trade (Bakery for example) and I want to say, a Bakery should purchase these items (I'll have a list of items for the class of trade Bakery.) I want to then take that list and compare it to what is already in all Bakery customer product templates. Lastly, I want to display all of the missing items. The items I think they should buy - but only the items that are not already in the customer's product template.
Obviously, using the final report generated, our Sales Staff would be more effective while hitting the beat (so to say.)
I have a little experience with Access and VBA - but I can't get my head around this one. Any help would be much appreciated...:cool:
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.
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.
