Converting Access database to SQL server or Informix
Lee Chee Qwun
Hi
I have a PowerBuilder V4.0 application that uses ODBC driver to connect to
an Access 2.0 database. I am planning to convert the database to either
SQL server or Informix.
Would appreciate if anyone can answer the following questions:
1) Which option is easier? I heard SQL server is very similar to Access
since both are Microsoft products. Will it be much easier to convert to SQL
server?
2) What changes must be made to the program?
3) Will the conversion be easier if I use ODBC driver? Will ODBC driver be
significantly slower than native driver?
Thanks
Chee Qwun
Andrew Mercer
I have successfully used MS-Access export to move tables to Informix OnLine
using ODBC. It creates and populates the tables. You have to remember that
Informix has a limit of 18 chars for table, column and index names. Then you
have to link the Informix tables in MS-Access.
Hope this helps.
Lee Chee Qwun wrote in message <6nfg9q$738$1...@newton2.pacific.net.sg>...
Nils Myklebust
<cc0a...@uco.es> wrote:
>Lee Chee Qwun escribió en mensaje <6nfg9q$738$1...@newton2.pacific.net.sg>...
>>Hi
>>
>>I have a PowerBuilder V4.0 application that uses ODBC driver to connect to
>>an Access 2.0 database. I am planning to convert the database to either
>>SQL server or Informix.
>>
>First you has met care with name on fields and tables, with blank spaces and
>limit size !!!
>If all is right, you can download the 'upsizing tools' from microsoft web,
>that's a complement to access export entery databases to Ms Sql Sever drive
>by ODBC. Upsizing tools make for you:
>
> - Create same index.
> - Make relation and integrites rules.
> - Create defults for all database.
> - Correct translate on autonumber fields.
>
>Dont'n test other products, that's best solution.
The best solution should never be determined by what's the easiest way
to migrate something. You have to think long range.
It may be marginaly more difficult to move to an Informix database,
but you'll get a better engine to run your application on. That's long
range thinking.
One day you'll possibly also want to move away from MS Access as a
front end tool. It's fine for small things, but it definitely have
it's limitations. Even using Visual Basic would be much better. Moving
the application from MS Access to Visual basic, probably a pease at a
time, may not be too hard. You may retain reports and some other
things in MS Access, but create your user interface in Visual Basic.
As your database grows you may also find it advantageous to create
server side applications or even multi tier applications. At that time
you should take a hard look at Java for development. That's the time
you sure want to leave Microsoft behind. They haven't done anything
worthwhile with or in Java. At that time another engine may also prove
a significant advantage. Using Informix you will even have the option
of going to the Universal Server Option giving you significant
advantages at the engine side.
Do check out Informix's offer for NT developers at
http://www.informix.com
Nils Myklebust
NM Data AS
Norway
E-mail: Nils.My...@nmdata.com
FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html
(Now with ODBC info under "Third party products".)
accd...@my-dejanews.com
Nils.My...@nmdata.com (Nils Myklebust) wrote:
> One day you'll possibly also want to move away from MS Access as a
> front end tool. It's fine for small things, but it definitely have
> it's limitations. Even using Visual Basic would be much better. Moving
> the application from MS Access to Visual basic, probably a pease at a
> time, may not be too hard. You may retain reports and some other
> things in MS Access, but create your user interface in Visual Basic
I'd surely appreciate some specifics about why one might want/need
to move away from Access as a front end tool. I keep hearing people
say this, but usually they are quite vague about the _reasons_ that
Access would become unsuitable as a client application.
I do know that creating the same client application in Visual Basic
as the ones I've worked on would be far more expensive in time,
effort, and third-party controls. As far as I can see, there _is_ no
acceptable substitute for Access reporting available to the VB
developer.
> As your database grows you may also find it advantageous to create
> server side applications or even multi tier applications. At that time
> you should take a hard look at Java for development. That's the time
> you sure want to leave Microsoft behind. They haven't done anything
> worthwhile with or in Java. At that time another engine may also prove
> a significant advantage. Using Informix you will even have the option
> of going to the Universal Server Option giving you significant
> advantages at the engine side.
Assuming the server-side applications are not intended to run on Unix,
why would I want to learn yet another language, an interpreted (or
emulated, as someone strongly claimed, as though there was a
universal definition) one, at that? With the VB language that we know
(and of which we use a goodly subset, VBA, in Access) we can
create COM components, and we could, if we so desired, even run
our Access apps under Citrix WinFrame or Microsoft Terminal Server
plus Citrix Mega-whatever). If you are tempted to quote "Write Once,
Run Anywhere", please spare us... laughing so hard tends to hurt
our ribs. It's almost as funny as the oxymoron "The Unix Standard".
P.S., I agree, from personal experience, that Informix is a solid,
reliable, heavy-duty, industrial-strength server. I also observe that
it "demands" a bit more tender loving care in maintenance and
administration than some other server databases, and the details
of its structure, constraints, relationships, etc., are somewhat
less accessible to us poor members of the Great Unwashed
Desktop Database Developer community. _Not_, according to
reports, quite so demanding or inaccesible as one or two others
of that class of server database, I'll admit.
However, let me also acknowledge that with each release, MS SQL
Server has extended its scope both on the "high-end" in competition
with Informix and Oracle, and on the "low-end" with more features
for the Great Unwashed (see above). Gee, I even heard, on good
authority, that the beta of Access 9 comes with a desktop Developer
Edition of SQL Server that will run on Win 95/98 as well as Win NT.
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Tim Uckun
>Assuming the server-side applications are not intended to run on Unix,
>why would I want to learn yet another language, an interpreted (or
>emulated, as someone strongly claimed, as though there was a
>universal definition) one, at that? With the VB language that we know
>(and of which we use a goodly subset, VBA, in Access) we can
>create COM components, and we could, if we so desired, even run
>our Access apps under Citrix WinFrame or Microsoft Terminal Server
>plus Citrix Mega-whatever). If you are tempted to quote "Write Once,
>Run Anywhere", please spare us... laughing so hard tends to hurt
>our ribs. It's almost as funny as the oxymoron "The Unix Standard".
I think his point was that Java has stronger support for distributed
applications.
As far as why you would want to learn another language well I guess you
don't HAVE to learn anything new at all. You could stick with VB all your
life if you want to. I am usually eager to learn new languages myself.
Henry Craven
>I think his point was that Java has stronger support for distributed
>applications.
>As far as why you would want to learn another language well I guess you
>don't HAVE to learn anything new at all. You could stick with VB all your
You may be on shaky ground there Tim.
Bricklayers Maybe. They are still laying bricks the same way since they
built the Ziggurats; - but in this industry ?
I can't name one Cobal or Fortran programmer who hasn't had to
learn another language and is till working within the original bounds.
Everything is evolving at an alarming rate. If you don't constantly update
your knowledge and skills in this industry you are dead. Where is an
Access 2.0 programmer going to be in 2 years if s/he doesn't know
Vb and SQL Server? And 2 more years when its all moving into
COM / DCOM. ?
If someone doesn't constantly want to update your skills (and I'm talking
5 - 10 hours per week here) then I don't think this is the industry for
them.
Experience quickly becomes lethargic and redundant to be overtaken by
eager youth trained in the latest and straining at the bit move on.
Such is the treadmill we are on.
Henry Craven
H_Cr...@bigpond.com
---------------------------
" "
------------------------- Marcel Marceau
accd...@my-dejanews.com
"Tim Uckun" <spa...@zdnetmail.com> wrote:
> I think his point was that Java has stronger support for distributed
> applications.
What the heck does "stronger support for distributed apps" mean?
I've been doing distributed apps since the days of doing them on
multiple networked System/370 mainframes and multiple Series/1
minis, haven't used Java in any of those implementations, and am
still waiting for some good specific _reasons_ why its support is
"stronger".
> As far as why you would want to learn another language well I guess you
> don't HAVE to learn anything new at all. You could stick with VB all your
> life if you want to. I am usually eager to learn new languages myself.
The list of languages I've used in this business would be as long as
your arm, so the "thrill" of learning a new language has lost some of
its lustre for me (most that I did learn, by the way, are long since
relegated to the garbage heap). In my "declining years", I've found
it much better to really know what I'm doing with just a few
languages (aka "rifleshot approach") rather than know just enough
to embarrass myself in many (aka "classic scattergun approach",
sometimes known also as "classic scatterbrain approach").
accd...@my-dejanews.com
"Henry Craven" <Gospodyn...@cyber.ia.net> wrote:
> If someone doesn't constantly want to update your skills (and I'm talking
> 5 - 10 hours per week here) then I don't think this is the industry for
> them.
I certainly wouldn't argue with your premise, Henry, though I might think
5-10 hrs/week a bit much. Of course, if I count "casual reading" of
technical books/magazines, I guess I spend more than that. <SIGH>
Believe me, there are depths yet unplumbed in Access and VB when
you consider the way M'soft is expanding COM. So much so that you
need to specialize even within M'soft's 'world' if you are going to
know your tools "in depth".
Henry Craven
>I guess I spend more than that. <SIGH>
That's the stuff, not necessarily full on course material. I think that
that is best done in a full on intensive over x weeks to get it drummed
in and then recap and expand at a more leisurely absorbing pace.
>there are depths yet unplumbed in Access and VB when
> you consider the way M'soft is expanding COM.
Agreed, The push with the release of MS Small Business Server
to "proper" networking will move many more databases to SQL
Server in even the smaller businesses, and from what I hear of
Access 9 SQL is in there too, so the move in that direction is
inevitable. Coupled with the free Upsizing tool they are making
it as attractive as possible. My guess is to clear the way to
bypass Jet. ( but this would leave VB and Access where ?)
As you say the push to COM is on. - Hopefully I'll find out the
whole story on the 21st.
Well, onwards and (Upwards ?).
Henry Craven
H_Cr...@bigpond.com
--------------------------------------
:-(
--------- Joe Killed the MIME
accd...@my-dejanews.com wrote in message <6o5d32$e0c$1...@nnrp1.dejanews.com>...
Rebecca Riordan
As I understand it, MS is simply uncoupling Access from Jet. Personally, I
think this is a perfectly sensible move. Access becomes a front-end
development tool, which is not really a major definition of the product.
Then there are two relational database engines, Jet and SQLServer, and you
use whichever one suits you.
That's pretty much what we do now anyway, isn't it?
- Rebecca.
Henry Craven wrote in message <35a6d...@139.134.5.33>...
Clive Bolton
This is not a jobs or contracting newsgroup. Stop wasting our time, your
clients' time. Do what you like with your own time, but not with ours.
Clice
Kal Thakkar wrote in message ...
>Dear Sir/Madam:
>
>I am Kal Thakkar, recruiter for Software Tactics, Inc., which is a small to
>midsize consulting firm serving fortune 1000 clients in Chicagoland area.
We
>provide our clients permanent placements as well as consultants in software
>and engineering field.
>
>Currently there are several permanent/consulting positions available for a
>programmer/analyst with background such as yours at our clients in Chicago,
>IL. The salary is competitive and includes standard benefits including
>relocation assistance. We pay referral fee of $300 for every successful
>placement. We sponsor candidates for H1 visa to work in the United States
>if they qualify.
>Please send your resume (via email preferred) with salary requirements,
>visa-status, best time to call and availability information and take your
>first step to a bright future.
>LOOKING FOR INFORMIX DBA CONTRACT/PERM ASAP
>Thank you very much for your time and consideration,
>
>Kal Thakkar
>Software Tactics, Inc.
>Phone: (847)995-8780
>Fax: (847)995-8783
>ktha...@msn.com
>
>
>
Nils Myklebust
The main issue here is *not* to argue with that poster, but make some
issues a little clearer for other interested parties. The "questions"
aren't meant to be answered. The answers are hopefully clear enough
for most unless I have blundered somewhere which sure is more than
possible.
:I'd surely appreciate some specifics about why one might want/need
:to move away from Access as a front end tool. I keep hearing people
:say this, but usually they are quite vague about the _reasons_ that
:Access would become unsuitable as a client application.
Have you ever tried to run 1000 users connected to the same database
using an MS Access front end? Did it work well? Did it give all users
good response time? Would other solutions have given better
responsetimes? Did you use a transaction monitor? Does MS Access
support a transaction monitor well?
Did you ever try with 10 users that wanted to count the number of
selected rows with arbitrary SQL commands (user selected) and then
insert say all customers found into another table, possibly in another
database, all with several million customers in the database, each
having a significant number of rows in many other tables of different
types of data? Did you get good response times? Where you able to
split the inserts into multiple smaller ones to avoide to long
transactions and still get good response times?
Did you try to create multi tiered applications with multi treading at
several levels in MS Access? How did it work out :-)
O, but VB can do that you say. Well, I would want to see that before I
belive you realy get a well working application out of it. Anyway, you
didn't want VB so that's hardly a question anyway. With some more work
from Microsoft it's likely VB will work for developing such
applications, but Java works now. Additionally that's where
significant serious development is done all over the world to create
better and better solutions in this space. Even Microsoft will have a
hard time keeping up with that.
There are many other examples, even at a much smaller scale, where MS
Access is simply a toy.
Of course for a significant number of smaller and simpler applications
MS Access is a great tool. I have however never heard of anyone that
have seriously tried to claim it as a tool for everything like you do.
Even Microsoft doesn't do that.
:I do know that creating the same client application in Visual Basic
:as the ones I've worked on would be far more expensive in time,
:effort, and third-party controls.
That depends on the application and what you want to do. VB is also
becoming simpler, better and faster with every release. Now mind you
VB is only marginally better than MS Access. Other tools are required
in many cases.
:As far as I can see, there _is_ no
:acceptable substitute for Access reporting available to the VBdeveloper.
Now it's for reporting. For simple reports MS Access is just greate.
In some cases Impromptu from Cognos is however better. Other tools
exist as well that can scale better and do more than MS Access can.
Have you however tried to print 100.000 invoices in one run and
implemented a simple way of reprinting some or all of these without
having to read all the data from the database again? Did it do logo
overlays for multiple companies, printing one of a large number of
letters with each invoice and have other such simple functionality. I
wouldn't even try.
:Assuming the server-side applications are not intended to run on Unix,
May be you have to. I have yet to see a Windows NT solution scale to
the power of a Sun Enterprise 10000. All I hear is that it stops way
before this.
:why would I want to learn yet another language, an interpreted (or
:emulated, as someone strongly claimed, as though there was a
:universal definition) one, at that?
May be you should take a look. To the extent that you think you need
it, you can actually compile Java to native code on many machines by
now. However I didn't know that MS Access did that yet, so the
argument isn't particularly well thought out. With VB you can - on
Windows, but you said you didn't want to use that.
A key issue is however that Java is more than fast enough for a large
number of bussiness type applications. In these cases your database
will allways be the "slow" part, so even interpreted Java isn't an
issue. What is a significant issue is however the transportation of
data. If you have any significant ammount of data to deal with you
don't want to transport it over a network. Not even a high speed local
network. Java is ideal in this case with it's simple options for
placing objects where the data is. Soon we will even see Java runing
inside the database engine from at least Informix.
: With the VB language that we know
:(and of which we use a goodly subset, VBA, in Access) we can
:create COM components,
You could, but it isn't quite as easy as Java server side objects and
soon to come Enterprise Java Beans. There are also serious limitations
as to it's scaleability as it's limited to Windows NT only. If you
should claim that Microsoft is cooperating with others to make it run
on Unix I would want to see you COM components written in MS Access --
err... VB, MS Access can't create them so you had to use VB instead as
you said -- runing on Unix. C++ you say - feel free to try. I
wouldn't.
: and we could, if we so desired, even run
:our Access apps under Citrix WinFrame or Microsoft Terminal Server
:plus Citrix Mega-whatever).
At what performance level? Did you check it out? This isn't quite
serious, so I prefer to leave it at that.
And did you try to create a batch mode program in VB? It should realy
run as a service on Windows NT. Not easy although it may be possible
for the advanced developers. Runing it as normal forground application
doesn't guite solve the same problems. In a production environement
you want something we could call a job control system to run things at
predetermined times. Sure the at command exists in Windows NT, but
that doesn't quite do it either. What is needed is a simple way for
users to set up the jobs and have them run on the server where the
database is located. Microsoft didn't make this easy. It has allways
been easy to create such solutions on Unix. With Java it has however
become easy to do even on Windows NT.
: If you are tempted to quote "Write Once,
:Run Anywhere", please spare us... laughing so hard tends to hurt
:our ribs.
I am sure your MS Access applications run without modifications on a
Sun server (not on MS Windows connecting via ODBC or some such thing).
You might want to check out Java a little better. We don't want "Run
Anywhere", but we know we can run several places, and with great
success. Also we can use the same language for client and server side
development. We can easily move parts back and forth as we see what
works best. We can use RMI or go to Corba if we should need that. If
you didn't know both actually work very well by now. We also have the
benefit of a large number of companies creating high performance large
scale solutions arround these and other technologies that you might
not have thought too much about yet.
: It's almost as funny as the oxymoron "The Unix Standard".
In the old days (yes even now) we run Informix 4GL based applications
on almost any Unix machine without even recompiling. That's via an
interpreter that may slow down your applications 0 to 30% or something
like that as compared to the compiled version of the same program.
Most often this is swamped down by other issues, but if you do want to
compile it requires nothing more than a simple recompile of exactly
the same source code on each machine/OS. No changes whatsoever.
For bussiness type applications Unix is actually quite standardised
these days. If you want a GUI front end you can now even get that
without rewriting your 4GL code. Sure I grant you it's not an MS
Access/VB type GUI application, but not all bad. For true GUI
development I would of course not use this language. For that Java is
much better.
Writing some low level things in C is sometimes different, but even
there it has become much better.
With Java we can actually run our applications on both Windows NT and
most versions of Unix without rewriting them. Quite nice for us as
well as our customers. You may not believe it, but that may be because
you never tried. May be you listened too much to Microsoft, or may be
you believed that Java means only running applets in a browser. We
don't currently run via a browser. We create applications. For writing
applets to run "anywhere" Java still has some way to go. It's heading
there however, although Microsoft is trying it's best to stop it.
That's natural of course, as it's one of the few serious threats to
Microsoft's hegemony in the computing industry. I like Microsoft, but
I also like competition. Java plays a main part in that.
:P.S., I agree, from personal experience, that Informix is a solid,
:reliable, heavy-duty, industrial-strength server. I also observe that
:it "demands" a bit more tender loving care in maintenance and
:administration than some other server databases,
It's actually quite easy and becoming simpler with each release,
although it also gets new features that others don't have.
: and the details
:of its structure, constraints, relationships, etc., are somewhat
:less accessible to us poor members of the Great Unwashed
:Desktop Database Developer community.
Don't quite see why. Using either a database design tool or even the
MS Windows based IECC tool that now comes with it, even the simpler
people should have no problems to talk about.
: _Not_, according to
:reports, quite so demanding or inaccesible as one or two others
:of that class of server database, I'll admit.
:However, let me also acknowledge that with each release, MS SQL
:Server has extended its scope both on the "high-end" in competition
:with Informix and Oracle, and on the "low-end" with more features
:for the Great Unwashed (see above). Gee, I even heard, on good
:authority, that the beta of Access 9 comes with a desktop Developer
:Edition of SQL Server that will run on Win 95/98 as well as Win NT.
Well, Informix allready have that - the Personal Edition. Works great
with MS Access, VB and Java.
Jon Borys
Nils Myklebust <Nils.My...@nmdata.com> wrote in article
<35bca4df...@news.newmedia.no>...
> While I where away an anonymous Access Developer wrote:
>
>
> Have you ever tried to run 1000 users connected to the same database
> using an MS Access front end? Did it work well? Did it give all users
> good response time? Would other solutions have given better
> responsetimes?
I certainly never have. Have you? If so, could you explain by what
mechanism java is able to accomplish this non-trivial task with such
robustness? I am working on a project that has approximately 200 users and
am looking for ways to improve performance. I use MS-Access and Openlink
ODBC to connect to informix. Is JDBC that much better/faster? Why?
Thanks in advance for the info!
Nils Myklebust
>
>
>Nils Myklebust <Nils.My...@nmdata.com> wrote in article
><35bca4df...@news.newmedia.no>...
>> While I where away an anonymous Access Developer wrote:
>>
>>
>> Have you ever tried to run 1000 users connected to the same database
>> using an MS Access front end? Did it work well? Did it give all users
>> good response time? Would other solutions have given better
>> responsetimes?
>
>I certainly never have. Have you?
I don't yet have this many users, but have studied the issues involved
and am designing for large number of users.
> If so, could you explain by what
>mechanism java is able to accomplish this non-trivial task with such
>robustness? I am working on a project that has approximately 200 users and
>am looking for ways to improve performance. I use MS-Access and Openlink
>ODBC to connect to informix. Is JDBC that much better/faster? Why?
JDBC isn't in itself better or faster than ODBC. We are talking about
the full Java technology here which includes much more than JDBC.
First with Java you can start at the simple level of moving part of
your application from the client to a server. The first level would
commonly be to move some of it to the same server as the database is
running on, but you can easily use any number of servers. It's well
known that reducing network traffic you can substantially increase
performance. In some applications you also want servers for offloading
application level processing. That's not possible with MS Access alone
as a development system, although there are ways of doing some things
by using remote COM/DCOM objects.
At the next level you will need a transaction monitor. Those are
available for Java developers. They are also available for VB
developers, but a little harder to use at least for an MS Access
developer. Currently you would loose most of the functionality and
advantages of MS Access if you had to use a transaction monitor
(assuming you can effectively at all). The two technologies aren't
designed for each other. This is also to some extent true for the VB
developers. There have been a strong move in the VB developer
community towards data aware controls that give you some of the same
advantages as MS Access. However they are neither very usefull for
multi tier applications nor, and especially so, for transaction
monitors.
There are also intermediate solutions between full transaction
monitors and simply moving objects to the server. One is to use shared
connections to the database among many users. This is relatively easy
in Java, and I think even available as products although I have not
studied this part of the field a lot yet. It's not possible in MS
Access alone, and if possible it would be hard using VB alone and
probably not practical. For these development tools it may be possible
to design some kind of servers that could do such things, but I doubt
it would be all that usefull.
The last thing I will mention here about Java that's very important to
us is the ability to write multi threaded applications in a relatively
simple way. One thing we use this for is to immediately display
information to the user that we know is easily available from the
database. Then we let the user continue to work, while more data is
gathered to be displayed as it becomes available. We are in the
process of developing this functionality now, but know that it will
become very usefull in day to day operations.
Another usefull use of multi threading to us is in an advanced job
control application we are developing for executing batch processes.
In our case we need to do that on multiple servers, and it would be
substantially harder to do with any tool but Java.
All in all Java is an integreated technology with most of the features
available to develop advanced applications of all sorts. With the
newest tools development is also quite easy and fast. It's hard to
beat now, and will become more so as the fledgeling tools bussiness is
developing more advanced, better and simpler tools.
>Thanks in advance for the info!
Nils Myklebust