Convert Access 2 to Access 2000

[Margaret Turner]

Could someone kindly point me in the of direction of how I can convert Access 2 to Access
2000.

My main challenge is the Access 2 code for a front end and back end databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Brendan Reynolds]

There's nothing in this code that needs converting. Given a reference to the
DAO 3.6 object library, and provided that reference appears above any ADO
reference in the list of references, the code works as is in Access 2003.
Here's my test. As you can see, the only things I changed were required to
test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in original
code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Margaret Turner]

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state what the
compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

There's nothing in this code that needs converting. Given a reference to the DAO 3.6
object library, and provided that reference appears above any ADO reference in the list
of references, the code works as is in Access 2003. Here's my test. As you can see, the
only things I changed were required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Could someone kindly point me in the of direction of how I can convert Access 2 to
Access 2000.

My main challenge is the Access 2 code for a front end and back end databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Brendan Reynolds]

It's years since I did a conversion from Access 2, Margaret, and I've
forgotten much of it now. I suspect that the problem is probably not in the
lines you posted, but inside the 'EnableControls' function.

The most common problems I remember were differences between DAO 2.x and DAO
3.x syntax. BeginTrans, CommitTrans and RollBack are now methods of the DAO
Workspace object rather than the Database object, and you can not refer to
fields of a recordset using the '!', e.g. rst!SomeField, you have to change
it to rst.SomeField (or rst("SomeField")). There was also a third one,
something to do with QueryDefs, but I seem to have some kind of mental block
about that one - every time this question comes up I forget it again. Any
one else?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state
what the compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is
objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

There's nothing in this code that needs converting. Given a reference to
the DAO 3.6 object library, and provided that reference appears above any
ADO reference in the list of references, the code works as is in Access
2003. Here's my test. As you can see, the only things I changed were
required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in
original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Could someone kindly point me in the of direction of how I can convert
Access 2 to Access 2000.

My main challenge is the Access 2 code for a front end and back end
databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[david epsom dot com dot au]

Any one else?

Set qdf = db.OpenQueryDef(whatever)
change to
Set qdf = db.QueryDefs(whatever)


You see that it can be done with a simple find and replace.

Open a code module and select "Compile" from the Debug menu.

You may have to remove the "Utility" reference from Tools | References,
and if your Ini file referenced any library files, that is where they
go now.

Also, Decl statements that reference DLL's will need to be re-written.

(david)

It's years since I did a conversion from Access 2, Margaret, and I've
forgotten much of it now. I suspect that the problem is probably not in the
lines you posted, but inside the 'EnableControls' function.

The most common problems I remember were differences between DAO 2.x and DAO
3.x syntax. BeginTrans, CommitTrans and RollBack are now methods of the DAO
Workspace object rather than the Database object, and you can not refer to
fields of a recordset using the '!', e.g. rst!SomeField, you have to change
it to rst.SomeField (or rst("SomeField")). There was also a third one,
something to do with QueryDefs, but I seem to have some kind of mental block
about that one - every time this question comes up I forget it again. Any
one else?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state
what the compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is
objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

There's nothing in this code that needs converting. Given a reference to
the DAO 3.6 object library, and provided that reference appears above any
ADO reference in the list of references, the code works as is in Access
2003. Here's my test. As you can see, the only things I changed were
required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in
original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Could someone kindly point me in the of direction of how I can convert
Access 2 to Access 2000.

My main challenge is the Access 2 code for a front end and back end
databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Chris Mills]

Margaret,
As far as I know (and I've done it Many Times though via A97), there should be
no particular difficulty (more than normal!) in converting from A2.0 to A2k.

Of course, some of the coding syntax/standards have changed since A2.0. So far
as I know, all the Old Syntax is still supported as "legacy code" in A2k.

The only problem I specifically recall (in auto-conversion), is that some code
lines may now require dots where you didn't need that in A2.0 (eg DoCommand or
similar). But these coding issues are not hard to find because compiling an
app in A2k should immediately throw up the Obvious Errors.

All constants in A2.0 were in capitals. All constants in A2k are not. Whilst
you should therefore upgrade your code, I believe you can do so "at your
leisure".

If you try to compile it in A2k, it should throw you into the exact line, if
there is a compile error.

