Problems with VB code in Access 2003
-
I work with a scientist who's invested a lot of time writing VB code inside several Access 97 databases. I helped her convert these to Access 2003 with support from MS because of strange problems we encountered, but not all of the issues were fixed and now the workarounds she needs to use are really slowing her down.
Is there a better platform to use other than Access? Straight VB perhaps? She uses forms and makes calls to a 3rd party graphics modeling package to generate graphs...
I'm a DBA, not a developer. Any suggestions?
Neil
-
can you provide more info on the work arounds she has to do and what needs to be done to avoid the work arounds
-
Asking if VB is a "better platform" is incredibly vague. VB is more powerful, but also more general-purpose. There will be a learning curve involved, bad enough with VB6 but huge with VB.NET.
I have done a lot of VB for over 10 years, and a little Access. Access forms have a lot of functionality that would have to be manually coded in VB, but I usually felt like I was trying to do keyhole surgery without being able to use the full power of VB. I'm not sure how a seasoned Access programmer will feel about VB, but I've never met a VB programmer that was excited about moving to pure Access.
If they are willing to work through the pain of moving to a different platform, I would recommend VB.NET. It won't be easy, but I think they will be much happier with the final product.
-
I wrote a lot in VB 6, Now I am using Access 2003. They are VERY similar. VB 6 was much more powerful, with a lot more obscure commands. If her programs were written in Access 97, there should be little that needs to be done to move them to 2003. The biggest issue I had was going from a DAO data object to an ADO data object. ADO is so much more complicated, and it took a bit for me to come to grips with the changes.
VB.NET may be the answer, but I know that I have as of yet grasped the concepts of programming in that different beast. I have all the 'Dummies, 'Idiot's, '12 steps' etc. available for VB.NET, but my poor old brain has some kind of mental block. That is one of the main reasons I moved to Access 2003. In our environment, it is a solution that works and everyone is happy with.
From the sounds of it, I think Jackaroe should stay with Access 2003 and resolve the workarounds one at a time.
This conversion stuff is for the birds...
Shalom!,Michael Lee
-
VB 6 and Access are very similar, but sometimes not as similar as they appear. Working with Access tables should be about as easy and familiar in VB6 as in Access, but I would be concerned with the behavior of forms and controls. They seem very similar at first glance, you use forms, buttons, combo boxes, etc., but Access controls have a lot of properties that provide a lot of behavior that must be added in code in VB6. Access forms expect to be data forms, VB6 forms require Data controls and lots of event code. The conversion isn't difficult, it's immediately obvious in VB that a bare form knows nothing about data access and you're going to go look for a way to add it. I worry more about things that look the same but work differently. The controls have different methods and events in the two platforms that can trip up the unwary.
A case in point is that Access buttons are modal. The OnClick event handler doesn't have to worry about being interrupted by other events, the button is automatically disabled until the handler exits. In VB, an OnClick event handler that starts a long process must first check to see if the process is already running and ignore repeat events. Or you might want to use a second OnClick as a Cancel action. But if you don't add this logic, and an impatient user clicks on Post Invoice again because they aren't sure it worked the first time, you've got a problem.
Converting to VB.NET would be more like starting over in a different language. It would be easier to convert to VB6 and then have a wizard upgrade to .NET. But the Access way of doing things may not be the best .NET way, it might be better to rethink the whole application and switch to DataReaders and DataAdapters rather than bound controls.
Nothing has been posted about the nature of the workarounds. If they are workarounds for some kind of (data-related) form behavior, switching to VB is going to require a lot work just to get to where the Access forms are now before you tackle the additional requirements. If you are torturing Access to achieve some kind of dynamic interactive form behavior, it might be easier in VB.
If you really want to go nuts, you can open an Access document as an object in a VB app, and have forms from both worlds running around the screen.
-
If the application was originally in Access 97 and now has breaks when call third-party graphic programs after upsize to 2003, check your references and classes in the upsized application. You may need to update the drivers or dlls for the third-party software that are compatible with the newer version. Will it compile (under debug menu)? If not then trace the errors and correct. You are on the right track, be careful and not move off of center or you will be inviting an incredible headache.
Mike
-
Also you consider changing your database engine from DAO to ADO in ACCESS 2003 when upgrading a solution from ACCESS 97.
N 56°04'39.16"
E 12°55'05.25" -
Thank you for your replies and sorry for being so vague. My coworker is vague with me and its been nearly a year since we sat down together and converted the first of several Access 97 databases with MS tech support's assistance.
We had two problems: dropped code fragments - like variable declarions - and a recurring error whenever saving code changes in the converted database: "You do not have exclusive access to this database" which blocks saving the database. There is a KB article on this last issue and I worked through everything with MS and the ultimate solution was to open a new database and copy the code from the converted database and paste into the new one and save. This works for a while and then the error returns.
The reason for my post was that before spending a lot of time again on this issue, I wanted to check and see if there was a better IDE than Access that had the benenfits without the bugs and also had a smooth migration path and simple learning curve. I read an article once which promoted something else over Access as an IDE but I don't remember what that "something else" was and I can't find the article now.
Given that my coworker is a scientist first and developer second - with limited bandwidth to learn new coding technique (and I'm no help) - from your collective replies, it sounds like Access 2003 is still the appropriate IDE for her. I'll look into moving from DAO to ADO.
Many thanks,
Neil
-
It sounds like she needs to learn to open the Access project exclusively before she starts coding. If that is unacceptable because other users need frequent access, she needs to learn the concept of having separate development, test, and production projects. These are not meant as derogatory comments, she may be a brilliant scientist but she is a little fuzzy on basic software development practices.
I once worked at a big corporate R&D center with a large population of PhD's. One genius in particular had habits like not using subfolders (5000 files with 8-char names in one folder), and instead of versioning or source control would just GOTO around dead code (he might need it again later). He never grasped the effect this had on people not blessed with a brain the size of a planet.
I think it would be a disaster to turn your scientist loose on VB anything without a guide. If Access 2003 doesn't solve your problems, then you should consider hiring a real developer to do the conversion.
-
Grin - You know my environment well. Thanks for the tips.
-N
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply