Migrating away from MS-Access

"John" <no****@nospam.comwrote in
news:ff********@news5.newsguy.com:

then look at migrating the front end to VB.net,
that'll be a while off tho...hopefully I can get management to
hire another to worry about VB.net...which has already been in
production for a year...if they hadnt have started that itd be
much easier and for all I know could be a waste of resources going
that path if I can get access going right...

I think migrating the front end to VB.NET is a bloody stupid idea. I
see no benefit in it at all -- just a huge additional cost (maybe
3-5 times as much as fixing the Access front end to work with SQL
Server). Does your employer have a solid justification for wanting
to use VB.NET? You certainly haven't mentioned it if they have.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 16 '07 #11

lyle

On Oct 16, 11:32 am, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:

"John" <nos...@nospam.comwrote innews:ff********@news5.newsguy.com:

then look at migrating the front end to VB.net,
that'll be a while off tho...hopefully I can get management to
hire another to worry about VB.net...which has already been in
production for a year...if they hadnt have started that itd be
much easier and for all I know could be a waste of resources going
that path if I can get access going right...

I think migrating the front end to VB.NET is a bloody stupid idea. I
see no benefit in it at all -- just a huge additional cost (maybe
3-5 times as much as fixing the Access front end to work with SQL
Server). Does your employer have a solid justification for wanting
to use VB.NET? You certainly haven't mentioned it if they have.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 16 '07 #12

John

Well the previous programmer blamed access for all his woes...that I believe
was the primary reason for going to VB.net...The other primary reason was
security (Which I know very little about, he may have been right, he may
have been wrong...)...

I think there are possibly a few other reasons such as wanting to operate a
cut down version on mobile devices

The few reasons I think are probably valid;
- Security, we do have some fairly complicated security requirements, which
im still learning about.
- Speed running over terminal services (We have remote locations that im
told run very slowly over terminal services, I hope a webservice is faster)
- The user interface is nicer, easier to control, with access I find that
there are lots of things you *can* do, but probably shouldnt...I know this
is weak :)
- Just recently, 2 weeks ago, I got informed we may have a sudden doubling
of users, then doubling again....yeah umm great :) So I think vb.net is
safer.
- Multideveloper is easier if that ever happens, although this is probably
moot because of the twice as long thing :)

But yeah in all honesty id much rather be working in access, its faster,
easier to do things, half the reasons for vb.net are flimsy at best, so it
will warrant some more thought and discussion with the head honcho...but I
doubt very much we will change course however...a portion of the users are
using the dotnet version and once that happens theres no going
back....they've seen all the developer express pretty pretty stuff....it
LOOKS great....it must be better...

John

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"John" <no****@nospam.comwrote in
news:ff********@news5.newsguy.com:

>then look at migrating the front end to VB.net,
that'll be a while off tho...hopefully I can get management to
hire another to worry about VB.net...which has already been in
production for a year...if they hadnt have started that itd be
much easier and for all I know could be a waste of resources going
that path if I can get access going right...

I think migrating the front end to VB.NET is a bloody stupid idea. I
see no benefit in it at all -- just a huge additional cost (maybe
3-5 times as much as fixing the Access front end to work with SQL
Server). Does your employer have a solid justification for wanting
to use VB.NET? You certainly haven't mentioned it if they have.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 16 '07 #13

"John" <no****@nospam.comwrote in
news:ff********@news1.newsguy.com:

Well the previous programmer blamed access for all his woes...that
I believe was the primary reason for going to VB.net...The other
primary reason was security (Which I know very little about, he
may have been right, he may have been wrong...)...

Can your employers explain exactly how VB.NET solves either of these
problems?

Security is a *back-end* issue, not a front-end one, so that's
irrelevant.

The rest sounds to me like the carpenter blaming his tools. New
tools won't raise the skill level of the carpenter.

I think there are possibly a few other reasons such as wanting to
operate a cut down version on mobile devices

VB.NET enables that, but I don't believe it's an *easy* thing.

And, of course, there's a Windows Terminal Server client for Windows
Mobile.

The few reasons I think are probably valid;
- Security, we do have some fairly complicated security
requirements, which im still learning about.

Has nothing to do with Access.

- Speed running over terminal services (We have remote locations
that im told run very slowly over terminal services, I hope a
webservice is faster)

Eh? That's a function of:

1. bandwidth

2. server provisioning.

If an Access app runs slow on Terminal Server, it probably runs
slowly on the desktop.

In other words, irrelevant to the front-end platform, and entirely
due to problems elsewhere.

- The user interface is nicer, easier to control, with access I
find that there are lots of things you *can* do, but probably
shouldnt...I know this is weak :)

Again, this is about the programmer's skill level, not about what
the platform can or can't do. I can't think of anything essential to
a database application that an Access app can't do. Care to cite an
example?

I will say that it certainly is true that you often must avoid the
default approaches suggested by the Access sample files (such as
binding a form to a table).

