Splitting an Access Database into fe/be-b'cuz db too large 1.65 gi

  • Thread starter Thread starter AccessNut
  • Start date Start date
A

AccessNut

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
 
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.
 
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.
 
AccessNut said:
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?
Click to expand...

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
 
If <logic if file available to view> Then
Me.PhotoImage.Picture = PhotoFilePath
Else
Me.PhotoImage.Picture = ""
End If
Click to expand...

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.
 
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...."

In the text in the article: "Now in the Form_Load event load the appropriate
SQL as follows ..." (code listed after my question.
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
 
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?
 
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
 
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
 
AccessNut said:
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.
Click to expand...

Good enough on the images.

With respect to performance go back to Duane's reference to my web
page. The single most important performance tip after splitting an
MDB is LDB locking which a persistent recordset connection or an
always open bound form corrects
http://www.granite.ab.ca/access/performancefaq.htm

Tony
 
Back
Top