MaxLocksPerFile Error

I would lower it from 300k to 200k or so and try that 300k is imo a bit high unless you actually need it to be that high 200k is normally what i would reccomend as a max

I would lower it from 300k to 200k or so and try that 300k is imo a bit high unless you actually need it to be that high 200k is normally what i would reccomend as a max

I have set it back down, and i am still getting the TEMP File size error. Any other suggestions??

Thanks

James

I am running the table analyzer in access 2003. I have edited the maxlocksperfile entry to 300,000 and when i try and analyze the table i get a Not enough space on temporary disk error. I have a 3ghz processor and 1 gb of RAM and nothing in the TEMP directory. The database is over 90,000 records, but with such a large amount of ram and processor power, i dont think there should be a problem. What do I do from here?


James

This has absolutely nothing to do with your system specifications - they are more than adequate. If you are working on a Network Drive, it is possible that your Temporary Drive is not on the Network Drive. Access uses a Temporary Directory to store Temporary Files as you work with data. If this Temporary Directory is on a Disk that's short of Free Space, such as your Local Hard Drive, you may see this Error even though your Network Drive has sufficient Free Space. Check you Temporary Drive as such:

1. From within Access ==> Help ==> About Microsoft Access.
2. In the Dialog Box that appears, click System Info.
3. Look at the line that reads TEMP Directory, TMP Directory. This is your Temporary drive. Make sure you have adequate space available.
4. If all else fails, create a Make Table query based on the original 90,000 Record Query, but set a Criteria such that only, say 1,000, Records are created. Run the Table Analyzer on this Table. I would say that any suggestions made would also be applicable to the 90,000 Record Version. Good Luck!
   

How much space is available on your drive?
That's the only relevant issue as far as I can see. Processor speed & RAM size don't effect much apart from the speed.

I have over 50 gigs of space available. I have set the TEMP folder to C:\Temp

Any other suggestions?

Thanks

James

Your Temp folder and the 50GB should prove more than adequate. I don't know of anything else that would cause this I'm afraid.
I didn't realise yesterday that ADezii had posted just before me. Nice to see that he was saying much the same (albeit in more helpful detail).

I have over 50 gigs of space available. I have set the TEMP folder to C:\Temp

Any other suggestions?

Thanks

James

Did you try Item #4 in Post #4?

We (my office) are going to migrate the database to an MS-SQL server database, so we shouldnt have any problems. On a related note, are the forms that are made in access2003 going to transfer to an MS-SQL database? Am I wasting my time trying to build a front end form in Access 2003 if it will be migrated to an MS-SQL Server?

Thanks for your time,

James

In most cases, you don't migrate the front end of the project, you migrate the back-end (or data store). Our colleagues in the MS-SQL forum will tell you more about this, but it's more common to keep the user interface in Access while linking all the tables to a database managed by an MS-SQL server.
I doubt an Access form would migrate to MS-SQL, but go ask the experts there if you're still interested.

Thank you for your assistance.


James

No problem.
Good luck with your project.

As to migrating to SQL server. You can use an ADP to directly connect to your SQL Server. An ADP is like an MDB only it does NOT use the JET DB it uses SQL Server.

You will have to change the forms to some degree. Since you are in the DEV mode you can port the Access tables to an instance of SQL server into a temporary DB and then DEV you forms using an ADP. You can still make an executable version of the ADM but it is called an ADE as opposed to an MDE.

Personally I have found that using the migration wizard is a pain in the %&&. I would just import my tables from my Access DB using a DTS process and then I would DEV my queries and stored procedures, I would not migrate those.

There are differences in the forms you would use in an ADP and some of the VBA will differ as well, so there will be a learning curve. Since you are in the DEV mode this is the time to make the change.

Once you have the application completed (ADP) and are ready to roll it out you can make a new DB and import the tables again. Update the table structure with any changes you had made to the development version, import your views, functions, and stored procedures from the development version. You will then have to change the connection property for your ADP to the new version and deploy your solution.

An ADP is much faster than using ODBC connection. Another advantage is that you can use the stored procedures directly. All the processing is done on the server side and only the results are sent, sort of like a pass-through query only the record sets are updateable. You can not have local tables with an ADP and you will not be able to link to other sources.

This is worth looking in to.

We (my office) are going to migrate the database to an MS-SQL server database, so we shouldnt have any problems. On a related note, are the forms that are made in access2003 going to transfer to an MS-SQL database? Am I wasting my time trying to build a front end form in Access 2003 if it will be migrated to an MS-SQL Server?

Thanks for your time,

James

WolfBear brought up some very good points, one of which was DTS (Data Transformation Services) within SQL Server. This would be an excellent utility for migrating your data in SQL Server. To access DTS:

1. Programs
2. Microsoft SQL Server
3. Import and Export Data.
4. Data Transformation Services Import/Export Wizard.