- Just recently, 2 weeks ago, I got informed we may have a sudden
doubling of users, then doubling again....yeah umm great :) So I
think vb.net is safer.

Why? User population is not relevant to your *front end* -- it's
only relevant to the back end.

- Multideveloper is easier if that ever happens, although this is
probably moot because of the twice as long thing :)

It can be done with Access, which can be used with VSS.

But yeah in all honesty id much rather be working in access, its
faster, easier to do things, half the reasons for vb.net are
flimsy at best, so it will warrant some more thought and
discussion with the head honcho...but I doubt very much we will
change course however...a portion of the users are using the
dotnet version and once that happens theres no going
back....they've seen all the developer express pretty pretty
stuff....it LOOKS great....it must be better...

It already exists? I don't recall you're having said that, unless
you're referring to the web app that uses the MDB data store.

It looks to me as though *all* the problems would be solved by
keeping the Access front end and migrating the back end to SQL
Server. None of the problems would be solved by keeping the MDB back
end and migrating the front end to anything at all.

So, that would make it pretty clear what the best plan of attack
would be, no?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 17 '07 #14

"David W. Fenton" <XX*******@dfenton.com.invalidwrote:

I completely agree with David's comments.

>- Speed running over terminal services (We have remote locations
that im told run very slowly over terminal services, I hope a
webservice is faster)

Eh? That's a function of:

1. bandwidth

2. server provisioning.

If an Access app runs slow on Terminal Server, it probably runs
slowly on the desktop.

In other words, irrelevant to the front-end platform, and entirely
due to problems elsewhere.

Just to expand on this. I've used TS over a 28.8 kpbs phone line. It was sluggish
but it worked. When using Access over a DSL connection to a client with a DSL
connection it's fast enough that I don't even realize it's not local.

So the problem there is not with Access.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oct 18 '07 #15

John Casey

MS-Access/VBA developer since 1991.

*** Sent via Developersdex http://www.developersdex.com ***

Oct 19 '07 #16

John Casey

In my experience, if you convert your Access tables directly over to SQL
Server and do not modify the front end code, you will end up with a
slower application. Regular SQL queries will still need to pull all the
data down from the server before it can process it. All of the existing
SQL queries will need to be converted to stored procedures to see any
type of performance gain.

Also, if the entire Access database resides on the server, this will
cause a performance issue. First try and split front end from backend
(data) and place the front end on the users desktop. I am sure you can
find several possible solutions to update the users front end
automaticaly as modifications are made to it. Later try and pull as many
not core data tables out of the back end and into the front end as they
are found. These tables are usually static tables used for drop downs,
temporary data handling, etc...

Next try and do as much query processing on the front end as possible.
For example if you are running a complex account report lets say, first
just pull down all required fields from all of that account records for
the report. Then do the remaining queries on the front end...

*** Sent via Developersdex http://www.developersdex.com ***

Oct 19 '07 #17

Per John Casey:

>In my experience, if you convert your Access tables directly over to SQL
Server and do not modify the front end code, you will end up with a
slower application.

I've had similar experiences: client mandates that we move tables
to client-server DB and just ODBC and keep the same queries. We
say "That's a bad idea". They say "Just do it".

Performance in 3 cases went from sub-second response time to
several seconds.

What upset me in those cases is that my name was on the app as
the last guy to touch it.
--
PeteCresswell

Oct 19 '07 #18

John Casey <jo********@hotmail.comwrote in
news:47**********************@news.qwest.net:

In my experience, if you convert your Access tables directly over
to SQL Server and do not modify the front end code, you will end
up with a slower application. Regular SQL queries will still need
to pull all the data down from the server before it can process
it.

This is just not true. Jet is very smart, and hands off many, many
of the queries to the server, and just returns the results. If you
look at the Jet SHOWPLAN, it shows exactly that. And if you use
Profiler on your SQL Server, you'll see that SQL Server then uses
the generic SP to make the query passed by Jet into a SP dynamically
and automatically.

Certain types of queries (updates, deletes, inserts) can be *very*
inefficient, though, because, for instance, an insert of 100 records
will get sent by Jet as 100 separate 1-record inserts (each of which
is then run through the generic SP). This is annoying to the
developer, but good behavior on Jet's part, since it allows the SQL
Server to serialize each line, rather than it being taken over by a
long action query. In that case, it's usually better to either use a
passthrough or a stored procedure.

All of the existing
SQL queries will need to be converted to stored procedures to see
any type of performance gain.

Not at all true. *Some* things will need to be converted. Some will
not.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 19 '07 #19

Per David W. Fenton:

>This
is pretty high-level and efficient, since it's vector-based (partly)
and not bit-mapped. So it's very fast.

Given how well it works across 28.8. dialup, I can't imagine why it
would be a problem running on any decent WAN.

I didn't want to say anything, but now I've got to chime in to
support David's assertion.

I do most of my development work via Remote Desktop Connection
over VPN tunnels.

I've got Verizon's most el-cheapo FIOS service: touted at 5mbps,
but I'll bet it never gets over 4.... yet my perceived
performance is good enough to work all day and never be conscious
of being on Remote Desktop. There's occasionally a little bit
of mouse delay, but that's it.

One client has a WAN pipeline from Penna to their London office
and I'm going to try VPN-ing across that as soon as I can. Guys
from London that are visiting here use it all day and claim "no
problem".
--
PeteCresswell

Oct 20 '07 #20

NewsGuy

ugg, just realised my posting details have changed...I used to read as
"John" <no****@nospam.com...now im
"NewsGuy" <jo**@nospam.com.auwrote in message
news:ff********@news5.newsguy.com...

Ive only just returned to this group after a few years absense, I notice
there is another guy called John...thats not me....:) I wonder if thats
confusing...

Installed Vista on my home puta......yes, Im asking for more pain....but so
far.....mostly good....

I guess I should mention that I do realise VB.net will take longer....its a
case of weather its worth it keeping in mind a large system that it may one
day grow into...

I dunno, also no one really wants to work with access is the general vibe I
get...some people scoff at me when I tell them Im an access developer...but
yeah I guess only fools listen to fools....I wonder how the employment
market is for access developers and if/when we get another if that has any
merit for analysis...

John

Oct 20 '07 #21

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff********@news5.newsguy.com:

must admit Im still slightly inclined to go the vb.net route,
(dispite me
agreeing with everything everyone has said so far, maybe im
crazy),

I'd say going the VB.NET route would be crazy, yes.

I think
once we start having to do things like unbound forms and massive
rewrites, while it would still be quicker to go access, I dont
consider vb.net to be hugly more expensive.

But you *don't* have to do unbound forms in Access. All you have to
do to make it perform well is to properly filter the data you're
retrieving to meet the immediate needs of the user. That does *not*
require creating unbound forms.

For what it's worth, in 11 years of professional Access development,
I've implemented unbound forms less than a dozen times. But I've
implemented lots of forms that load a single record or a small group
of records.

I do think that it is definitly the case the front end needs a
70-80% rewrite to work with sqlserver

Well, first, how much work would it take to make the Jet back end
properly structured and to adapt the front end to work with that?
That part should *not* be considered part of the SQL Server
conversion at all. Once youv'e figured that out, then you'd want to
estimate what it would take to convert the back end to SQL Server,
and my bet is that it's relatively trivial (though it's very hard
without experience to forecast exactly which parts of the app will
need to be adapted/moved to the SQL Server).

There must be reasons why people develop in
VB.net.

I think it's because they don't know any better. VB.NET *is* better
for a VB developer who wants to do Internet-enabled apps. But for
Access developers, it lacks a lot.

This is how I think of it;

Access Advantages
Developing a data access layer is where all the work is for
VB.Net. Access has already done this, so there is no work.

I think you're underestimating a lot of what Access has to offer.

VB.NET
The user interface is much quicker to develop robustly in VB.Net
utilising inheritance and user controls.

Huh? Since when? Access has a whole host of controls and objects
specifically designed for use with databases, most of which
VB/VB.NET lack. Sure, VB.NET is better with databound controls than
VB, but still there is nothing to compare to an Access continuous
form for ease of use. And the event models are much poorer for the
tasks of data editing than the Access form/control event model.

Data validation is easier

Howso?

Im just reading a post by Albert D Kallal, lol and David W Fenton,
and all the guru's, lol, I think is a good thread that has alot of
merit.
http://groups.google.com/group/comp....ss/browse_thre
ad/thread/8053a3b03a7162c6/879440056ce94e1b?hl=en&lnk=st&q=vb.net+v
s+access#879440056ce94e1b

That thread certainly makes the point about unbound forms not being
required to create a good app in Access.

right now thats taking a long time to read :) I think it may boil
down to a decision as to weather the project is big enough to
warrent developing the data access layer....but clearly I need to
do more research....as those 3 things were the only I could think
of off the top of my head....

I would vote against the data access layer. But, of course, I'm
operating without first-hand information about the app.

I think that many people from the C/S side of things overengineer
their apps, and a separate data access layer to an app that is
currently operating with a Jet back end sounds like complete
overkill. If a data access layer is necessary, then the app couldn't
work at all with a Jet back end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 20 '07 #22

Per NewsGuy:

I think
once we start having to do things like unbound forms and massive rewrites,
while it would still be quicker to go access, I dont consider vb.net to be
hugly more expensive.

I keep hearing that from people who probably know, but for the
one app that I took the time to develop in both VB6 and MS Access
the ratio was somewhere between three and five to one (got it
recorded somewhere... but can't recall exactly).

But based on the dollars billed by a large company's IT
department when they replaced two of my MS Access apps with
net-centric (I know for sure one was .Net... not sure about the
other) I'd expect the .Net ratio tb more like 10:1.

Been meaning to master enough of .Net to prove or disprove that
suspicion for about five years now... but never got around to it.

Maybe you can report back if/when you do it.

Also... there are those who would argue that in the whole life
cycle of an application, the initial development time is
chickenfeed anyhow.
--
PeteCresswell

Oct 21 '07 #23

NewsGuy

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff********@news5.newsguy.com:

> must admit Im still slightly inclined to go the vb.net route,
(dispite me
agreeing with everything everyone has said so far, maybe im
crazy),

I'd say going the VB.NET route would be crazy, yes.

lol :) Perhaps...im in a flip flop mood today...im going round in circles a
bit....need to take a break to get some objectivity... right now I'm
thinking yeah, Access is much faster, if I just did a complete rewrite of
the access front end it would be dandy, but still thers a "but..." in the
back of my mind....

>I think
once we start having to do things like unbound forms and massive
rewrites, while it would still be quicker to go access, I dont
consider vb.net to be hugly more expensive.

But you *don't* have to do unbound forms in Access. All you have to
do to make it perform well is to properly filter the data you're
retrieving to meet the immediate needs of the user. That does *not*
require creating unbound forms.

For what it's worth, in 11 years of professional Access development,
I've implemented unbound forms less than a dozen times. But I've
implemented lots of forms that load a single record or a small group
of records.

>I do think that it is definitly the case the front end needs a
70-80% rewrite to work with sqlserver

Well, first, how much work would it take to make the Jet back end
properly structured and to adapt the front end to work with that?
That part should *not* be considered part of the SQL Server
conversion at all. Once youv'e figured that out, then you'd want to
estimate what it would take to convert the back end to SQL Server,
and my bet is that it's relatively trivial (though it's very hard
without experience to forecast exactly which parts of the app will
need to be adapted/moved to the SQL Server).

>There must be reasons why people develop in
VB.net.

I think it's because they don't know any better. VB.NET *is* better
for a VB developer who wants to do Internet-enabled apps. But for
Access developers, it lacks a lot.

>This is how I think of it;

Access Advantages
Developing a data access layer is where all the work is for
VB.Net. Access has already done this, so there is no work.

I think you're underestimating a lot of what Access has to offer.

>VB.NET
The user interface is much quicker to develop robustly in VB.Net
utilising inheritance and user controls.

Huh? Since when? Access has a whole host of controls and objects
specifically designed for use with databases, most of which
VB/VB.NET lack. Sure, VB.NET is better with databound controls than
VB, but still there is nothing to compare to an Access continuous
form for ease of use. And the event models are much poorer for the
tasks of data editing than the Access form/control event model.

Yeah, you are right, tho there are certain difficulties to do a few things
on continuous forms...for example say having an editable textbox that shows
tax inclusive/exclusive and binds the exclusive value to the field is a bit
of a mess...

I guess I like the fact that in VB.Net I know it can be done, even if it
takes longer, I know I can get it to do whatever is required, I dont have
that confidence with access....

I think probably this and this reason alone is why alot of people lean
towards VB.net based things...

One instance of this is, say the toolbar, in VB.Net we have it different
depending on the user who is logged in, in Access we just pop up a msgbox
saying you do not have security to use this...its no biggie, but its just
not eligant...weather the boss wants to pay for that kind of elegance I
guess is what counts...

>Data validation is easier

Howso?

Well ya can just have inherited validation that just requires one line of
code...in access I've always had a whole lot of procedural based validation
and sometimes alot of if thens...but maybe this is my inexperience
showing...but I guess its not a big thing, certainly nothing to base a
migration on...

>Im just reading a post by Albert D Kallal, lol and David W Fenton,
and all the guru's, lol, I think is a good thread that has alot of
merit.
http://groups.google.com/group/comp....ss/browse_thre
ad/thread/8053a3b03a7162c6/879440056ce94e1b?hl=en&lnk=st&q=vb.net+v
s+access#879440056ce94e1b

That thread certainly makes the point about unbound forms not being
required to create a good app in Access.

>right now thats taking a long time to read :) I think it may boil
down to a decision as to weather the project is big enough to
warrent developing the data access layer....but clearly I need to
do more research....as those 3 things were the only I could think
of off the top of my head....

I would vote against the data access layer. But, of course, I'm
operating without first-hand information about the app.

I think that many people from the C/S side of things overengineer
their apps, and a separate data access layer to an app that is
currently operating with a Jet back end sounds like complete
overkill. If a data access layer is necessary, then the app couldn't
work at all with a Jet back end.

