Replies

Thanks for your suggestions Wendell and Charlotte, I made some decisions on my own, but I took your ideas into consideration. I’ve decided to go with MDB/ODBC. I found that the specific queries that caused this architecture to slow down involved a lot of Access-extensions in the SQL and/or heavy reliance on calculated fields. If I understand correctly, in situations like this both Jet and the ODBC provider will process pieces of the query and this causes a drastic reduction in performance. If I target those queries as the first to be enhanced with stored procs, my front-end should perform better overall from the migration to SQL Server. I also have a few tables with static data in the client MDB’s as well as some functions that create temporary client-locallized tables, which would not have worked as-is in an ADP setup.

I steered clear of the upsize wizard from the beginning because I didn’t think it was necessary. Since all that really needed to be converted was the Access data MDB, I used the Data Transformation Import utility in SQL Server. I haven’t revisted the database since then, except to write some stored procs, so that is surely to be where a qualified SQL Server DBA can work his or her magic.

A question about performing backups for Access MDB files: These files cannot be properly backed up unless they can be accessed in exclusive mode. In this situation, the database is in use 24 hours a day, and shutting down the system is a costly proposition (makes you wonder why this isn’t running on SQL Server already, huh?) My plan for the interim was to hook up another MDB with code that would simply create a new uniquely-named MDB and export the contents of all database tables to the new MDB, and this could run on an hourly basis. Any foreseeable problems with that?

Mind if I butt-in on this thread? I have a situation right now that I am trying to work through and it matches this thread to a T.

For several months I have been working for an agricultural specialty-crop-sorting company who put in place a MS Access 2000 database to track product as it flowed through their plant. It’s up nearly 24/7 and it uses a wireless connection on several forklifts using ASP to update the location of products in the database in real-time, and a MS Access frontend that is linked to the database for reporting and real-time status updates. The whole setup really seems to push the envelop of what Access is capable of.

My part of the project is the MS Access front-end reporting and status modules. Everything has worked great for quite awhile and this client is very pleased with the work I have done. But last week they suffered their first database crash and realized that the regular daily backups were not effective (I had no idea that they were not shutting down the system to perform their backups). They lost three hours of production data and had to rebuild it from paper trails, etc – took them two days to do this. I don’t think they want to bring the system down once a day to perform a backup, although I believe that they are doing just that since the crash.

Their tech coordinator now wants to implement SQL Server, which I originally thought was an excellent idea (I pushed the idea months ago), but I have no experience with the product. I’ve tried to get up to speed and I think I understand the issues, but I’m not exactly sure how I should go about migrating my existing Access front-end to link with SQLServer.

I see two options:

1) Continue using .MDB front end
2) Change to ADP front end

I have experimented with both and I like (2) best because it’s use of OLE DB makes it run faster. Using (1) seems to slow the beast down even more than it was to begin with. Another problem however, is that I am rarely onsite for this client, and performing mods to stored procs, if I am not mistaken, would almost certainly require work to be performed onsite, whereas with MDB files, I can simply send an update via email and discuss issues via phone. I have a day job and cannot be on location daily – luckily the tech coordinator works around the clock and never sleeps .

Anyways, my main concern right now is that my existing Access applications make heavy use of queries both as Access objects and in VBA. There are a lot of situations in which I have included VBA functions directly in the queries (i.e. Format, iif, isnull, and my own creations). In one particular instance, I have the following line of code to assign row data to a listbox:

Forms(“frmDumpLoadHistory”).Controls(“listDumpHistory”).RowSource = “SELECT * FROM qryDumpHistory WHERE [CurrentBatch] = ” & bmPrimary1BatchNum

This effectively filters qryDumpHistory by the CurrentBatch field where CurrentBatch = the current batch number for the primary batch process.

I expect that in an ADP alternative, I would have to create a parameterized stored proc and pass the batch number as a parameter to the stored proc. But I don’t know how to pass parameters to a stored proc within VBA code. Can anyone help me out here? I’m quite certain I can figure out workarounds for the imbeded VBA functions in my queries but passing parameters is my shortcoming.

This client wants to move to SQLServer in as little time as possible, and I’m quite certain I will have to tell them it won’t happen as quickly as they hope for. Any ideas on how to manage this change?

Are there any other major issues that I haven’t mentioned that I should be aware of in my predicament?

Thanks, I’ve always found good information here at Woody’s.

R

Hi there,

In reference to what you said:

“Is you database split with data in one and the rest in another – if so you might consider making the front-end a MDE – they go corrupt less often, and also make it READ ONLY at the OS level – then it can’t go corrupt”

How does this stop the back-end from going corrupt. I am going to try this solution, but it kind of bugs me I can’t figure out why this will work.

Thanks,
Rob

Thanks! By following the information in both microsoft articles, I was able to solve the problem.

Since the necessary files were installed in the proper location, the problem must have been in the windows registry.

My co-workers are happy once more!

Hi Mark,

I think (hope) you are right. We tried to re-run the Office97 setup last week and I thought we had everything included, but I’m not sure how carefully we checked the data access options.

Shortly after I sent my previous post, I checked the microsoft knowledgebase and found article Q155666, entitled “Troubleshoot ‘Couldn’t find Installable ISAM’ Error”, and it says pretty much what you told me. The file in question is MSXCL35.DLL, but when I checked today, both machines had that file. If nothing else, I now know what ISAM means (indexed sequential access method)

I don’t have admin priveldges to run setup, so on Monday I will try running setup again and see what happens.

Thanks for your help,

Rob

Good day!

I have a very similar problem. The difference seems is that my code is breaking on a Right() procedure calls (it will also break at a Format() call if I comment out the first one).

I recently transfered my projects to a different PC, and the old PC has already been re-imaged.

In my references, I have found that “Visual Basic for Applications” is selected, pointing to VBA332.DLL. There are three other “Visual Basic for Applications” references that are not selected, but the VBA editor won’t let me select them – it says I’m not allowed to select the same reference names twice.

I also have a MISSING: reference, “Microsoft Scriplet Component”, and the file doesn’t exist anywhere on my PC. But I don’t believe that Right() and Format() procedure calls have anything to do with that library, do they?

I’m perplexed by this situation, and I appear to be the only person my company who knows anything at all about VBA.

Once I get this to work on my PC, can I only hope that my end-users’ PC’s will have the same libraries? (I guess they should have the company std which should be the same as mine).

Can anyone help?

Thanks,
Rob

Thanks Geoff, this got me started. I don’t have a lot of time right now to tinker with this, but I attached this code to a button control and it worked like a charm. Gratefully it doesn’t use FileSearch at all.

Thanks for the tip, I can definately start my project with this.

Without eating up a lot of useless bandwidth, I’d like to echo that sentiment. I have had many questions that posts – and answers to those posts – in this forum have started me in the right direction. The fact that this is my first post is proof that there are lots of us out here wandering aimlessly through MS Office and learning a great deal.

Thanks to everyone here!!