Access 2000 database upgrade

[Nitsirk]

Greetings everyone!

I am new here and was hoping I could pick your brains regarding a database upgrade. I am currently running an Access 2000 database that is in serious need of some fine tuning and upgrades. As I perform these upgrades I was pondering upgrading the database to Access 2003 or 2007. I know that ideally it will be a seamless transition but I also know that realistically this will not be the case.

Has anyone else gone through that can tell me the issues they experienced when doing so? I am not looking for specific "how to" adivice. I am more looking for warnings and/or success (or horror) stories regarding this transition.

The idea I had was to keep the back end in Access 2000 while I upgrade the front end to Access 03 or 07. During this time I would keep the end users using 2000. Do you think it would cause trouble for my tables if my front end was 03 or 07 and accessing a back end of 2000?

Thank you in advance for any advice you can offer. It is much appreciated.

Nitsirk

 

[Bozo]

Can't help you with your Access problem but; Welcome to SF :-D

Bozo :joker:

 

[ddrueding]

I suspect that Access 2007 is a bit new to have many experiences with it. But this seems a pretty easy thing to test; how big is the data set and how many users do you have?

 

[Nitsirk]

The entire thing is only about 15MB or so and I currently have 10 users.

 

[ddrueding]

Considering the process of upgrading is the pain, I wouldn't think of upgrading to 2003. That just means you'll have another upgrade even sooner. If you have Access 2007, this would be really easy to try out. Otherwise, I happen to have just upgraded and could take a look.

 

[Mercutio]

For what it's worth I've been using an Access 2000 database in 2007 all week. So it'll at least open old files without issue. I dimly recall having issue with Access 2000 converting older MDB files by default.

Microsoft says that when you wave your magic "Convert Database" wand, the file format just changes and you can use your new DB.

From what I can read about the new format, unless you have actual need for the new data types, there isn't much reason to switch up, any more than there's a compelling reason to move to .DOCX or whatever.

 

[Nitsirk]

Well, the reason I am considering the upgrade is that we are tossing around the idea of upgrading all the other office programs. (this is nice way to put it)

In reality my boss said, "Get everyone on Excel 2003 because the VBA code I wrote for Blah Blah Excel program doesn't work on So and So's computer and I don't want to fix it." He has a special way about him. :roll:

I figured that I might as well get everyone on 2007 professional everything and be done with it for a few years if I was going to spend the money on ugrading everyone's Excel. I also figured why go with 2003 when its already 4 years old. Therefore, this leads to the question of would my Access database work.

I have been using a demo of 2007 for a few days now but haven't tried out the Access yet. I will probably do that soon.

I shudder at the amount of phone calls I am going to get when they start using Office 2007 since it looks so much different that what they are used to. I only have about 10 people to deal with but about half of them are very needy. I understand that this is just part of the territory and what one should expect; however, it is still going to be painful nontheless.

Thanks for the speedy responses guys, nice to meet you all.

Nitsirk

 

[Mercutio]

Looks like Access 2007 will leave your DB format unmolestered unless you choose to run the Conversion Wizard. I just opened a half dozen different .MDBs of various vintages (2000, XP, 2003) and they all seem be OK and functional before and after I used 2007 on them.

But I am not an Access person either.

 

[Adcadet]

Welcome. Does this mean we've got our first female member?

 

[Nitsirk]

Welcome. Does this mean we've got our first female member?

Nitsirk <--------------------- yes, female :flower:

 

[Nitsirk]

This isn't a boys only club is it?

 

[Handruin]

No it isn't, but you might think so. There are very few females who: 1.) openly confess they are female AND 2.) enjoy the geekier side of things like computers and storage, etc.

 

[Nitsirk]

Well, I did just open my database in 2007 and even converted the file. So far nothing appears to be amiss. For the moment it appears I was being overly cautious but I am not fully convinced until I have time to do further testing. I really expected it to blow up. It couldn't really be this easy could it? Time will tell.

 

[ddrueding]

Not to fuel any paranoia, but I wouldn't have expected it to blow up. I would, however, expect it to do some random thing slightly differently that may or may not affect your situation.

 

[Adcadet]

This isn't a boys only club is it?