A2k was a bit of a "lemon". It can suffer from at least slowness, but if
that's your prob then post separately. Make sure you have the latest patch
(SP2? I'm happy enough with SP1)

I had major problems converting from A2.0 to A97. The users revolted. They got
together in arms! It was all due to speed-reduction, which of course I could
do something about. One advantage of not having an optimised app, is that I
could then optimise it when the users revolted! They thought I was neat!

However, you DO need to test every aspect of a conversion. I believe I charged
about $5000, which in $NZ is only about half $US.

REFERENCES is a major consideration. Don't just trust MS-Access, control it.
Whilst MS changed the default from DAO to ADO, DAO works just fine in most
circumstances. Any code module, Tools, References.

Why A2k at this time? A2k2 is reported to be more stable. A2k3 I'm still wary
of... A2k gave me major probs but I think I've got over most of them...

Chris

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state what the
compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

There's nothing in this code that needs converting. Given a reference to the DAO 3.6
object library, and provided that reference appears above any ADO reference in the list
of references, the code works as is in Access 2003. Here's my test. As you can see, the
only things I changed were required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Could someone kindly point me in the of direction of how I can convert Access 2 to
Access 2000.

My main challenge is the Access 2 code for a front end and back end databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Brendan Reynolds]

DAO code has to be converted. You could get away without converting it when
moving from Access 2 to Access 97 by using the DAO 2.x/3.x Compatibility
Library, but you can't do that when moving to Access 2000 or later, because
there is no DAO 2.x/3.6 Compatibility Library.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Margaret,
As far as I know (and I've done it Many Times though via A97), there
should be
no particular difficulty (more than normal!) in converting from A2.0 to
A2k.

Of course, some of the coding syntax/standards have changed since A2.0. So
far
as I know, all the Old Syntax is still supported as "legacy code" in A2k.

The only problem I specifically recall (in auto-conversion), is that some
code
lines may now require dots where you didn't need that in A2.0 (eg
DoCommand or
similar). But these coding issues are not hard to find because compiling
an
app in A2k should immediately throw up the Obvious Errors.

All constants in A2.0 were in capitals. All constants in A2k are not.
Whilst
you should therefore upgrade your code, I believe you can do so "at your
leisure".

If you try to compile it in A2k, it should throw you into the exact line,
if
there is a compile error.

A2k was a bit of a "lemon". It can suffer from at least slowness, but if
that's your prob then post separately. Make sure you have the latest patch
(SP2? I'm happy enough with SP1)

I had major problems converting from A2.0 to A97. The users revolted. They
got
together in arms! It was all due to speed-reduction, which of course I
could
do something about. One advantage of not having an optimised app, is that
I
could then optimise it when the users revolted! They thought I was neat!

However, you DO need to test every aspect of a conversion. I believe I
charged
about $5000, which in $NZ is only about half $US.

REFERENCES is a major consideration. Don't just trust MS-Access, control
it.
Whilst MS changed the default from DAO to ADO, DAO works just fine in most
circumstances. Any code module, Tools, References.

Why A2k at this time? A2k2 is reported to be more stable. A2k3 I'm still
wary
of... A2k gave me major probs but I think I've got over most of them...

Chris

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly
state what the
compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

There's nothing in this code that needs converting. Given a reference
to the DAO 3.6
object library, and provided that reference appears above any ADO reference in the list
of references, the code works as is in Access 2003. Here's my test. As
you can see, the
only things I changed were required to test the code without the
complete database ...

'passing EnterSurname as argument, assuming it is form control in
original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Could someone kindly point me in the of direction of how I can
convert Access 2 to
Access 2000.

My main challenge is the Access 2 code for a front end and back end databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Brendan Reynolds]

That's the one, thanks David.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Any one else?

Set qdf = db.OpenQueryDef(whatever)
change to
Set qdf = db.QueryDefs(whatever)


You see that it can be done with a simple find and replace.

Open a code module and select "Compile" from the Debug menu.

You may have to remove the "Utility" reference from Tools | References,
and if your Ini file referenced any library files, that is where they
go now.

Also, Decl statements that reference DLL's will need to be re-written.

(david)

It's years since I did a conversion from Access 2, Margaret, and I've
forgotten much of it now. I suspect that the problem is probably not in the
lines you posted, but inside the 'EnableControls' function.

The most common problems I remember were differences between DAO 2.x and DAO
3.x syntax. BeginTrans, CommitTrans and RollBack are now methods of the DAO
Workspace object rather than the Database object, and you can not refer
to
fields of a recordset using the '!', e.g. rst!SomeField, you have to change
it to rst.SomeField (or rst("SomeField")). There was also a third one,
something to do with QueryDefs, but I seem to have some kind of mental block
about that one - every time this question comes up I forget it again. Any
one else?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state
what the compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is
objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
There's nothing in this code that needs converting. Given a reference to
the DAO 3.6 object library, and provided that reference appears above any
ADO reference in the list of references, the code works as is in
Access
2003. Here's my test. As you can see, the only things I changed were
required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in
original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path &
"\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Could someone kindly point me in the of direction of how I can convert
Access 2 to Access 2000.

