Hello,
We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the right
direction, tell us the main gotchas, etc. Thanks a lot.
Teri Welch 7 2120
Hi
If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.
John
"Teri Welch" <tr****@yahoo.c om> wrote in message
news:xLuwd.5516 $jn.1131@lakere ad06... Hello,
We maintain a VB6 front-end application using an Access 2000 database. All code and forms are in VB6. The program also uses several queries/reports defined in Access. For corporate reasons we must move to SQL Server right away. Based on our configuration, we're hoping someone can give us a feel for what's involved here. Since we're only using Access for the data we're not sure what an "upsizing" would entail. Can someone point us in the right direction, tell us the main gotchas, etc. Thanks a lot.
Teri Welch
There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.
Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.
What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.
With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try... http://groups.google.com/groups?hl=e...ases.ms-access
Ryan
Teri Welch wrote: Hello,
We maintain a VB6 front-end application using an Access 2000
database. All code and forms are in VB6. The program also uses several
queries/reports defined in Access. For corporate reasons we must move to SQL Server
right away. Based on our configuration, we're hoping someone can give us a
feel for what's involved here. Since we're only using Access for the data
we're not sure what an "upsizing" would entail. Can someone point us in the
right direction, tell us the main gotchas, etc. Thanks a lot.
Teri Welch
On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jb************ @hotmail.com>
wrote: Hi
If you are going to remove access completely your access queries will need to be re-written as stored procedures or views and the reports rewritten in (say) VB. Alternatively you can use an Access Data Project where most of the code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.
Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
The access upgrade wizard will migrate most things, although it can run into problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
I suggest that you create a test system and copy your database and application onto it. Then you try the upgrade, and you will get a feel for what is involved and how much can be easily migrated.
An excellent suggestion - I agree.
Hi Steve
I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.
John
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:kd******** *************** *********@4ax.c om... On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jb************ @hotmail.com> wrote:
Hi
If you are going to remove access completely your access queries will need to be re-written as stored procedures or views and the reports rewritten in (say) VB. Alternatively you can use an Access Data Project where most of the code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about upsizing an Access database to SQL Server, the first response says something about having to convert all your queries to views and stored procedures. Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into reasonable server-side SQL for execution, and it creates prepared statements for parameter queries, so SQL Server won't have to keep recompiling each query each time it sees it. Furthermore, Access is quite happy doing things like using a named query parameter to look up a value from a form control and allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to figure out how to do things like bind forms to parameterized stored procedures, and implement extra code and forms for editing that were never needed before, etc. It's a huge, complex, error prone exercise that forces you to add complexity and remove convinience in the UI, all for basically no return on investment.
Now, you're going to say that sometimes Access does not do a good job of producing the server-side SQL. Yup, that's true, so in -those- cases, using a view and/or a stored procedure can be very beneficial. You're also going to say that if you have multiple systems accessing the same back-end, you want to make the database interactions consistent and centralize business logic. I say, yes, but again, do that incrementally as needed for specific cases. If you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great language for it anyway, and you probably should think about a 3-tier system and dump the Access UI altogether.
The access upgrade wizard will migrate most things, although it can run into problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen the upsizing wizard do a particularly good job. For each table, you should be deciding what should be your clustered index, whether you need a TIMESTAMP field, checking for NULL in Boolean fields, etc.
I suggest that you create a test system and copy your database and application onto it. Then you try the upgrade, and you will get a feel for what is involved and how much can be easily migrated.
An excellent suggestion - I agree.
Yup - I'd say my knee was a bit quick on the jerk there <g>.
On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jb************ @hotmail.com>
wrote: Hi Steve
I don't disagree with anything you said, but you seemed to have missed the caveat in the first sentence, i.e. remove access all together and you don't really have any choice.
John
"Steve Jorgensen" <no****@nospam. nospam> wrote in message news:kd******* *************** **********@4ax. com... On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jb************ @hotmail.com> wrote:
Hi
If you are going to remove access completely your access queries will need to be re-written as stored procedures or views and the reports rewritten in (say) VB. Alternatively you can use an Access Data Project where most of the code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about upsizing an Access database to SQL Server, the first response says something about having to convert all your queries to views and stored procedures. Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into reasonable server-side SQL for execution, and it creates prepared statements for parameter queries, so SQL Server won't have to keep recompiling each query each time it sees it. Furthermore, Access is quite happy doing things like using a named query parameter to look up a value from a form control and allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to figure out how to do things like bind forms to parameterized stored procedures, and implement extra code and forms for editing that were never needed before, etc. It's a huge, complex, error prone exercise that forces you to add complexity and remove convinience in the UI, all for basically no return on investment.
Now, you're going to say that sometimes Access does not do a good job of producing the server-side SQL. Yup, that's true, so in -those- cases, using a view and/or a stored procedure can be very beneficial. You're also going to say that if you have multiple systems accessing the same back-end, you want to make the database interactions consistent and centralize business logic. I say, yes, but again, do that incrementally as needed for specific cases. If you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great language for it anyway, and you probably should think about a 3-tier system and dump the Access UI altogether.
The access upgrade wizard will migrate most things, although it can run into problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen the upsizing wizard do a particularly good job. For each table, you should be deciding what should be your clustered index, whether you need a TIMESTAMP field, checking for NULL in Boolean fields, etc.
I suggest that you create a test system and copy your database and applicatio n onto it. Then you try the upgrade, and you will get a feel for what is involved and how much can be easily migrated.
An excellent suggestion - I agree.
Thanks for the replies from everyone.
1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreads heet functions.
2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.
Thanks,
Teri Welch
"Ryan" <ry********@hot mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. . There are a number of tools you can use to upsize the database, but in my experience, I would tend to do this manually to ensure everything in the structure is correct.
Your main issue will be with the queries as the syntax can be slightly different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...) then move the data using the import wizard and do any manipulation you need to get this correctly into the tables. Then, I'd address each query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be worthwhile looking at using views and stored procedures as these may give you additional benefits.
What you will find is that once done, it's probably quicker and much less likely to have issues with record locking as SQL handles this much better.
With your reports, I would imagine you will leave these in Access, set up an ODBC link to your SQL tables in access and call the reports through VB as you will currently do. All you need to do is swap the links over and it should be straight forward. You may do this and then think about leaving the links in for the queries, but I would advise not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this NG will help, if not, try...
http://groups.google.com/groups?hl=e...ases.ms-access Ryan
Teri Welch wrote: Hello,
We maintain a VB6 front-end application using an Access 2000 database. All code and forms are in VB6. The program also uses several queries/reports defined in Access. For corporate reasons we must move to SQL Server right away. Based on our configuration, we're hoping someone can give us a feel for what's involved here. Since we're only using Access for the data we're not sure what an "upsizing" would entail. Can someone point us in the right direction, tell us the main gotchas, etc. Thanks a lot.
Teri Welch
Hi Teri
"Teri Welch" <tr****@yahoo.c om> wrote in message
news:s1hyd.8482 $jn.4225@lakere ad06... Thanks for the replies from everyone.
1. How can we convert MS-Access import specs to SQL Server. We import many data files from outside sources -- in both ASCII and Excel formats -- and make heavy use of import specs within VB6 programs by invoking the TransferText and TransferSpreads heet functions.
You can use DTS to import from many different locations. 2. Regarding reports, must we leave them in Access? We are inclined to remove Access completely from the picture. Maybe that is too drastic and you would advocate otherwise. But we question the performance of using Access reports and the necessity to have Access open everytime a report is invoked from within VB6. Plus, we have to keep Access installed. How can we create the reports natively within SQL Server.
Reporting services is provided with SQL server, but this is probably not the
type of reporting you require. There may be some third party VB/ActiveX
component that will do a similar job as your current requirements. Thanks, Teri Welch
John "Ryan" <ry********@hot mail.com> wrote in message news:11******** **************@ z14g2000cwz.goo glegroups.com.. . There are a number of tools you can use to upsize the database, but in my experience, I would tend to do this manually to ensure everything in the structure is correct.
Your main issue will be with the queries as the syntax can be slightly different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...) then move the data using the import wizard and do any manipulation you need to get this correctly into the tables. Then, I'd address each query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be worthwhile looking at using views and stored procedures as these may give you additional benefits.
What you will find is that once done, it's probably quicker and much less likely to have issues with record locking as SQL handles this much better.
With your reports, I would imagine you will leave these in Access, set up an ODBC link to your SQL tables in access and call the reports through VB as you will currently do. All you need to do is swap the links over and it should be straight forward. You may do this and then think about leaving the links in for the queries, but I would advise not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this NG will help, if not, try...
http://groups.google.com/groups?hl=e...ases.ms-access Ryan
Teri Welch wrote: > Hello, > > We maintain a VB6 front-end application using an Access 2000 database. All > code and forms are in VB6. The program also uses several queries/reports > defined in Access. For corporate reasons we must move to SQL Server right > away. Based on our configuration, we're hoping someone can give us a feel > for what's involved here. Since we're only using Access for the data we're > not sure what an "upsizing" would entail. Can someone point us in the right > direction, tell us the main gotchas, etc. Thanks a lot. > > Teri Welch
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: areejan2000 |
last post by:
Experts/Programmers please advice on these questions.
1. Is Mysql secure than MsAccess (there are softwares avilable to
retrieve Ms Office Password)
2. Which 3rd Party tool Would You recommend for Administration,Query
creation.... Etc
3.Any tools for setup Mysql database to ship with ApplicationProgram
(Access need not be installed in a Windows machine)
|
by: analyst |
last post by:
I need to migrate an Excel app I developed ten years ago, that has evolved
into something with a life of it's own.
But I know little to nothing about database platforms and development.
The Excel file stores info in 60 cells on each line of the spreadsheet, a
new line for every sample that is received. This spreadsheet grows to 10,000
lines in a couple months and I have to archive and purge it back down to a
more stable size.
The...
|
by: raibeart |
last post by:
It is like comparing a VW bug with a Mercedes.
JET has corruption problems.
It is not meant for use over the internet.
It is not meant for more that 3 or 4 users.
No clear migration path to higher end DBs.
Limited capacity.
Slow.
File system based technology.
File copy of empty MDB file for data to specific directory structure.
|
by: jmnaste |
last post by:
Hi,
I tried to migrate data of an Access 2003 application (.mdb) to SQL
Server 2005 Express.
Using the Wizard and choosing 'create a new database', I receive (a
French message saying roughly) '... migration wizard requires SQL
Server version 6.5 sp 5 and later ... connect to an SQL server source
of data.'.
Given that failure, I went to another PC running MSDE (according to
|
by: KR |
last post by:
Hi,
We have several Access database that we would over time plan to convert
to SQL. I am looking for a tool that works better than the upsize
wizard in helping with the transition - I am looking for something
reliable yet reasonably priced.
Thanks in advance for all your help
| |
by: Elton Cohen |
last post by:
Hi newsgroup!
Can anyone tell me where I should put a simple Access database file in
order to be accessible for every computer in the network (same
workgroup)? There does not need to be any protection (I heard people say
that I should buy a dedicated server or have special rights for each and
every user, but I can keep it simple since there will be no intruders -
no internet connection available -).
I don't think that the "Programs"...
|
by: Igor |
last post by:
Can I convert .MDB file to SQL Server database or I need to develop SQL
server database from begin, every table, every field?
|
by: Wes Groleau |
last post by:
When I try to import from Access, the DTS wizard only allows me to
import tables and queries. OK, I'm not surprised the "macros" and
reports don't come over.
But it executes each query, and created a _table_ to hold the results.
The sensible thing would be that SELECT queries become views
and the others become stored procedures. But I find no way
controls I can select to do that.
|
by: nihad.nasim |
last post by:
Hi there,
I have a database in Access that I need on the web. The web page
should connect to the database and write records for certain tables
and view records for others. I want to know a reliable way of
connecting Access to a server. I am willing to switch to any version
of Access which might solve the problem. Which server would you
recommend and what are the advanatages and disadvantatges of the
server you propose? Please also inlcude...
|
by: Chad |
last post by:
Hi
Is it possible to substitute an alternative data source (eg MySQL or
SQL Server) into an existing MS-Access application?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |