Connect to Acess Database

[oceanboy]

Hi all,

I am not sure this is the place to ask the question but I have an application that is written in VB6 that connects to Access MDB. However, the MDB was created using Access 97 and we are now using 2003. I can't edit that MDB due to different version. If I upgrade it to Access2003 MDB, the application (VB6) won't work.

So, is it the data connection used in the application does not fit to Access2003 MDB? What is the best way to solve this?

Thanks,
OB

[techgnome]

Hmmm..... depends... Are you using ADO... or DAO? IF you are using DAO, then yeah, it's possible that changes would be a problem.... if you are using ADO... then it shouldn't matter....

-tg

[oceanboy]

Yes it is DAO unfortunately. Is it something to do about the version of the DAO object library that being used?

[techgnome]

I think so... You may want to check msdn.microsoft.com and see if you can find out what the latest version of DAO is available... but be warned it's not going to be a supported item... so don't expect much in the way of help or documentation from them.... it's possible you may have to go to a third party to find it.

-tg

[oceanboy]

go to a third party might not be an option because my boss would like to move the database from Access to SQL server. I just thought that upgrading Access97 to Access2003 temporarily. I don't know whether it is going to work (moving to SQL) as it is very likely that i will be the only person working on it. :S

for now, i will be trying if changing the object library will work and find out abit more about it. =)

[techgnome]

nonono.... that's not what I meant.... I meant that you might have to get the latest DAO from someone other than Microsoft, because it isn't a technology MS supports any more.... so it's possible that some one else may have it available for d/l.

-tg

[oceanboy]

sorry, i misunderstood you. hehehe! no worries mate! thanks for your explanation.

[Hack]

go to a third party might not be an option because my boss would like to move the database from Access to SQL server. I just thought that upgrading Access97 to Access2003 temporarily. I don't know whether it is going to work (moving to SQL) as it is very likely that i will be the only person working on it. :S

for now, i will be trying if changing the object library will work and find out abit more about it. =)

If it is a one time shot you could create a DTS package (SSIS in 2005) that would do this for you.

[dilettante]

The "latest DAO" is probably available from Microsoft yet. I'd guess it's 3.60 but I don't track these things. DAO 3.60 can use Jet 4.0 MDBs, but...

It might not help much:

... Access 95 and Access 97, and Visual Basic 4.0, 5.0, and 6.0 applications that use the DAO Data control, are all hard-coded to use the Jet 3.x database format will not be able read Jet 4.0 data.

INFO: Issues Migrating from DAO/Jet to ADO/Jet

However:

I was able to have it create databases in Access 2000 by changing
"dbVersion30" to "dbVersion40".

Ugh! "In Access 2000." Why do people insist on using incorrect terminology and just confusing themselves? The whole thread stems from this sort of misrepresentation. At least the guy trying to help over there did make an effort to try to correct the OP's incorrect language.

How to get VB app to generate Access 2003 databases with DAO?


But the point is, using DAO 3.60 it might be possible to handle both old Jet 3.x MDBs and Jet 4.0 MDBs.

[Hack]

As techgnome states

I meant that you might have to get the latest DAO from someone other than Microsoft, because it isn't a technology MS supports any more....

There is no such thing as the "latest DAO" in terms of Microsoft, and I don't know of anyone else that makes it.

Make the switch to ADO, move to SQL Server, and just do some rewrites (probably not a whole lot - depends on the app)