My main challenge is the Access 2 code for a front end and back end
databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Chris Mills]

Um...Access2000 has DAO 3.6... as does Access XP...

What conversion? As I recall there may have been some tightening of dots and
bangs.

Chris

 

[Brendan Reynolds]

Of course they do - as does Access 2003. Which is why the old DAO 2.x code
needs to be converted. See posts in this thread by myself and David.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Chris Mills]

Is there a way that I can find what in all the code the conversion is
objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

EnableControls appears to be a user-defined function (ie written within the
app, not pre-defined within Access). Fortunately, since A2.0, global code
search is available. Any code module, Edit, Find, and select to search
"Current Project". Can you find a function named "EnableControls"?

As to why that line won't compile, there's insufficient info to say.

Chris

 

[Chris Mills]

See posts in this thread by myself and David.Thanks Brendan (and David). I did not mean to argue too diligently with you
guys. And certainly not to accost you.

It just seems to me, that Recordsets are the most common DAO usage, and that
has barely changed. I am sure some of the more in-depth db manipulation has
changed, in David's case possibly QueryDefs. I thought opening a querydef
involved some quite advanced database manipulation, which Margaret did not say
that she does.

Of course, (for Margaret's benefit), A2k changed the default Reference from
DAO to ADO. But it can easily be put back to DAO in the references. So far as
I know, DAO was marked by MS for obsolescence, but it has by no means become
so.

The most serious concern, in db manipulation via recordsets, is merely some
tightening up of dots(.) vs bangs(!) (as you pointed out). When I pointed this
out to a programming colleague many years ago, he just said I should have been
using dots and bangs correctly in the first place! (even though A2.0 allows
one to be "slack"). I love that colleague!

Of course, like yourselves, I've forgotten many of the hassles I had
converting from A2.0, like converting any 16-bit references as David pointed
out.

Cheers
Chris

 

[Margaret Turner]

Thanks David.

Set qdf = db.OpenQueryDef(whatever)
change to
Set qdf = db.QueryDefs(whatever)


You see that it can be done with a simple find and replace.


Open a code module and select "Compile" from the Debug menu.

This is one of the objections

Function EnableControls(WhichSection As String, State As Integer) As Integer

' Enable or disable controls in specified section of form.

Dim MyForm As Form
Dim MyControl As Control
Dim i As Integer, SelectedSection As Integer

' Use active form. If no form is active, exit
' function without displaying error message.
On Error Resume Next
Set MyForm = Screen.ActiveForm
If Err Then
EnableControls = False
On Error GoTo 0
Exit Function
End If

' Define valid values for WhichSection.
Select Case UCase$(WhichSection)
Case "FORM HEADER"
SelectedSection = 1
Case "PAGE HEADER"
SelectedSection = 3
Case "DETAIL"
SelectedSection = 0
Case "PAGE FOOTER"
SelectedSection = 4
Case "FORM FOOTER"
SelectedSection = 2
Case Else
MsgBox "Invalid argument", , "EnableControls"
EnableControls = False
Exit Function
End Select

' Set state for all controls in specified section.
For i = 0 To MyForm.Count - 1
Set MyControl = MyForm(i)
If MyControl.Section = SelectedSection Then
On Error Resume Next
MyControl.Enabled = State
On Error GoTo 0
End If
Next i

EnableControls = True

End Function

I have not used Access since the days of Acces2.

How do I compile only the EnableControls module?

You may have to remove the "Utility" reference from Tools | References,
and if your Ini file referenced any library files, that is where they
go now.

Also, Decl statements that reference DLL's will need to be re-written.

No dll's were used.

Thanks again David.

Margaret

It's years since I did a conversion from Access 2, Margaret, and I've

forgotten much of it now. I suspect that the problem is probably not in the
lines you posted, but inside the 'EnableControls' function.

The most common problems I remember were differences between DAO 2.x and DAO
3.x syntax. BeginTrans, CommitTrans and RollBack are now methods of the DAO
Workspace object rather than the Database object, and you can not refer to
fields of a recordset using the '!', e.g. rst!SomeField, you have to change
it to rst.SomeField (or rst("SomeField")). There was also a third one,
something to do with QueryDefs, but I seem to have some kind of mental block
about that one - every time this question comes up I forget it again. Any
one else?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state
what the compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is
objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
There's nothing in this code that needs converting. Given a reference to
the DAO 3.6 object library, and provided that reference appears above any
ADO reference in the list of references, the code works as is in Access
2003. Here's my test. As you can see, the only things I changed were
required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in
original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Could someone kindly point me in the of direction of how I can convert
Access 2 to Access 2000.

My main challenge is the Access 2 code for a front end and back end
databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Margaret Turner]