You are quiet probably right here also, unfortunatly im out of my depth to
argue any different :(

John

Oct 21 '07 #24

NewsGuy

"(PeteCresswell)" <x@y.Invalidwrote in message
news:br********************************@4ax.com...

Per NewsGuy:
>I think
once we start having to do things like unbound forms and massive rewrites,
while it would still be quicker to go access, I dont consider vb.net to be
hugly more expensive.

I keep hearing that from people who probably know, but for the
one app that I took the time to develop in both VB6 and MS Access
the ratio was somewhere between three and five to one (got it
recorded somewhere... but can't recall exactly).

I guess I consider VB6 and .net a different kettle of fish, mainly cause
inheritence makes alot of things so much quicker to develop with...
Right now, in my feeble state of mind, id say 1:3
access:vb.net...........but im in no way qualified to state this, so I
probably shouldnt have written it...

But based on the dollars billed by a large company's IT
department when they replaced two of my MS Access apps with
net-centric (I know for sure one was .Net... not sure about the
other) I'd expect the .Net ratio tb more like 10:1.

Been meaning to master enough of .Net to prove or disprove that
suspicion for about five years now... but never got around to it.

Maybe you can report back if/when you do it.

Also... there are those who would argue that in the whole life
cycle of an application, the initial development time is
chickenfeed anyhow.
--
PeteCresswell

Yeah, I'd agree...200% agree....ya gotta admit though it is difficult to
appreciate that when ya got say 3 months of nobody seeing anything actually
work..."Whats that shy nerdy computer guy do all day, he just sits there and
types and types"....he *must* be crazy lol...

Oct 21 '07 #25

SmartbizAustralia

Wow, what a discussion!

I have been developing in access for the last 15 years, not because
that's all I know but it is so easy to create fast useful
applications!
And yes I also develop with sql server and vb.net...
A good access developer can create a system in half the time of a .net
guy.
Even .net applications can be better prototyped in access first.

So, fix the structure and then port to .net if that is what you have
to do....

The biggest issues you see are:

Bad coding and bad database design.

Yes, move the backend to sql server but do this in a smart way.
Use stored procedures wherever you can and don't query huge
recordsets...e.g be smarter by filtering first!
Just read John Viescas's book on building access applications.

So you really need to do a current e-r plan and future e-r plan and
make the necessary steps to achieve it.

If your users mainly want reports, then do these with stored
procedures and reporting services...this reduces the complexity and
enables your users to get their information securely over the
internet....

You will never win against die hard .net coders until they get the
sack for taking so long and not delivering....

One of the biggest frustrations are pure developers who really don't
understand databases and how to get the most out of them which is
quite a large proportion. If that wasn't true, I wouldn't be so busy.

Bottom line, you need a database specialist, not a developer.......

Regards,
Tom Bizannes
Sydney, Australia

Oct 21 '07 #26

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news3.newsguy.com:

I guess I consider VB6 and .net a different kettle of fish, mainly
cause inheritence makes alot of things so much quicker to develop
with...

I think you over-rate the value of inheritance.

And in regard to the comparison to Access, VB and VB.NET are really
quite comparable in lacking Access's design that is specifically
architected for database applications.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 21 '07 #27

Per David W. Fenton:

>I think you over-rate the value of inheritance.

I'm with David on this one - again.

The flip side of inheritance (as opposed to cloning) is that
if/when you change the object, you need tb *really* careful of
unforeseen consequences to the object's consumers.
--
PeteCresswell

Oct 21 '07 #28

"NewsGuy" <jo**@nospam.com.auwrote:

>The table names have primary key's called "ID" for a good 75% of the tables
which presents a big problem with automated changes...urrggg...this makes me
consider a manual rewrite...

Could you expand on this a bit more?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oct 21 '07 #29

rkc

NewsGuy wrote:

>
I guess I consider VB6 and .net a different kettle of fish, mainly cause
inheritence makes alot of things so much quicker to develop with...

Are you talking about customizing GUI widgets here or creating the data
layer that people have told you you don't need?

Oct 22 '07 #30

NewsGuy

"rkc" <rk*@rkcny.yabba.dabba.do.comwrote in message
news:47***********************@roadrunner.com...

NewsGuy wrote:

>>
I guess I consider VB6 and .net a different kettle of fish, mainly cause
inheritence makes alot of things so much quicker to develop with...

Are you talking about customizing GUI widgets here or creating the data
layer that people have told you you don't need?

GUI...i find it quicker and more control...

Oct 22 '07 #31

NewsGuy

Well if I stick into speed ferret a search for ID it comes back with 1000+
changes which I have to be extreamly careful about as I cannot see them in
context....as alot of the times it is not prefixed like say tablename.ID

Thats with just a plain search, I see there are other kinds of searches in
speed ferret which I havent had a chance to thouroughly look into as there
may be a way to minimize that...

So I figure done manually if I migrate form by form, query by query into a
fresh database, maybe that might be the way to go...but still I gotta look
into automation before I go to that...but right now I cant think of a way to
get around the above problem...
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:8h********************************@4ax.com...

"NewsGuy" <jo**@nospam.com.auwrote:

>>The table names have primary key's called "ID" for a good 75% of the
tables
which presents a big problem with automated changes...urrggg...this makes
me
consider a manual rewrite...

Could you expand on this a bit more?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oct 22 '07 #32

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news3.newsguy.com:

Well if I stick into speed ferret a search for ID it comes back
with 1000+ changes which I have to be extreamly careful about as I
cannot see them in context....as alot of the times it is not
prefixed like say tablename.ID

What do you mean you can't see them in context? SQL strings are
fully viewable, while you see at least the line of code in modules.
Are there all that many references in code?

One solution would be this:

1. rename your linked tables to something else.

2. create a query with the name of each linked table.

3. in the query, include all fields, plus the ID field aliased by
the name it *should* have.

This will transparently allow you to convert all your forms/reports
as needed.

It would also allow you to rename the field in the base table and
then create an alias for ID.

Either way, it's a useful interim step while implementing this kind
of change.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 22 '07 #33

NewsGuy

Not a bad idea...Cool thanks man...I will explore it..

I been a bit busy with critical bug fixes is why I havent had a chance to
have a good sticky beak into it....soon i hope

John

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news3.newsguy.com:

>Well if I stick into speed ferret a search for ID it comes back
with 1000+ changes which I have to be extreamly careful about as I
cannot see them in context....as alot of the times it is not
prefixed like say tablename.ID

What do you mean you can't see them in context? SQL strings are
fully viewable, while you see at least the line of code in modules.
Are there all that many references in code?

One solution would be this:

1. rename your linked tables to something else.

2. create a query with the name of each linked table.

3. in the query, include all fields, plus the ID field aliased by
the name it *should* have.

This will transparently allow you to convert all your forms/reports
as needed.

It would also allow you to rename the field in the base table and
then create an alias for ID.

Either way, it's a useful interim step while implementing this kind
of change.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 22 '07 #34

NewsGuy

"(PeteCresswell)" <x@y.Invalidwrote in message
news:1h********************************@4ax.com...

Per David W. Fenton:
>>I think you over-rate the value of inheritance.

I'm with David on this one - again.

The flip side of inheritance (as opposed to cloning) is that
if/when you change the object, you need tb *really* careful of
unforeseen consequences to the object's consumers.
--
PeteCresswell

Yes, very true :)

I dunno, I guess ya can set up forms quiet easily that work just like access
minus the continuous forms....

Oct 22 '07 #35

NewsGuy

"SmartbizAustralia" <to*@smartbiz.com.auwrote in message
news:11********************@y27g2000pre.googlegrou ps.com...

Wow, what a discussion!

I have been developing in access for the last 15 years, not because
that's all I know but it is so easy to create fast useful
applications!
And yes I also develop with sql server and vb.net...
A good access developer can create a system in half the time of a .net
guy.
Even .net applications can be better prototyped in access first.

So, fix the structure and then port to .net if that is what you have
to do....

The biggest issues you see are:

Bad coding and bad database design.

Yes, move the backend to sql server but do this in a smart way.
Use stored procedures wherever you can and don't query huge
recordsets...e.g be smarter by filtering first!
Just read John Viescas's book on building access applications.

So you really need to do a current e-r plan and future e-r plan and
make the necessary steps to achieve it.

If your users mainly want reports, then do these with stored
procedures and reporting services...this reduces the complexity and
enables your users to get their information securely over the
internet....

You will never win against die hard .net coders until they get the
sack for taking so long and not delivering....

One of the biggest frustrations are pure developers who really don't
understand databases and how to get the most out of them which is
quite a large proportion. If that wasn't true, I wouldn't be so busy.

Bottom line, you need a database specialist, not a developer.......

Regards,
Tom Bizannes
Sydney, Australia

Yeah you are right, one thing I notice about the old guy was he likes doing
the user interface but the backend is an afterthought...
You raised a few questions, which I beg to ask, im short on time at the
moment...I havent looked at reporting services before...the current vb.net
app thats attacheddoesnt use stored procedures its just hardcoded sql
statements.

Thanks Tom
John

Oct 22 '07 #36

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff********@news3.newsguy.com:

I guess ya can set up forms quiet easily that work just like
access minus the continuous forms....

No, you can't.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 23 '07 #37

"NewsGuy" <jo**@nospam.com.auwrote:

>Well if I stick into speed ferret a search for ID it comes back with 1000+
changes which I have to be extreamly careful about as I cannot see them in
context....as alot of the times it is not prefixed like say tablename.ID

Oh, I see what you mean. Yeeyuck. I do *NOT* like duplicating field names between
tables although some do.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm
A few people like my idea. A few people are utterly horrified. Most people just
aren't quite sure about my sanity. <smile>

>So I figure done manually if I migrate form by form, query by query into a
fresh database, maybe that might be the way to go...but still I gotta look
into automation before I go to that...but right now I cant think of a way to
get around the above problem...

I once accidently named a field on a table Inactive. 99% of the time I would've had
the initials of the table in their before the word Inactive but the table started
with an L so it was real easy to miss the l in front of Inactive. That was a PITA
to fix up correctly given how many fields in my database ending with Inactive.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oct 24 '07 #38

NewsGuy

I like some aspects of your naming conventions...

In the past I have not typically prefixed all fields with the initialised
table name, only the primary keys with the full table name with ID if it
happens to be a autonumber or CODE if it ends up being textual based (rarly
tho)...

But yeah I do repeat the fieldname if it is a foreign key, but I see the
merit of your approach, i might start doing that...

Im fond of tbl, qry, mod, etc perfixes too...

....and I often name things that logically read backwards just so they line
up neatly into groups when alphabetised, im not sure if thats hard for
others to read, but I find it easier like that...

After a few important things for the most part I guess consistancy is what
counts

Thats all a whole new thread for that discussion im sure :)
John
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:m1********************************@4ax.com...

"NewsGuy" <jo**@nospam.com.auwrote:

>>Well if I stick into speed ferret a search for ID it comes back with 1000+
changes which I have to be extreamly careful about as I cannot see them in
context....as alot of the times it is not prefixed like say tablename.ID

Oh, I see what you mean. Yeeyuck. I do *NOT* like duplicating field
names between
tables although some do.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm
A few people like my idea. A few people are utterly horrified. Most
people just
aren't quite sure about my sanity. <smile>

>>So I figure done manually if I migrate form by form, query by query into a
fresh database, maybe that might be the way to go...but still I gotta look
into automation before I go to that...but right now I cant think of a way
to
get around the above problem...

I once accidently named a field on a table Inactive. 99% of the time I
would've had
the initials of the table in their before the word Inactive but the table
started
with an L so it was real easy to miss the l in front of Inactive. That
was a PITA
to fix up correctly given how many fields in my database ending with
Inactive.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oct 25 '07 #39

NewsGuy

I guess its not as easy, but easily is a subjective term...

I havent actually done it so, I guess I should shut my gob :) It doesnt seam
to me like to hard a thing to do...(Ive been wrong about,
'seams easy' more times than not tho:) )

Continuous forms for mine are the huge time savers in access....well amongst
other things....

Depending on where we go...I may know soon enough...