I only ask because I was trying to figure out what a "Nitsirk" was until I realized that it was a name backwards.

And no, I'm not Ted Acda, or anything.

 

[Nitsirk]

I only ask because I was trying to figure out what a "Nitsirk" was until I realized that it was a name backwards.

And no, I'm not Ted Acda, or anything.

Most people don't figure that out right away.
You are smarter than the average bear Ted Acda. :thumbright:

 

[Mercutio]

I think Nits is the fifth female-type-person to sign up. We had some posts from Tannin's tech Kristi and some others in some of the darker topics in the Pub from someone named Hannah and there have been a few others with questions in the support forum at various times.

Anyway, ddrueding, if you don't expect Access to blow up, it's 'cause you haven't had to deal with much Access. In the past it's been notorious for having compatibility issues with previous versions of its native file format. Generally, the issues are with things like incompatible or unavailable .OCXs or VBA that works in one version that doesn't work in another. The problems are pretty much concentrated in the forms, rather than the data, so I understand it's a 100% standard practice to build Access applications with the data in one .MDB and the forms and whatever additional code in another.

Now, if Access 2007 doesn't do that, all I can say is that it's about damned time. It only took them seven major version releases to get to that point.

 

[Adcadet]

Most people don't figure that out right away.
You are smarter than the average bear Ted Acda. :thumbright:

Rat. Why can't people just accept this? I thought the beady red eyes, perky ears, long snout, pink nose, and whiskers would have given it away. I can't wait to see what happens when you meet Tea. And really, I'm not "Ted." My handle has something or other to do with my given name, but it's nothing that obvious or clear.

But thanks, though.

 

[mubs]

Is that a rat or a mouse? I thought they used mice in labs, not rats.

 

[Adcadet]

It's a rat. Research labs use both mice and rats, it depends on what exactly they're doing.

 

[timwhit]

Most people don't figure that out right away.
You are smarter than the average bear Ted Acda. :thumbright:

I didn't catch that at all. I must be slower than the rest of the children.

Back on topic. I spent a couple summers during school developing applications in Access. They really suck. I think the biggest problems we had was with Reference files. Will DAO 3.6 be compatible with 2003? How about ADO? etc.

If at all a possibility, I would move to using a VB.net frontend and just connect to the Access DB tables. Much less problems with just upgrading the data. It isn't the easiest thing to convert an Access frontend to VB/VB.net, but it is many times more stable and faster, and most of the controls you will miss from Access are available for VB.

P.S. I hate Access.

 

[Will Rickards]

If I were to recommend a migration strategy it would be an MSDE backend (sql server desktop edition) with an access front end. A lot of form stuff is just easier in access than vb or vb.net.

 

[Mercutio]

NOW the VB guys come out..

 

[ddrueding]

What you REALLY need is a LAMP arrangement with a custom web interface

 

[Mercutio]

No doubt.

 

[timwhit]

I wouldn't call myself a VB guy anymore. I haven't done any VB development since 2003. I'm glad about that too.

 

[Mercutio]

I think VB is like a permanent stain on the soul.
Kinda like murder.

 

[Nitsirk]

I think VB is like a permanent stain on the soul.
Kinda like murder.

Well, that seems a bit of a harsh assessment Merc. :???:

But anyway, thanks for all the suggestions guys. I appreciate all the input; however, unfortunately I am "stuck" with Access. It is what I know how to use and what is most effective given that. I am not a full time IT person and like tends to happen in a lot of small companies, I have the IT part of my job by default in a way. I know more than anyone else in my company regarding IT things; therefore, I am the designated IT gal, whether it be building spreadsheets and databases or managing the server stuff. That is sorta the reason why I tend to read a lot of computer forums. I am always absorbing the information and finding a use for a lot of the info I come across.

I do enjoy that aspect of my job; however, with all the other hats I wear, its more about finding effective and efficient solutions to the problems that come up with organizing our jobs without me spending 40 hours a week on the programming and other IT stuff. I also have accounting, risk management and contract duties that demand my time as well. (I work for a construction company). Before I came along the redundancy of data and double entry they had going on make me sick. :hurl: It was one of the most ridiculous systems I had ever seen. Everything was scattered about into numerous excel spreadsheets and there was just data chaos. I at least feel good that I was able to get a lot of that straightened out. It is all a work in progress.

