Using Access 97 with Project

[Kahuna]

Hi folks

Many moons ago I used A97 to control a linked database which was also used
by Project 98 to build a plan.

That wasn't very satisfactory even then as the link code seemed flaky!
(probably my code that is <lol>)

I need to interface with Project again and still need to use A97 as the app,
though the version of Project could I guess be anything from P98 through to
the latest version.

Is there any type of tutorial or guidance you guys can offer to get me
started on this path? For example,

Can I use the A97 Jet 3.51 database to link from a current version of
Project, or could I develop a specific database in, say Jet 4 for Project to
link to, and then use OLEDB to link to it from A97? Does the latest Project
use VBA as the programming interface?

Comments Appreciated

 

[Earl Lewis]

Kahuna,

Basically, you take the same approach as you did before, save the project files as Access databases. The problem with this approach is that Project wants to create one Access database for every project. I suppose you could create a "Project Open" routine that asks the user to select which mdb to open when you start up your application.

This architecture would be a very good reason to use the MSAccess "split" database design where you create the user interface elements (queries, forms, reports) as one mdb and all the data files are kept in separate mdb's and pointed to as required by the user.

If you have any more questions about this reply let me know or check out the ms.public.acess newsgroups.

Earl
Hi folks

Many moons ago I used A97 to control a linked database which was also used
by Project 98 to build a plan.

That wasn't very satisfactory even then as the link code seemed flaky!
(probably my code that is <lol>)

I need to interface with Project again and still need to use A97 as the app,
though the version of Project could I guess be anything from P98 through to
the latest version.

Is there any type of tutorial or guidance you guys can offer to get me
started on this path? For example,

Can I use the A97 Jet 3.51 database to link from a current version of
Project, or could I develop a specific database in, say Jet 4 for Project to
link to, and then use OLEDB to link to it from A97? Does the latest Project
use VBA as the programming interface?

Comments Appreciated

 

[Earl Lewis]

Kahuna,

Forgot one important thing. You'll probably have to add a step to your process that converts the database created by project to Access 97 format after it is generated. MSPorject Pro 2002 and 2003 create Access 2000 format mdbs and they won't open in Access 97. This can be automated in Access 2000, 2002 or 2003 if you like. An Access one-liner would be something like this:

Public Sub ConvertTo97(strOrigFile As String, strAccess97File As String)
Application.ConvertAccessProject strOrigFile, strAccess97File, acFileFormatAccess97
End Sub

Earl
Kahuna,

Basically, you take the same approach as you did before, save the project files as Access databases. The problem with this approach is that Project wants to create one Access database for every project. I suppose you could create a "Project Open" routine that asks the user to select which mdb to open when you start up your application.

This architecture would be a very good reason to use the MSAccess "split" database design where you create the user interface elements (queries, forms, reports) as one mdb and all the data files are kept in separate mdb's and pointed to as required by the user.

If you have any more questions about this reply let me know or check out the ms.public.acess newsgroups.

Earl
Hi folks

Many moons ago I used A97 to control a linked database which was also used
by Project 98 to build a plan.

That wasn't very satisfactory even then as the link code seemed flaky!
(probably my code that is <lol>)

I need to interface with Project again and still need to use A97 as the app,
though the version of Project could I guess be anything from P98 through to
the latest version.

Is there any type of tutorial or guidance you guys can offer to get me
started on this path? For example,

Can I use the A97 Jet 3.51 database to link from a current version of
Project, or could I develop a specific database in, say Jet 4 for Project to
link to, and then use OLEDB to link to it from A97? Does the latest Project
use VBA as the programming interface?

Comments Appreciated

 

[Kahuna]

Thanks for that feedback Earl.

Couple of questions if you've time to respond:

1. Can the MSP(say)2002 then read the A97 database after its converted? I
ask this because what I really need to do is write the data to the tables
from my A97 app and have them read by MSP. There may be some interaction
also where the user changes the content (start dates etc) in MSP and that
needs to be readable again by my app.

2. Is it possible to keep the Db created with MSP in A2k format and link to
those table through A97 using an ODBC driver?

I think these are at the heart of my challenge Earl, I appreciate your
input.

As an aside - my app runs in RunTime, and is a split Back/Front End but with
numerous other library / support Db's. It's my intention to make one of
these specific to the Scheduling data for use in MSP. Also A2k or any other
Access version may not be installed on the users box.

 

[Earl Lewis]

See comments in-line.

Earl
Thanks for that feedback Earl.

Couple of questions if you've time to respond:

1. Can the MSP(say)2002 then read the A97 database after its converted?

Project 2003 seems to be fine with READING an Access 97 mdb. Don't know how it will react to writing.

I ask this because what I really need to do is write the data to the tables
from my A97 app and have them read by MSP. There may be some interaction
also where the user changes the content (start dates etc) in MSP and that
needs to be readable again by my app.

2. Is it possible to keep the Db created with MSP in A2k format and link to
those table through A97 using an ODBC driver?