John

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff********@news3.newsguy.com:

>I guess ya can set up forms quiet easily that work just like
access minus the continuous forms....

No, you can't.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 25 '07 #40

"NewsGuy" <jo**@nospam.com.auwrote:

>Im fond of tbl, qry, mod, etc perfixes too...

I'm not. Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

Using tbl, qry, frm, rpt really, really slows me down when working in a database.

>...and I often name things that logically read backwards just so they line
up neatly into groups when alphabetised, im not sure if thats hard for
others to read, but I find it easier like that...

In larger systems I will use PO for purchasing, Inv for inventory, QC for Quality
Control and such.

>Thats all a whole new thread for that discussion im sure :)
John

<chuckle Oh yeah!

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oct 25 '07 #41

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news5.newsguy.com:

I havent actually done it so, I guess I should shut my gob :) It
doesnt seam to me like to hard a thing to do

Well, if all your Access work is extremely trivial point and click
work with no use of the rich event model, you would find it easy to
recreate in another development environment. But once you become
dependent on the Access event model, the other products become much
more complex to work (as do unbound forms in Access itself).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 25 '07 #42

NewsGuy

>I havent actually done it so, I guess I should shut my gob :) It
doesnt seam to me like to hard a thing to do

Well, if all your Access work is extremely trivial point and click
work with no use of the rich event model, you would find it easy to
recreate in another development environment. But once you become
dependent on the Access event model, the other products become much
more complex to work (as do unbound forms in Access itself).

I dunno if I agree with that...binding controls to a dataset is pretty
straight forward, and I dont really see how its much different, I see all
the extra work in saving the dataset and all that stuff...

Hmm actually I can sorta see what you are getting at...but im not entirly
sure...I clearly need to know more :) This is my first database driven
project in dotnet if we do indeed go that way (apart from uni
assignments)...

I've come to the conclusion the project needs a rewrite from scratch using
the old one as a template for requirements, im not sure that even trying to
tidy up the old version would be worth it as the database strucuture is just
too conviluted...

I think I need to take a chill pill on discussion of vb.net vs access while
I do more research...

Has anyone ever tried a cross post to a vb.net newgroup? is that frowned
upon? I'd imagine it'd turn into a nasty brawl :)

John

Oct 26 '07 #43

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff********@news1.newsguy.com:

>>I havent actually done it so, I guess I should shut my gob :) It
doesnt seam to me like to hard a thing to do

Well, if all your Access work is extremely trivial point and
click work with no use of the rich event model, you would find it
easy to recreate in another development environment. But once you
become dependent on the Access event model, the other products
become much more complex to work (as do unbound forms in Access
itself).

I dunno if I agree with that...binding controls to a dataset is
pretty straight forward, and I dont really see how its much
different, I see all the extra work in saving the dataset and all
that stuff...

The fact that you reduce what I said to "binding controls to a
dataset" shows that you don't really use Access except trivially.

Hmm actually I can sorta see what you are getting at...but im not
entirly sure...I clearly need to know more :) This is my first
database driven project in dotnet if we do indeed go that way
(apart from uni assignments)...

What does a data-bound .NET form offer to replace the BeforeUpdate
and AfterUpdate events of the form? Do you have to write your own
code? Or does it have default events for that that can be altered as
needed?

I've come to the conclusion the project needs a rewrite from
scratch using the old one as a template for requirements, im not
sure that even trying to tidy up the old version would be worth it
as the database strucuture is just too conviluted...

