SSW - Database FAQ & Links

How good is Microsoft Access and the Internet?

Debbie wrote:

Hello Adam,

I was looking for info on ACCESS and HTML, I had no luck on www.microsoft.com, and saw a message you had posted to comp.databases.ms-access about .idc files. I've also tried calling Microsoft (nobody was there!!), so I'm not getting very far.

Basically, our company wants to configure some home pages where the data is stored in a database (most probably Access) and people will visit the pages and get data pulled out of the database and displayed using HTML files. This sounds reasonable to me, the question is, can Access handle it?

How do you like Access? We're thinking about getting it.... if we can get any info on it, such as can well on portables, can it run on Unix, can it handle HTML, can it connect to other databases?

Well, thanks in advance for any advice you can give.

Debbie.

Adam Answered:

Yes, there is very little information about Access and HTML. It is very new technology and very little has been written about it. I spent many hours gleaning the information I needed to finally get a web site/access system running for Magna Data an Internet Service Provider in Sydney.

Access works very well with Microsoft NT Server, Internet Information Server and .asp (Active Server Pages with SQL in them). I am very impressed at the speed and ease of use, once it is setup. The nice thing about Access is it is user friendly and fairly easy for non-programmers to maintain.

For that job Internet users can visit their site, ask to see information on specific products. Then they can even register online using the registration form.

This is just a little application I wrote to demonstrate a sample use of Access, hinting at much bigger uses. Access works great at taking Internet user input, processing it and returning information in an html document.

Selecting Access versus SQL Server depends on how much traffic you expect to get at your site. Access is not as robust as SQL Server, but easier to use and maintain (cheaper). SQL Server is the database that Microsoft uses for its giant web site (BIG BUCKS).

The nice thing is that with Access you can start small, with a Pentium 266 64 MB RAM and 4 gig hard drive, maybe a $5,000 investment in hardware and software. Then, as your site develops over the next few months, you can simply add another 64 MB of RAM and then maybe a file server via ethernet with as many hard drives as you want, etc. Then if your site gets really hot, you can move to a DEC Alpha with SQL Server, etc. I guess the point I am trying to make is that Microsoft has provided upgrade paths for you, so if you select Access you can later move up as it makes sense, to bigger equipment and SQL Server.

Access is a Windows product which means it will not run on Unix. I have used both Unix and Windows. Unless you have a Unix engineer in your office now, don't use Unix. It will soon be a dead language, because Windows NT is SOOOOOOO easy to use. It is Windows, you know. Bill Gates announced Wednesday that all of their software is going to be Internet aware. However, if you like a DOS-like prompt and know what this command means i1 ,then Unix might just be your language of choice.

Access can connect to many other databases. If a database has an ODBC driver, Access can connect to it. By the way, I am using Access 2000 for the Internet work. However, Access may not be able to connect to a proprietary database of custom design.

Access will run on any Windows capable computer with at least 16MB of RAM, including laptops. WARNING! You will be very unhappy running Access 2000 on any computer less than a Pentium 200 with 64MB. This is true with all Win 95 products.

Well, that should give you some idea, I hope I got you excited about using Access for your project. If you need some help, please call on us. Our expertise includes:

1. Active Server Pages (ASP) development

2. Internet Information Server (IIS) setup and maintenance and

3. SQL Server 7 database development

4. SQL Server 7 setup and maintenance

Adam Cogan
Sydney, Australia
SSW
adamcogan@s*w.com.au

Real deal on the Decompile switch

Michael wrote:

I saw you at the Microsoft Seminar earlier this month in Perth, which was very useful thanks. If you have time I do have a question.

You probably won't remember but I asked you in the break about copying and pasting reports and forms in Access 97 causing Access to perform an illegal operation and 'hang' next time you opened it. Your reply was to use the "/decompile" switch in the command line. Not only has this worked extremely well but it reduced the file size from 8Mb (a lot of code in this database) to 4Mb.

However, I can find no information at all anywhere about decompile either in the Access Help, Knowledge Bases or anywhere on the Microsoft web site. Maybe its there, but I can't find it.

My concern is that the message you get after decompiling is something like "Access has upgraded your code to the version of Visual Basic on your system", and I'm worried that if I distribute my database to end users (either 'as is' or as a setup with runtime Access) that if they don't have MS Office SR1 and SR2 or the Jet SR pack or whatever else I might knowingly or inadvertently have on my computer, will that cause a problem? In other words, what does "the version of Visual Basic on your system" really mean and is there any danger to using this apparently undocumented decompile feature, particularly regarding distribution of the end result?

If you have a minute to answer or point to a resource (Access 97 Developers Handbook, Litwin/Getz/Gilbert doesn't appear to mention it either) I would be very grateful.

Thank you.

Michael.

Adam Answered:

This is an undocumented feature. Here is real deal on the /Decompile switch (Originally posted 5/22/99 by Michael Kaplan) This post is to give a little historical background and info on this undocumented command line switch in msaccess.exe.

To use it, you simply run:

msaccess /decompile <your database name>

and that's all there is to it. But what exactly does it do?????

VBA AND THE 11 STATES OF COMPILATION

That's right, internally there are 11 different compilation levels between decompiled and fully compiled like you would find in an MDE. As you make objects dirty, you will decompile the project, but dirtying Module1 does not remove all the "compiled" info about Module2 or Form1, for example. The exact levels are impossible to even detect unless you have source and debugging symbols for VBA, and insanely difficult even then.... so lets just leave it as read that the yes/no question of "is it compiled?" has many subcategories under the NO answer that essentially mean its not compiled but some parts of it are kind of.

P-CODE VERSUS CANONICAL TEXT

Your code is stored in two forms, each one of which is a Stream object in the project's storage(s). One form is the canonical text that you look at and edit and such, the other is the compiled version of the code that runs.

VBA must always compile before it runs, so in an app that runs you will always find p-code. And unless you are running in an MDE (where the canonical code is stripped out) you will always have the canonical text too. Any time VBA thinks that the compiled code is invalid (such as when you make a change or the binary format changes, which is so far only during beta cycles), it will "decompile" the module and then compile it again from the canonical text.

ACCESS BETAS: BINARY FORMATS, ETC.

People who were on the betas for Access 95, 97, and/or 2000 will remember the binary format issues. From build to build, changes would be made in VBA or in the Access OM which would make old compiled code invalid. Usually a crash is the best you could expect. To help fix this, some work was done to have a global way to decompile *ALL* code that is present in a project so that you do not risk having any invalid code that might crash.

**********

This is the reason the flag is there and the only reason. The command line switch is undocumented because there is never a binary format change except during betas and in internal builds.... so there is no reason to document something never intended to be used.

**********

NOW there are some positive benefits that are side effects that people have made use of:

1) SIDE EFFECT: CORRUPTED PROJECTS

Now, as a side effect, you have a way to deal with corrupt projects! You see, the canonical text is never what is corrupted, it is always some compiled portion of a project, like a module or most commonly the typeinfo of a form or report. By globally telling Access that the compiled portion should be thrown away, you get rid of whatever the bogus piece of code is.

Now this kind of fix is what would have taken care of the old Access93 vba232 page faults and other problems where Access was walking off the end of a vtable and crashing, as well as a zillion other such little problems. This is what made PSS first expose this flag for.... if a project is corrupted, this is the best way to uncorrupt it.

2) SIDE EFFECT: PROJECT SIZE

It was found that there are times where an object would be decompiled and while the Stream object from the storage would be properly invalidated, it would be orphaned and left in the Storage, and then would not be cleaned up later.

There are many applications that use structured storage that have such a problem in their garbage collection... VBA/Access is just one of them, that's all. Over time, these orphaned streams will contribute some bloat to the project. People noticed that a fully compiled app would take up more space than the same app fully compiled with all objects imported to a new database..... and that is the very issue being discussed here. As you may have guessed, the /decompile switch, which invalidates *all* streams that contain compiled code, does an effective job of garbage collection and removes these orphaned streams. Thus, a /decompile /compact will make for the smallest possible size of as database.