"Chris Mills" wrote in message

Margaret,
As far as I know (and I've done it Many Times though via A97), there should be
no particular difficulty (more than normal!) in converting from A2.0 to A2k.

Chris are you suggesting I convert first to A97?

Of course, some of the coding syntax/standards have changed since A2.0. So far
as I know, all the Old Syntax is still supported as "legacy code" in A2k.

The only problem I specifically recall (in auto-conversion), is that some code
lines may now require dots where you didn't need that in A2.0 (eg DoCommand or
similar).

I can fix that OK. Thanks.

But these coding issues are not hard to find because compiling an
app in A2k should immediately throw up the Obvious Errors.

All constants in A2.0 were in capitals. All constants in A2k are not. Whilst
you should therefore upgrade your code, I believe you can do so "at your
leisure".

If you try to compile it in A2k, it should throw you into the exact line, if
there is a compile error.

My last posting mentioned a module that I do not know how to compile as Debug | Compile
will try to compile the lot.

I thought if I got the macros and modules "clean" then compile all?

Does that sound like a reasonable strategy?

A2k was a bit of a "lemon". It can suffer from at least slowness, but if
that's your prob then post separately. Make sure you have the latest patch
(SP2? I'm happy enough with SP1)

Chris, it's just that I have A2k on my system.
Hoepfully slowness will not be a problem as it is a single user - use the application once
a year for about a month.

I had major problems converting from A2.0 to A97. The users revolted. They got
together in arms! It was all due to speed-reduction, which of course I could
do something about. One advantage of not having an optimised app, is that I
could then optimise it when the users revolted! They thought I was neat!

However, you DO need to test every aspect of a conversion. I believe I charged
about $5000, which in $NZ is only about half $US.

I'm an Aussie.

I am doing this volunatry work as
1. I wrote the application years ago for a sporting organisation to run their annual
tournament
2. They are going to purchase a laptop so I need to upgrade my code so it runs on their
Access version.
Until now I have the luck that the user had A2.0.
3. I'll use this as a 'fun' exercise to familiarise myself with newer versions of access.

REFERENCES is a major consideration. Don't just trust MS-Access, control it.
Whilst MS changed the default from DAO to ADO, DAO works just fine in most
circumstances. Any code module, Tools, References.

Why A2k at this time?

Just that's the version I have on my PC. $ gets in my way to upgrade my version of MS
Office at the moment.

A2k2 is reported to be more stable. A2k3 I'm still wary
of... A2k gave me major probs but I think I've got over most of them...

Thanks Chris

Margaret

Chris

Thanks Brendan for your prompt reply.

The automatic conversion from Access2 to Access 2000 did not clearly state what the
compile errors were.
There are more than 200 forms, mostly with code attached.
I had assumed that the code I supplied was at fault.

Is there a way that I can find what in all the code the conversion is objecting to?

For example, one piece of code won't compile
Dim Tmp As Variant
Tmp = EnableControls("Detail", True)

It would seem that I have a huge conversion exercise to go through

Thanks again,

Margaret

There's nothing in this code that needs converting. Given a reference to the DAO 3.6
object library, and provided that reference appears above any ADO reference in the list
of references, the code works as is in Access 2003. Here's my test. As you can see, the
only things I changed were required to test the code without the complete database ...

'passing EnterSurname as argument, assuming it is form control in original code
Public Sub TestTheCode(ByVal EnterSurname As String)

Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase(CurrentProject.Path & "\northwind.mdb")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Categories")
Debug.Print MyPlayers.Fields(0)
MyPlayers.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Could someone kindly point me in the of direction of how I can convert Access 2 to
Access 2000.

My main challenge is the Access 2 code for a front end and back end databases.

The "program" database has the following code:
Dim ThisPlayer As Long
ThisPlayer = Val([EnterSurname])
Dim MyWs As Workspace
Dim MyDb As Database
Set MyWs = DBEngine(0)
Set MyDb = MyWs.OpenDatabase("C:\FSC\TOURNDAT.MDB")
Dim MyPlayers As Recordset
Set MyPlayers = MyDb.OpenRecordset("Players")

Any help greatly appreciated.

Thanks,

Margaret

 

[Margaret Turner]

"Chris Mills" wrote in message

Thanks Brendan (and David). I did not mean to argue too diligently with you
guys. And certainly not to accost you.

It just seems to me, that Recordsets are the most common DAO usage, and that
has barely changed. I am sure some of the more in-depth db manipulation has
changed, in David's case possibly QueryDefs. I thought opening a querydef
involved some quite advanced database manipulation, which Margaret did not say
that she does.

Yes some of the forms run querydefs.

Of course, (for Margaret's benefit), A2k changed the default Reference from
DAO to ADO. But it can easily be put back to DAO in the references. So far as
I know, DAO was marked by MS for obsolescence, but it has by no means become
so.

The most serious concern, in db manipulation via recordsets, is merely some
tightening up of dots(.) vs bangs(!) (as you pointed out). When I pointed this
out to a programming colleague many years ago, he just said I should have been
using dots and bangs correctly in the first place! (even though A2.0 allows
one to be "slack"). I love that colleague!

Thanks Chris

Margaret

 

[david epsom dot com dot au]

I don't see anything wrong with that, but in A2K, the module names must be
different from the function names.

Check that the Modules are all named differently from the Functions and
Subroutines, and that none of the References are missing.

(david)

 

[Chris Mills]

Chris are you suggesting I convert first to A97?The other responders are all known and highly respected by me.
No I'm not, that's just usually the way I did it.
And if I converted A2.0~A97~A2k~A2k2, the damn ADO recordset code was all
precisely the same. Well apart from a bit of confusion about dots vs bangs, or
in DoCommand or menu manipulations some confusion about spaces vs dots or
specifying the correct menu version. If you are opening a querydef I will
kow-tow to David, except I don't know why your app would open such, unless you
wish to "manipulate" database objects beyond normal (ie "simple").

My last posting mentioned a module that I do not know how to compile as Debug | Compile
will try to compile the lot.

Well certainly it only compiles the lot. But I thought it would throw you into
the first line in error or something.

It is well-known that MS-Access errors can be "approximate". And if there are
more than a few errors then the compiler might get completely "lost" (no
disrespect to Microsof).

Take a copy of your db, and start dotting out errant lines until you get it to
compile. Just for a test mind. If you come across more than a dozen errors,
then you may be chasing your tail (for instance, if a reference to DAO is
missing, then ALL DAO lines would error)

Please don't laugh, but I have apps with hundreds of forms. On desperate
occasions I have deleted nearly all of them until I get the damn thing to
compile.

Both A97 and A2k (etc) are subject to subtle db corruptions. A2.0 is too but
not so bad. The A2k converter is particularly prone. Therefore, do a compact
of A2.0 before trying to convert (if A97 or greater, do a
decompile/compile/compact). As soon as you've converted to A2k, immediately do
a decompile/compile/compact in A2k. /Decompile is an undocumented command line
switch (though it is documented at www.trigeminal.com). If you see no result,
then your probs are worse than that.

I thought if I got the macros and modules "clean" then compile all?

Does that sound like a reasonable strategy?

Ah, macros? I don't know anything about macros beyond using them to startup
and shutdown. Macros are not part of a "code compile". Therefore, they might
affect how your overall app runs, but not why your code can't compile or run.

Chris, it's just that I have A2k on my system.

A2k should be fine if SP'd. I had a lot of initial troubles but you'll be
pleased to know my mother helped me get over most of them ;-)

Contributors to these ng's consistently say that A2k2 was much more stable.
Although I have A2k2 (A-XP) installed on a test PC, I have not had a major
reason to change from distributing A2k. (meaning, the particular problems I
have were STILL present in A2k2)

Hoepfully slowness will not be a problem as it is a single user - use the application once
a year for about a month.

I was not referring to general slowness (all later and bigger software runs
slower, you will especially notice that if converting from A2.0) I was
referring to complete lock-ups for 10 or 20secs. Any responders who have not
experienced this, then please don't respond. Some besides me have noticed it.

I'm an Aussie.

Aussies should refer to David.
One Australia sunk didn't it? (but then we didn't do much better ourselves in
defending the last Americas Cup)

I am doing this volunatry work as

What is wrong with A2.0? Admittedly I only have one customer still using A2.0,
but until it falls over they can stay there! The best reason I can think for
converting, is that it's hard nowadays to find programmers familiar with A2.0.
Or, on later operating systems, it might just fall over gawd knows why.

Chris

 

[Brendan Reynolds]

No problem, Chris, your question helped to expand and clarify the
discussion.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.