access mdb with link to sql server tables, views compact problem

[Alibaba2003]

Hi,
I am in the process of upgrading my backend to sql server. I have moved the tables and some of the queries successfully to our sql server. I have linked the access front to it. However, any little use of the front end inflates the the size of the mdb file quickly . I have tried to compact and repair but it has no effect on the file size. I have imported all of the objects into a new database and the file size came back to normal. It is a little exhausting to do the later. Is there is a work around for this?

Thank you for your help
T.


It's Nice to Be Important But It's more Important to BE Nice

 

[palmermon]

have you tried pushing it off to an MDE during this dev phase. Its semi compiled and doesnt change much in size while youre developing. Youll have to push it around the building after each build, but i assume youre doing that already.

It will reduce in size once all the MDB tables have moved out. But PLEASE go for an ADP when its all done.

Palmermon - Always Thinking

 

[cmmrfrds]

Are you creating object in vba code? Do you destroy the object when finished with them?

 

[Alibaba2003]

Hi Thank you for your response. I will surely move to an ADP format soon. But what objects needs to be destroyed in the vba code. I set the connection and the recordset objects to NOTHING after i am done with them. Anything else that may contribute to this expansion? thanks

It's Nice to Be Important But It's more Important to BE Nice

 

[SiJP]

Each time you delete (destroy!) an object from your database, access is not clever enough to free up this space for use.. so you get the appearance that it is still in use. This is commonly referred to as bloating.

If any of your code delete's any objects (linked tables or tables or queries or forms.. etc etc) then it is highliy likely your db is getting a bit bloated.

It is strange, however, that a compact is not reducing the size!

Are any of your queries DELETE or MAKETABLE queries, which could be contributing to your data?

------------------------
Hit any User to continue

 

[cmmrfrds]

Those were the objects I was thinking of, but any object created in vba code should be destroyed.

I ran into a bug in Access a few years ago on an app that was upgraded from 97 to 2000. What happened was the app was not releasing the vba code from memory. Every time I worked on vba code it retained the memory so that it kept growing in size. This was fixed by importing all the objects into a new Access file so, I don't think, that applies here.

What can inflate the size of an app and does not add to functionality is using bitmaps etc.. to supply background colors to Forms.