RISKS TO DECOMPILE: WHY YOU SHOULD NOT USE IT CONSTANTLY

If you think about the mechanism, you are relying on the canonical text always being completely valid, and you are relying on the ability to globally invalidate a compiled state. If there is ever a problem in either area, /decompile will take a project that was working fine and turn it into cottage cheese. And while such bugs should not happen.... it is impossible to make a /decompile bug happen without using /decompile. They simply did not extensively test a command line switch that was never meant to be used.... nor should they have to, really.

SO, PLEASE REMEMBER that this is a very powerful technique that was added for reasons having nothing to do with any of the reasons that you may want to use it now. It may help you save an otherwise hopelessly corrupted project. But use it sparingly as you may end up in a worse situation than you started by just globally using the switch on projects that do not need it.

IF IT AIN'T BROKE, DON'T FIX IT.

Adam Cogan
Sydney, Australia
SSW
adamcogan@s*w.com.au

What is client/server?

Client/Server database systems are different from traditional database systems in that the database exists on a dedicated server machine. Instead of network machines "fighting" each other for the information needed, a dedicated server processes requests from network "clients". This greatly increases reliability, since access to data is controlled entirely by the host machine. Security is much more easily enforced since authentication is done by the server machine.

If a client/server system has been properly designed, the overall system speed will be increased and network traffic decreased, since workstations only receive the data they asked for.

Will Microsoft Access be appropriate for my company? What about Microsoft SQL Server?

Question:

My Company is considering starting development of a custom application in Access 97. Because they don't want to spend much money at this point, they want to use Access database at first and switch to SQL Server some time in the future. Could you tell me how difficult it is to switch between these two databases? Anything I could do now to make the conversion easier when it comes?

Answer:

Switching from Access (Jet) database to SQL Server isn't as simple as it seems. Yes, the dialects of SQL are similar, but they're not the same. More importantly, while SQL Sever is a true relational database, the Jet database is an Indexed Sequential Access Method (ISAM) database - also known as a flat file database. Although we could argue the differences between the two for hours, suffice it to say that, in many ways, they're exact opposites.

Putting this difference in simple terms: What's fast in Access is slow in SQL Server, and vice versa. In Access, the fastest way to get to a record is to open up a table object, specify an index, and seek to the record. If you want to pull data from several tables, you can open multiple table objects and seek on them all, assembling the data programmatically.

With SQL Server, you can't use a table object at all. While indexes certainly exit (and are vital), you don't normally concern yourself with them directly. The best way to pull data from several tables is using a join query. SQL Server is at its best when it pulls small record sets - whether from one table or joined from several. This technique is painfully slow in Access.

More importantly, with SQL Server, there are far more features and protections for data. In a professionally configured SQL Server, the users (and their front-end applications) can never write directly to the tables at all. They access the data through views, and modify the data using stored procedures. None of these techniques are available in Access.

So, put this thought to your money-saving employer: Are you actually saving any money by planning a conversion like this? In short term, maybe. In the long term, no. Yes, for the moment, you'll delay buying the hardware, software, and training needed to configure SQL Server. In the long term, you'll still have to buy all those things and convert the code. And you never know how these code conversions can go; it's a difficult thing to predict.

Never underestimate the value of a known cost. You know now how much the SQL Sever is going to cost, and you're already planning the initial development costs. There's really no way to know for sure what the cost of conversion will be. If you stick with the known costs, at least you're certain of what you're in for. Conversion can eat up a lot of dollars (and time) before it's completed. It's an unknown, which makes it the worst cost of all.

Microsoft Access Server Headaches

As a consultant, I am often called into projects that are running behind schedule and require additional resources. This year, a stock broking corporation called me in to convert a mainframe application to PC environment. The majority of the company's income was generated from the application I was converting. Not only was I converting their money maker, the system was required to be up and running within six weeks. The project manager decided that I should build the system using the Microsoft Access database product.

