Compact and repair: Could not find MSysComplexColumns

[Oriel Tzvi Shaer]

Thank-you both - I learned.
In my case, I don't have the option to do it locally.
The good thing is we have good network connection to it.
I tested C & R locally and on server (DB of appr. 200MB), and surely enough it took twice as long
(locally ~5 sec., server ~10 sec.),
but, as I said, it is not an option, in my case.

 

[Oriel Tzvi Shaer]

Indeed we do. All is done on copy only

 

[The_Doc_Man]

Then run the job on the server, NOT from your local PC.

Pat, that DOES assume the OP has Access available on the server.

In my case, I don't have the option to do it locally.

@Oriel Tzvi Shaer - I will have to disagree with you. In order for you to use an Access BackEnd file properly, you MUST have "MODIFY" level privileges on the BE file AND on its folder. Which means you can, if no one is currently using the DB, perform the C&R locally and then just overwrite the BE. If you are the "official" maintainer of the BE file, that IS a valid maintenance activity. When I was a contractor for the U.S. Navy, which had ALL SORTS of regulations about what I could and couldn't do, that kind of replacement activity was still allowed.

 

[Oriel Tzvi Shaer]

Then run the job on the server, NOT from your local PC.

If I understand you correctly, that's also quite impossible for me to take a trip to from Israel to Germany every time I would need to do the job.
If I didn't understand you correctly, then how do I get around doing it on the server itself from where I sit physically?

Pat, that DOES assume the OP has Access available on the server.



@Oriel Tzvi Shaer - I will have to disagree with you. In order for you to use an Access BackEnd file properly, you MUST have "MODIFY" level privileges on the BE file AND on its folder. Which means you can, if no one is currently using the DB, perform the C&R locally and then just overwrite the BE. If you are the "official" maintainer of the BE file, that IS a valid maintenance activity. When I was a contractor for the U.S. Navy, which had ALL SORTS of regulations about what I could and couldn't do, that kind of replacement activity was still allowed.

I have all access and previliges I need, but I need to do C&R specificly after I connect the linked tables of DB to external SQL Server.

Actually, maybe I should have made it clear earlier: I work with SQL Server databases.

Anyway, in order to connect to SQL DB, the accdb file need to be in Server - there I have the appropriate ODBC connections I need for connecting the linked tables.
What I could do is: after connecting the linked tables, copy the file back to local computer and then C&R - but that would really take much longer - if my whole concern is the time it takes.

In addition, the C&R happens in memory on your local computer so everything in the db must make at least one round trip between the server and your PC which dramatically increases the amount of time it takes. Take a large db that you don't care about. Compact it while it is sitting on the server and time it. Then compact the original version on your local PC.

I wanted to ask about this:
What difference does it make for Access if I am physically on Server or from afar?
How can it tell the difference?
What sense does it make to do the job locally - why does Access bother to do the job on my local computer?

Thank-you all for the patience!
Such environment really allows learning!

 

[Gasman]

If I understand you correctly, that's also quite impossible for me to take a trip to from Israel to Germany every time I would need to do the job.
If I didn't understand you correctly, then how do I get around doing it on the server itself from where I sit physically?

I get on to my sisters computer, who lives over 200 miles away everytim she needs help. You just use a remote access program. I use Anydesk as it is for personal use only. Plenty out there for business use, like Teamviewer and Anydesk, plus MS's own remote access.

Actually, maybe I should have made it clear earlier: I work with SQL Server databases.

What you did not make clear, is that you must be accessing the BE remotely? if it is Germany and you are in Israel?

 

[ebs17]

why does Access bother to do the job on my local computer?

Access generally works locally, both with VBA and when using its own SQL dialect (Jet SQL).

Compact & Repair - Are you talking about a frontend or a backend?

When repairs are necessary, you should always be a little nervous.
A frontend can simply be replaced with a clean copy.
Compacting a backend makes sense when there are major data changes, so that memory is released and index trees are rearranged.
Before compacting an Access backend, you should make sure that you have exclusive access to it, i.e. that there are no ongoing accesses from third parties, especially not write accesses.

There are various methods for compacting:
Application.CompactRepair
DBEngine.CompactDatabase

The second method is a DAO method and does not require the installation of MS Access, but only requires the provision of DAO (ACE) and could be run on any Windows system.

 

[Oriel Tzvi Shaer]

In my case it was simply a case of insufficient memory in the server I was running the Compact and Repair.
Clearing some memory solved the problem.

Returning to original answer to original question:
In my case (though FE yet...) it was simply a case of insufficient memory on the computer.
(taken out the "server" business - though it was fascinating learning new facts and experience from you all)
Clearing some memory solved the problem.
I hope this may help someone

 

[The_Doc_Man]

Actually, maybe I should have made it clear earlier: I work with SQL Server databases.

Well, that simplifies things a lot. You don't Compact & Repair an SQL database. SQL Server does its own housekeeping. The Access function built for this purpose won't work on an SQL Server database file. Some things CAN be done to force an SQL database do clean up some things - but you wouldn't do them from the C&R option.

Therefore, there is something you haven't told us. A table linked to an SQL database does not need C&R because that is an action specific to a native Access backend file. So... what is the target of the C&R operation? What is actually going on here?

 

[Oriel Tzvi Shaer]

At the end of the day, in the way we link the tables to specific SQL DB, it causes the size of the Access file to increase significantly, and C&R helps return it to it's original size.
Though it really doesn't matter - why, I am trying to focus on answering the original question, whether it gives an answer to the original asker or to other asker - it could be that clearing memory may help someone solve such a problem