Thanks again for all the input and it was nice meeting you all. I will probably not be a stranger. You all seem like a nice bunch. :sunny:

 

[ddrueding]

Nitsirk,

One of my clients is a construction company (~250 office employees, few thousand in the field) and I've been their soul source of IT since they were 20 guys in a run-down house. It is always a mess. I currently have them using HardDollar, Cheetah, and Primavera Expedition; but would love to know your experiences beyond Access

 

[Will Rickards]

I think VB is like a permanent stain on the soul.
Kinda like murder.

I'm personally offended. I'm a VB guy. And I'm a great programmer. I do VB6 still, VB.net for web stuff, and VBA which used to be mostly word vba but now is vba embedded in our flagship product Elite Enterprise. Now vba in a non-ms app is an adventure. Stuff is half implemented or not implemented at all.

I learned to program in Pascal then moved to C. I like the pascal syntax. It is much easier to read. And believe me, you spend more time reading code than you do writing it. I love javascript but think its biggest downfall is that they chose the c syntax. If they chose a pascal type syntax like vbscript, vbscript wouldn't have existed and the world would be a better place.

 

[Mercutio]

There's always Delphi, Will.

The one that drives me nuts is Foxpro.

Microsoft has a product costs less to buy than Access, yet has a developer-type license (you have to buy Office Enterprise to make redistributable Access Apps). It has 90% of the feature set of SQL server, including comparable speeds (way, way, way faster than Jet), doesn't have as many retarded corruption issues, uses a file format that's been standard and stable since the mid-80s (.DBF, same as dBase) and is well-known for being able to handle "enterprise" workloads...

Yet every two years, Microsoft tells everyone who will listen that they aren't going to ever support it again so for the love of god don't consider using it for anything.

 

[Nitsirk]

Nitsirk,

One of my clients is a construction company (~250 office employees, few thousand in the field) and I've been their soul source of IT since they were 20 guys in a run-down house. It is always a mess. I currently have them using HardDollar, Cheetah, and Primavera Expedition; but would love to know your experiences beyond Access

We are still working out of someones house. Our new office is in the works and if stays on its current schedule should be done by 2009 IMO. They keep saying this summer but at the rate its going, not going to happen.

Anyway, we have about 15 office-type employees (clerical, sales, etc) and anywhere from 30-60 guys in the field depending on workload.

Before I came along they dabbled in Expedition but found it didn't play nice on the server with the accounting software for some reason. Btrieve is the the word that comes to mind. Expedition didn't exactly fit their needs for the customized reports they wanted though.

 

[time]

I think VB is like a permanent stain on the soul.
Kinda like murder.

I support Mercutio. I think Delphi has always been a much better solution for what VB is trying to be.

Nitsirk, Access is a good solution for simple problems. It's infinitely preferable to an Excel-based 'system', but look elsewhere for solutions to larger problems. I used to have a rule of thumb for Access' precursor, Paradox for Windows: if you can't build it in six weeks, it's the wrong tool.

Keep it up, your Access experience could be useful in future jobs.

 

[Will Rickards]

Microsoft has a product costs less to buy than Access, yet has a developer-type license (you have to buy Office Enterprise to make redistributable Access Apps). It has 90% of the feature set of SQL server, including comparable speeds (way, way, way faster than Jet), doesn't have as many retarded corruption issues, uses a file format that's been standard and stable since the mid-80s (.DBF, same as dBase) and is well-known for being able to handle "enterprise" workloads...

Yet every two years, Microsoft tells everyone who will listen that they aren't going to ever support it again so for the love of god don't consider using it for anything.

Yeah it was called MSDE (sql server 7) or sql server desktop edition (sql server 8) or most recently sql server 2005 express edition. It is free. It doesn't have forms. I'm not sure it comes with the sql tools, but you can download winsql for free.

It had/has certain restrictions on the number of concurrent users (used to be 5, I don't know what it is now) and the size of the database (used to be 2GB, I don't know what it is now). And they do release service packs for it just like sql server. As for support, yeah you probably can't call MS but why would you need to?