Don't know if this is possible. I tend to think not.

I think these are at the heart of my challenge Earl, I appreciate your
input.

As an aside - my app runs in RunTime, and is a split Back/Front End but with
numerous other library / support Db's. It's my intention to make one of
these specific to the Scheduling data for use in MSP. Also A2k or any other
Access version may not be installed on the users box.

You'd only need Access 2000 or greater to conver the initial mdb from A2k format to A97. Then the clients can be all A97 from then on out.

 

[Kahuna]

Thanks Earl - my experience with MSP2k (and A97) was that it attempted to
convert the Db back to a later format.! (I stress it was back in 2001 and a
couple of hours playing not exactly research <lol>) It seems that rather
than risk complications because of different versions of MSP on the users
box, automation might be the right way to go anyway!!

I'll give it some time and see where we go.

Any-one else with experience here would be welcome to comment.

 

[JackD]

More comments in line

--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

See comments in-line.

Earl

Thanks for that feedback Earl.

Couple of questions if you've time to respond:


Project 2003 seems to be fine with READING an Access 97 mdb. Don't know

how it will react to writing.

With Proj 2k if the existing db is 97 it can write to it. It can not create
an access 97 database though. I think this should be the same for proj 2003,
but can't be sure. The transition from access 97 to 2000 was very messy. I
recall that in some cases if a 97 db was opened in Access 2000 it would be
irrevocably converted and 97 users could not open it again.

I think you want to get away from A97 if you can.

You'd only need Access 2000 or greater to conver the initial mdb from A2k

format to A97. Then the clients can be all A97 from then on out.

 

[Kahuna]

Thanks for the feedback Jack.

Fraid A97 is still he most stable Access available and DAO is our preferred
data model. So no chance of changing just yet. Need instead to find a method
of Project Interaction that works across the board (A97 / MSP 2k - 04).

Cheers

 

[Earl Lewis]

Kahuna,

I'd have to take issue with the statement about stability of A97 vs. later versions. I agree that A97 was a huge improvement in stability over prior versions but all later releases of Access were improvements over A97 as well.

What kind of stability concerns do you have about later versions? I've done plenty of fairly intensive development projects in A2k and A2k2 and had no "stability" issues that I couldn't resolve.

If stability is your only reason for sticking with A97 on this project I think you should re-evaluate - and save yourself lots of database version compatibilty problems in the process. ;>)

Earl
Thanks for the feedback Jack.

Fraid A97 is still he most stable Access available and DAO is our preferred
data model. So no chance of changing just yet. Need instead to find a method
of Project Interaction that works across the board (A97 / MSP 2k - 04).

Cheers

 

[Kahuna]

Thanks Earl, and I know what you mean. A97 was the last really stable and
fully de-bugged version of Access (IMHO), debateable I know. But reality is,
my entire architecture is based on A97 and until it's proven otherwise
deployment with any other version is less than stress free! (I take it as
read that you have solved your problems with other versions). I deploy to
Shell, BP, Conoco, Elf, Exxon and a few other majors and they will sanction
nothing but A97 on their systems (at least in UK AFAIK) they have the real
experience of clearing up after an install that went wrong. (and I was in
the teams at that time too!).

If I need to move design strategy it will likely be to another database
platform rather than another version of Access. But that's not in the 2005
plan for sure.

Cheers

 

[Rod Gill]

Hi,

Create a new blank database in Access 97.
In Project save as and select .mdb file format, then select the new blank
db.
Enter a project title and Project should create the required db structure
all in the 97 format. By default it will save in the corresponding office
format, so Project 2003 probably (though I haven't tested) saves in
2002/2003 format.

No problems from there on in.

Have to say I use Access extensively and have found 2003 great, better than
97 though I hate the new security model!! Development productivity I find in
Access 2000 to be 20% faster than 97 and 2003 another 20% at least faster
than 2000.

Provided you use split databases and latest Office updates 2000, 2002 and
2003 are very stable. I've even got them handling 2000 simultaneous users by
caching offline! Great web site is www.mvps.org/access (the /word is
brilliant and the /project is also useful).

 

[Kahuna]

Thanks Rod, take your point on developer productivity, but still, most of my
clients freak when they know our apps are in Access. Makes them feel better
when they hear we use no external ActiveX, difficult API calls or
third-party addins. All automation is by late binding too. Also use SageKey
as the install runtime and to secure the A97 registry settings.

Using these tools we can install and not effect any other versions of Access
on the machines - same for uninstall. Found other versions of Access cause
problems in one or more of these areas. Have some clients running NT 4 too
(or earlier!!).

As for the creation of the mdb for use by MSP - that's the route I took in
the past. Now of course since MSP saves back in another version of the mdb
we cant read it in A97 - so back to square one!

I think automation is the route to take but as yet unsure if I can still use
DAO with the latest versions of MSP.

Cheers for the support Rod.