The application design specified a maximum of three concurrent users. Based on the type of queries that were to be performed, I felt comfortable in stating that the performance would be acceptable for the users. After rushing to meet my deadline, the system was implemented. Everything went smoothly until this company's business skyrocketed and more loans than anticipated had to be processed. Before I knew it, the number of users had increased to fifteen.

With fifteen users on the system, the network came to a standstill. The reason the application brought the network to standstill is simple: in a Microsoft Access server architecture, all database processing occurs on the local PC. Therefore, when the users issued complicated queries to the server, the network jammed with data being sent back to the local workstations. Often, the queries being issued from the applications required thousands of rows to be returned to the local PCs.

In the Microsoft Access server environment, this is the equivalent of calling a car dealership and asking how many blue pickup trucks they have in stock. To get the answer, the dealer drives every car to your house and you count the number of blue pickup trucks. Obviously, this is not very efficient. In the Client/Server database computing environment (ie. Microsoft SQL Server), a different approach is taken. Someone at the dealership counts the number of blue pickup trucks and passes the information back to the caller.

Eventually, the mortgage banking system was rewritten using a Microsoft SQL Server as the backend. Performance was improved, network bottlenecks were decreased, and users were happy.

The Year 2000 is all over....

It's all over now and all SSW escaped unscathed but invalid dates in databases still cause problems from time to time. All versions of Access and SQL Server internally store dates using four digit years. (Actually it is stored as a number). Hence the reason why valid dates stored in time/date fields were not be affected when the century ticked over. The main problem lies in the initial entry of dates, either manually or from imported data. Access 2.0 will interpret any two digit year as starting with an implicit 19. Access 95 varies depending upon which .dll's are on your system. Access 97 assumes all two digit years below 30 are in the 21st Century and all other two digit years are in the 20th Century. Access 2000 does the same but gives you options in Tools - Options to configure this.

At SSW we feel that it is best to subject all incoming dates to a custom date validation routine. (For example we prevent data entry errors such as 1/1/199). This has the added advantage that you can modify a single function to change the rules for date validation easily as time progresses or your internal business rules alter.

At a PC level we also have had problems. Some of the built-in date functions, such as Now(), read the date from the Windows system clock. If this is correct, then the Now() function should work okay. However some users accidently change the date, resulting in invalid dates being passed to the Now() function. This is a system wide problem and, as such, we feel that it should not be corrected inside Access (since all sorts of other dates on your system will be wrong). Instead at SSW we include a test in the start-up routine of all of our databases applications that tests the Now() function and makes sure that it is later than 1 April 2000 (or whatever the date is when you program the routine). If it is less than this date, then the BIOS is reporting an incorrect date, a warning message to that effect is shown so you can get the problem resolved. This also solves problems caused by CMOS battery failure, so it's a good routine to use anyway.

PS Windows 2000, Windows NT 4.0 and Windows '98 will apparently trap and correct these BIOS date problems.

A related matter is the display of date information. As a rule we advise that all date information is displayed using a custom format such as d mmm yyyy (move the fields around to suit your own country format) rather than using the Short or Medium Date formats which only show two digit years. Using a four digit year display format gives added re-assurance to the user that the date information in the database is correct.

How do I find out if SSW Consulting Services will fit my needs?

Contact us, and we will discuss your project with you. After understanding your situation and identifying your needs, we will make a proposal for you. At this point, it's entirely up to you whether you want to go ahead with the project, seek alternative solution or adjust the proposal to your needs. Call SSW to organize time to discuss your situation.

What is the typical size of SSW Consulting Services?

SSW projects can range from a few hours to several months, depending on your needs. If you are interested in discussing a particular problem or your application's architecture for a very short time with a SSW consultant, check our web page SSW Consulting Services for the rates. Whether you require a Database, a Web Site, or both, we can provide your business with a customised solution.