If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.65 gi
I split the db today into a fe/be because of the size.
It did accomplish my goal of reducing the size of the be on the network, however, the front-end is still the same size. Since the tables are now linked instead of actual tables, I thought that the fe would be a smaller size? Since the database is approaching the Access limit of 2 gig, is there a way to reduce the size of the fe? Also, after splitting the db, it is unreasonably slow. I know that it was faster before because now the links go to the network, as opposed to the fact that I was running it from my desktop. Can I speed it up somehow? Please advise. Thank you Donna |
#2
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.65 gi
Did you try compact your database?
Are there any images in the MDB? -- Duane Hookom Microsoft Access MVP "AccessNut" wrote: I split the db today into a fe/be because of the size. It did accomplish my goal of reducing the size of the be on the network, however, the front-end is still the same size. Since the tables are now linked instead of actual tables, I thought that the fe would be a smaller size? Since the database is approaching the Access limit of 2 gig, is there a way to reduce the size of the fe? Also, after splitting the db, it is unreasonably slow. I know that it was faster before because now the links go to the network, as opposed to the fact that I was running it from my desktop. Can I speed it up somehow? Please advise. Thank you Donna |
#3
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.65 gi
Thank you Duane, compact & repair did the trick and reduced the size from 1.6
gig to 39,208 KB. So simple, I don't know why I didn't think of that. In answer to your question, yes I do have images in a couple of the forms and reports. It wasn't a problem before, is it now a problem? The forms are still very slow to arrive when I click on the button to open them. Is there a quick fix for this? I do have many buttons, subforms, subreports, combo boxes, etc. - and sometimes many subreports/subforms in one report/form. Thank you so much for your quick response and fix for the size problem. Have a good day. Oh BTW - when I received the notifcation of a response - the two links provided, one to read the thread and the other to continue receiving threads neither brought me here. They both came up with blank screens. White empty forms. I don't know if telling you about it is the proper procedure. But since I have your attention and don't know whom else to contact, I thought I would mention it. "AccessNut" wrote: I split the db today into a fe/be because of the size. It did accomplish my goal of reducing the size of the be on the network, however, the front-end is still the same size. Since the tables are now linked instead of actual tables, I thought that the fe would be a smaller size? Since the database is approaching the Access limit of 2 gig, is there a way to reduce the size of the fe? Also, after splitting the db, it is unreasonably slow. I know that it was faster before because now the links go to the network, as opposed to the fact that I was running it from my desktop. Can I speed it up somehow? Please advise. Thank you Donna |
#4
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.6
Your file size will increase dramatically if you store images in your tables.
A few small images on forms and/or reports generally works ok. If I haven't logged in, I will get linked to a blank page if I click the second link. The first link to read the message generally works. Regarding performance, there are lots of resources on the web. Tony Toews has one of the better pages at http://www.granite.ab.ca/access/performancefaq.htm. -- Duane Hookom Microsoft Access MVP "AccessNut" wrote: Thank you Duane, compact & repair did the trick and reduced the size from 1.6 gig to 39,208 KB. So simple, I don't know why I didn't think of that. In answer to your question, yes I do have images in a couple of the forms and reports. It wasn't a problem before, is it now a problem? The forms are still very slow to arrive when I click on the button to open them. Is there a quick fix for this? I do have many buttons, subforms, subreports, combo boxes, etc. - and sometimes many subreports/subforms in one report/form. Thank you so much for your quick response and fix for the size problem. Have a good day. Oh BTW - when I received the notifcation of a response - the two links provided, one to read the thread and the other to continue receiving threads neither brought me here. They both came up with blank screens. White empty forms. I don't know if telling you about it is the proper procedure. But since I have your attention and don't know whom else to contact, I thought I would mention it. "AccessNut" wrote: I split the db today into a fe/be because of the size. It did accomplish my goal of reducing the size of the be on the network, however, the front-end is still the same size. Since the tables are now linked instead of actual tables, I thought that the fe would be a smaller size? Since the database is approaching the Access limit of 2 gig, is there a way to reduce the size of the fe? Also, after splitting the db, it is unreasonably slow. I know that it was faster before because now the links go to the network, as opposed to the fact that I was running it from my desktop. Can I speed it up somehow? Please advise. Thank you Donna |
#5
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.65 gi
AccessNut wrote:
In answer to your question, yes I do have images in a couple of the forms and reports. It wasn't a problem before, is it now a problem? Each image embedded on a form or report can take between 100 Kb and 5 or 10 Mb of space within the Access MDB. The reason is that Access 2003 and earlier convert the graphic to BMP format. BMP format was very useful back in the Windows 3.1 and Windows 95 days as it was much faster to read the BMP file straight into video memory than it was to generate an image on the screen from a jpg file. These days with fast CPUs that's no longer a problem. Therefore to keep the size of the FE down we suggest one of two methods. 1) Put the image on one form and/or report and reference that form/report as a subform/sub report. 2) Load the image from a file on the server as required. Using the Toolbox drop an image control on the reports detail section. Note that you will have to follow the dialog and actually insert an image. But then go to the Picture property on the Format tab in the property sheet and delete the actual file. You will probably want to set the Size Mode from clip to zoom. And rename the control. Then in the report detail section in the On Print event you want to add the following lines of code If logic if file available to view Then Me.PhotoImage.Picture = PhotoFilePath Else Me.PhotoImage.Picture = "" End If You don't want to do this logic in the Format event as Microsoft has told us, via Stephen Lebans, that using that event can lead to memory leak problems. Or use BMP images. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#6
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.65 gi
"Tony Toews [MVP]" wrote in
: If logic if file available to view Then Me.PhotoImage.Picture = PhotoFilePath Else Me.PhotoImage.Picture = "" End If Just a quibble, but it is more efficient to use the constant vbNullString instead of "". It doesn't matter in a context like this, but in a loop, it can make a huge difference, as using "" causes memory to be allocated each time, while using the constant does not, since the memory is already allocated. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#7
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.6
Thank you again, Duane,
The link from my email "notify" worked this time. Who knows maybe I wasn't signed-on and thought I was...thus, the earlier blank page. Regarding the performance issue, I am still in the dark. I explored the link that you provided, but admit that I do not know exactly how to do it. This text from the article: "Delete the SQL from the RecordSource and RowSource properties of the form, ...subforms, comboboxes and listboxes...." ??? If I delete the RecordSource property of the form, how will the form (etc.) know where to get the data? Plus, I have many forms, subforms, and comboboxes - do I have to go through all my forms, reports, combo boxes, etc and remove the record source??? ???? In the text in the article: "Now in the Form_Load event load the appropriate SQL as follows ..." (code listed after my question. ??? What exactly does this code do? Does it pull in the forms (into memory) thus, saving the time that it takes to "load" these forms, reports, etc. into memory? I have coded into each of the buttons to close the form that the button is "in" and open the form indicated in the code, thus, only one form is ever "open" at one time. Do I have to put this code into every form? Do I have to personalize it with my form names or something? Sorry for all of the questions, it is just that whenever I am provided code as a solution I want to understand how it works before I go through all of the time and work to remove all of the record source property only to find out that I might have to put them all back in. I just don't understand how the form will know where to get the data without the recordsource that I now have.???? Thanks for all of your help, I really appreciate it. Below is the code suggested in the article. Private Sub Form_Load() Me.RecordSource = "qryLargeTable" Me.txtSomeField.RowSource = _ "SELECT SomeField " & _ "FROM qryLargeTable " & _ "GROUP BY SomeField " & _ "ORDER BY SomeField;" End Sub It also pays to clear the record sources in the Unload event as sometime these get saved with the form in Access 2000. Private Sub Form_Unload(Cancel As Integer) Me.RecordSource = "" Me.cboFindRecord.RowSource = "" End Sub "Duane Hookom" wrote: Your file size will increase dramatically if you store images in your tables. A few small images on forms and/or reports generally works ok. If I haven't logged in, I will get linked to a blank page if I click the second link. The first link to read the message generally works. Regarding performance, there are lots of resources on the web. Tony Toews has one of the better pages at http://www.granite.ab.ca/access/performancefaq.htm. -- Duane Hookom Microsoft Access MVP "AccessNut" wrote: Thank you Duane, compact & repair did the trick and reduced the size from 1.6 gig to 39,208 KB. So simple, I don't know why I didn't think of that. In answer to your question, yes I do have images in a couple of the forms and reports. It wasn't a problem before, is it now a problem? The forms are still very slow to arrive when I click on the button to open them. Is there a quick fix for this? I do have many buttons, subforms, subreports, combo boxes, etc. - and sometimes many subreports/subforms in one report/form. Thank you so much for your quick response and fix for the size problem. Have a good day. Oh BTW - when I received the notifcation of a response - the two links provided, one to read the thread and the other to continue receiving threads neither brought me here. They both came up with blank screens. White empty forms. I don't know if telling you about it is the proper procedure. But since I have your attention and don't know whom else to contact, I thought I would mention it. "AccessNut" wrote: I split the db today into a fe/be because of the size. It did accomplish my goal of reducing the size of the be on the network, however, the front-end is still the same size. Since the tables are now linked instead of actual tables, I thought that the fe would be a smaller size? Since the database is approaching the Access limit of 2 gig, is there a way to reduce the size of the fe? Also, after splitting the db, it is unreasonably slow. I know that it was faster before because now the links go to the network, as opposed to the fact that I was running it from my desktop. Can I speed it up somehow? Please advise. Thank you Donna |
#8
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.6
I'm not sure about that particular suggestion since it mentions "Form save
time" rather than "record save time". It would be one of my last attempts at optimizing. Did you try the other suggestions? -- Duane Hookom Microsoft Access MVP "AccessNut" wrote: Thank you again, Duane, The link from my email "notify" worked this time. Who knows maybe I wasn't signed-on and thought I was...thus, the earlier blank page. Regarding the performance issue, I am still in the dark. I explored the link that you provided, but admit that I do not know exactly how to do it. This text from the article: "Delete the SQL from the RecordSource and RowSource properties of the form, ...subforms, comboboxes and listboxes...." ??? If I delete the RecordSource property of the form, how will the form (etc.) know where to get the data? Plus, I have many forms, subforms, and comboboxes - do I have to go through all my forms, reports, combo boxes, etc and remove the record source??? ???? In the text in the article: "Now in the Form_Load event load the appropriate SQL as follows ..." (code listed after my question. ??? What exactly does this code do? Does it pull in the forms (into memory) thus, saving the time that it takes to "load" these forms, reports, etc. into memory? I have coded into each of the buttons to close the form that the button is "in" and open the form indicated in the code, thus, only one form is ever "open" at one time. Do I have to put this code into every form? Do I have to personalize it with my form names or something? Sorry for all of the questions, it is just that whenever I am provided code as a solution I want to understand how it works before I go through all of the time and work to remove all of the record source property only to find out that I might have to put them all back in. I just don't understand how the form will know where to get the data without the recordsource that I now have.???? Thanks for all of your help, I really appreciate it. Below is the code suggested in the article. Private Sub Form_Load() Me.RecordSource = "qryLargeTable" Me.txtSomeField.RowSource = _ "SELECT SomeField " & _ "FROM qryLargeTable " & _ "GROUP BY SomeField " & _ "ORDER BY SomeField;" End Sub It also pays to clear the record sources in the Unload event as sometime these get saved with the form in Access 2000. Private Sub Form_Unload(Cancel As Integer) Me.RecordSource = "" Me.cboFindRecord.RowSource = "" End Sub "Duane Hookom" wrote: Your file size will increase dramatically if you store images in your tables. A few small images on forms and/or reports generally works ok. If I haven't logged in, I will get linked to a blank page if I click the second link. The first link to read the message generally works. Regarding performance, there are lots of resources on the web. Tony Toews has one of the better pages at http://www.granite.ab.ca/access/performancefaq.htm. -- Duane Hookom Microsoft Access MVP "AccessNut" wrote: Thank you Duane, compact & repair did the trick and reduced the size from 1.6 gig to 39,208 KB. So simple, I don't know why I didn't think of that. In answer to your question, yes I do have images in a couple of the forms and reports. It wasn't a problem before, is it now a problem? The forms are still very slow to arrive when I click on the button to open them. Is there a quick fix for this? I do have many buttons, subforms, subreports, combo boxes, etc. - and sometimes many subreports/subforms in one report/form. Thank you so much for your quick response and fix for the size problem. Have a good day. Oh BTW - when I received the notifcation of a response - the two links provided, one to read the thread and the other to continue receiving threads neither brought me here. They both came up with blank screens. White empty forms. I don't know if telling you about it is the proper procedure. But since I have your attention and don't know whom else to contact, I thought I would mention it. "AccessNut" wrote: I split the db today into a fe/be because of the size. It did accomplish my goal of reducing the size of the be on the network, however, the front-end is still the same size. Since the tables are now linked instead of actual tables, I thought that the fe would be a smaller size? Since the database is approaching the Access limit of 2 gig, is there a way to reduce the size of the fe? Also, after splitting the db, it is unreasonably slow. I know that it was faster before because now the links go to the network, as opposed to the fact that I was running it from my desktop. Can I speed it up somehow? Please advise. Thank you Donna |
#9
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.6
Thank you Duane, Tony and David,
I think we may have gotten "off subject" - my main issue is the reduction in speed opening forms and reports after splitting my very large db. I can remove the images, they are not that important. I do not think that the problem that I am having with forms opening slowly from the buttons clicked to open them has much to do with images - I had only answered that question because Duane asked it. The forms that are slow (since I split the db) don't necessarily have images on them - they do have subforms and my main issue is a report that has many subreports - it takes too long to open. The link that was provided by Duane took me to information that I am not sure how to use. I wrote that in my reply to Duane and inserted the code with the questions. I put my questions between ??? and ??? Sorry, I wanted to bold it so it would stand out, but lacking that function this was my solution (good or bad). Since the greater than/less than symbols are used in this forum for other reasons, I'm sure it was a bad choice. Please review my prevous response string - the answers that I am seeking, reference those questions. Thanks for your response. Donna "David W. Fenton" wrote: "Tony Toews [MVP]" wrote in : If logic if file available to view Then Me.PhotoImage.Picture = PhotoFilePath Else Me.PhotoImage.Picture = "" End If Just a quibble, but it is more efficient to use the constant vbNullString instead of "". It doesn't matter in a context like this, but in a loop, it can make a huge difference, as using "" causes memory to be allocated each time, while using the constant does not, since the memory is already allocated. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#10
|
|||
|
|||
Splitting an Access Database into fe/be-b'cuz db too large 1.6
OMG - I apologize, I just looked back and do not see my response.
I sent it this morning - now, I think that I read somewhere that it takes 24 hours for a post to show up???? I guess I will have to wait until tomorrow for anyone to see it. And, I guess you will have to wait as well. sorry. Donna "David W. Fenton" wrote: "Tony Toews [MVP]" wrote in : If logic if file available to view Then Me.PhotoImage.Picture = PhotoFilePath Else Me.PhotoImage.Picture = "" End If Just a quibble, but it is more efficient to use the constant vbNullString instead of "". It doesn't matter in a context like this, but in a loop, it can make a huge difference, as using "" causes memory to be allocated each time, while using the constant does not, since the memory is already allocated. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
|
Thread Tools | |
Display Modes | |
|
|