Chad said:
We are in the middle of trying to convert an Access 2003 front end to
talk to an SQL server. Our front end has 100+ forms for which we've
spent about 160 hours changing code and syntax to deal with the
differences between an Access back end and SQL back end. This front
end was previously in Access 2000 format.
I would assume you stared out with an application that compiles ok..right?
I can't imagine you would start spending money and developer time to upsize
the data to sql server, but not fist fixed any compile errors in the
existing mdb file. In other words, you have a broken application, and you
are now moving trying to get this broken application to work with sql
server. I mean, most development systems do NOT let you run things with
broken code..but it can be very helpful, or your case, this ability sounds
like ms-access has given you too rope here.
The general approach to developing is when you make a small change to your
code, you then compile it..right? Surly, you did not willy nilly go all over
the place changing code, and then trying to compile later on? That makes no
sense at all. Further, some code obviously calls and uses other code. If
other code can't be compiled, then your current routine you are working on
can't get, nor have proper syntax checking either. So, if one routine calls
another, and the parameters don't match, then you get a compile error.
However, if the other routine can't compile, then this parameter and syntax
checking cannot occur (this would be impossible to resolve). So, compile
order is likely in order of dependences.
And, it should be noted that you don't have to make a mde file to compile
here.
Further, the mere fact of linking the tables to sql server should NOT cause
any compile errors at all. In other words, the instant you linked all your
tables to sql server, your mdb file should still have compiled .
I am sure during the normal devilment process you compile frequently and
often...right? In fact, I compile every time I modify some code in a form.
All you do it hit alt-d, and this highlights the debug menu (hey, by the
way, does not the debug menu have a good name in this case!!!). The first
option in this menu is "compile", and thus I just whack enter. In fact,
alt-d, Enter key happens so fast and often, I don't even think about his any
more.
So, often, I go alt-d, hit enter key...product compiles. If I just made a
type-o, then the compiler shows me the mistake. FURTHER, my cursor is put on
the mistake I just made. If no compile errors, then I whack the alt-f4 key
to close down the VB ide. Now, I am right back to my form code, at this
point, I go ctrl-f4 to close the form (you can see, I don't like Mr. Mouse
very much!!). After ctrl-f4, my cursor is usually sitting on the form I was
working on..and so then I hit enter key run and test the form.
The problem is that we've now spent about 120 hours trying to work
through compile errors, mostly relating to recordset statements, I'm
told.
Hum, the above sounds strange. It is not clear if you hint that errors
occurred as result of trying to convert to sql server, or in fact those
errors already existed?
The odd thing is that the program works in MDB form without any
perceivable errors.
Golly, you can have tons of compile errors, and if you run parts of the
system that don't run that bad code, then sure, the application will seem to
run. Really, a good sound developer process here is to never allow a mdb
into production (I never do). I mean, how will you know the syntax is
correct until you try can run the code? It stands to reason that you want to
compile frequently, and this occurs a LONG LONG time before you every get to
making a mde.
Now, you can't fix what is done here. However, lets just say it is very bad
to start a migration to sql server without first making sure the application
compiles. Hence, I would have got the old "non" sql server version working,
and got rid of all compile errors at that point.
As I mentioned, if you take a perfectly working mdb , and it compiles file.
You then upsize and move all the tables to sql server, and then LINK all of
the tables. At this point, the code will COMPILE in this case! It should
NOT break. .
Ok, so, someone messed up. If this was only a day into this, then I would
suggest going back to the original non sql server mdb, and getting that
working correctly, and removing all compile problems. It is a BAD IDEA to
both try and upgrade to sql server, fix things that will need to be changed,
and on top of that try and fix compile errors that EXISTED there from
before!!
And, as mentioned, after moving the data to sql server, NO NEW compile
errors should occur here. Sure, some code will be need to be changed (about
95% or MORE of the code will actually run as is, and NOT have to be changed.
Thus, the code will compile, but in fact NOT work as you need).
I should also note that our front end file keeps growing in size with
each successful attempt to convert to an MDE file. This seems
strange.
Well, hum, during the development process, the front end does bloat and
expand a lot. I didn't know that converting to mde (and failing) would cause
this..but then again, you don't need to convert to a mde to compile here.
Sure, the final step before you release to your users is making a mde, but
as a developer..you simply use the debug menu..and compile. So, you can (and
should) frequently compile from the VB ide. If there is a error in the
current routine you are working on, then that is first error you will get.
After that, I really don't know the sequence. I suspect routines that are
dependent on the current routine, or routines that call the routine you just
modified need to be compiled (since, as I mentioned, correct syntax in the
routine you currently are can depend on other routines that you write).
I would simply open up the first code module (non form), and then go
debug-compile. Any errors you get in that module can be fixed. Now, go to
the next module (I have about 20 modules in my last project). Once you get
all modules compile. Then, I would open up the first form in design mode,
whack the code button, and then again in the IDE go debug-compile. Any
errors in the current module world be shown first. So, at least this gives
you good attack plan here. So, errors in the form you are working on should
show first.