You mean the back end? That will have to be cleaned up either way,
won't it? It's very hard to build a good app on top of a badly
structured database, no matter what development tools you're using.

I think I need to take a chill pill on discussion of vb.net vs
access while I do more research...

As much in Access as VB.NET, I'd say.

Has anyone ever tried a cross post to a vb.net newgroup? is that
frowned upon? I'd imagine it'd turn into a nasty brawl :)

Most of the .NET people don't understand Access, so I would *not*
appreciate your dragging them into our newsgroup.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 26 '07 #44

Per NewsGuy:

>I think I need to take a chill pill on discussion of vb.net vs access while
I do more research...

From a purely parochial perspective, if you learn VB.net, you'll
be gaining a more widely-used skill set for developers than with
MS Access.

--
PeteCresswell

Oct 26 '07 #45

NewsGuy

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@216.196. 97.142...

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff********@news1.newsguy.com:

>>>I havent actually done it so, I guess I should shut my gob :) It
doesnt seam to me like to hard a thing to do

Well, if all your Access work is extremely trivial point and
click work with no use of the rich event model, you would find it
easy to recreate in another development environment. But once you
become dependent on the Access event model, the other products
become much more complex to work (as do unbound forms in Access
itself).

I dunno if I agree with that...binding controls to a dataset is
pretty straight forward, and I dont really see how its much
different, I see all the extra work in saving the dataset and all
that stuff...

The fact that you reduce what I said to "binding controls to a
dataset" shows that you don't really use Access except trivially.

>Hmm actually I can sorta see what you are getting at...but im not
entirly sure...I clearly need to know more :) This is my first
database driven project in dotnet if we do indeed go that way
(apart from uni assignments)...

What does a data-bound .NET form offer to replace the BeforeUpdate
and AfterUpdate events of the form? Do you have to write your own
code? Or does it have default events for that that can be altered as
needed?

I guess we'd have to write our own code, which I guess is no biggie for
simple stuff...but it wont all be simple...

What kind of instances would you use BeforeUpdate and AfterUpdates for? Why
do I feel like a noob asking that :) Maybe I am! Hmm nah my memory has
somewhat faded...

>I've come to the conclusion the project needs a rewrite from
scratch using the old one as a template for requirements, im not
sure that even trying to tidy up the old version would be worth it
as the database strucuture is just too conviluted...

You mean the back end? That will have to be cleaned up either way,
won't it? It's very hard to build a good app on top of a badly
structured database, no matter what development tools you're using.

Yeah, sorry thats what I meant, was not related to this part of thread, more
the overall thread...I've given up on trying to tidy it, rewrite is the
go...

>I think I need to take a chill pill on discussion of vb.net vs
access while I do more research...

As much in Access as VB.NET, I'd say.

Quiet Possibly, I guess I've only really worked on the one fairly large
access application which i spent 3 or 4 years doing but I never really
encountered any major problems. Maybe I dont know as much about access as I
thought I did...

>Has anyone ever tried a cross post to a vb.net newgroup? is that
frowned upon? I'd imagine it'd turn into a nasty brawl :)

Most of the .NET people don't understand Access, so I would *not*
appreciate your dragging them into our newsgroup.

Yeah, I could invisage that being the case...:) Its pretty hard to be a guru
at both I suppose...

Oct 27 '07 #46

NewsGuy

"(PeteCresswell)" <x@y.Invalidwrote in message
news:ov********************************@4ax.com...

Per NewsGuy:
>>I think I need to take a chill pill on discussion of vb.net vs access
while
I do more research...

From a purely parochial perspective, if you learn VB.net, you'll
be gaining a more widely-used skill set for developers than with
MS Access.

Yeah, 90% of places want VB.Net, I rarly see MS-Access jobs....although my
Access experience got me this one..

It'd be nice if micrsoft made access into a full on development environment
with easier methods of deployment...I guess they dont really want that
though, they want people to go to dotnet..

John

Oct 27 '07 #47

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

What kind of instances would you use BeforeUpdate and AfterUpdates
for? Why do I feel like a noob asking that :) Maybe I am! Hmm nah
my memory has somewhat faded...

BeforeUpdate, at the form or control level, allows you to validate
data before it is saved and inform the user that there's a problem.
This is crucial in avoiding tying up the server with the validation.

AfterUpdate is essential for doing actions after the update is
completed. For example, you might want to requery another open form
or rowsource-bearing control in the AfterUpdate event.

These are essential events for all database programming, and you
don't have to do anything but write the code specific to your app
when using Access. With .NET, you have to do it all yourself, right?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 27 '07 #48

"NewsGuy" <jo**@nospam.com.auwrote in
news:ff*********@news4.newsguy.com:

It'd be nice if micrsoft made access into a full on development
environment with easier methods of deployment...I guess they dont
really want that though, they want people to go to dotnet..

What problems do you have with Access deployment?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Oct 27 '07 #49