December 16, 2003 at 7:23 am
Hi everyone,
I'm doing an upgrade from Access 2000 to SQL Server 2000 - I already have a VB front end in which I intend to rewrite code to connect to SQL instead of Access.
But my first step is to actually get the data out of Access and into SQL!
Here's my dilemma. RIght now, I have 4 client machines, each with a local copy of the Access database (but only one is using the application at any given time) - at the end of each day, the data entry user uploads their newly added records to the central server database.
I'm reading an Microsoft article about upsizing and it says that there are several migration options to consider, and I'm trying to decide wqhich is the best one to use. Can anyone offer an opinion please???
(1) Create a two-tier application.
You can link server-based tables to local tables in Access and use the other existing database objects (queries, forms, reports, modules, and macros) as before. This creates a two-tier application, in which the server running SQL Server stores data, and copies of the interface are stored in Access on the users' computers. Queries are run on the client tier. Locally stored forms, reports, modules, and macros provide the remaining elements of the user interface. The design of server-based tables cannot be modified from Access. The advantage of this method is a low transition cost because no modifications are needed to the front-end database objects. Users continue to interact with a familiar interface, and the SQL Server provides centralized data storage, backup and recovery options, a multiuser environment, an integrated security model, and other advantages. The main disadvantages of this method are that queries are still processed locally, and a large amount of network traffic is generated.
(2) Create a client/server application.
Microsoft Access 2000 introduces Microsoft Access Project, a new type of Access file (.adp) designed as a client/server application. Communication between the client and the server takes place through OLE DB component architecture, which was specifically designed for efficient client/server communication. All tables and data definition objects such as views, stored procedures, and database diagrams are stored on the server. The client stores only code-based or HTML-based objects such as forms, reports, modules, and macros. Most data processing takes place on the server, and only filtered data is sent to the client, thus minimizing network traffic.
(3) Upsize data without making any modifications to the Access application.
If you are going to create a new application in Microsoft Visual Basic®, Active Server Pages (ASPs), or another environment, you may not want to migrate the application-specific logic and user interface.
What would you consider my best option, given my somewhat limited knowledge of SQL Server?
Thanks so much!
Christy
🙂
December 16, 2003 at 11:45 am
Do you want to use the VB front end and eliminate Access altogether (sounds like option #3)? If so, I recommend using DTS to move the database from Access on the server to a database in SQL Server. Microsoft says you can also use the Upsizing Wizard in Access 2000, but I don't have any experience with it.
Maybe this article will help: http://support.microsoft.com/default.aspx?scid=kb;en-us;237980&Product=sql
Greg
Greg
December 16, 2003 at 1:41 pm
Ok. "Be wary quiet, I'm hunting wabbits."
Upsizing Access to SQL Server does give you some advantages, but there are costs.
The two-tier approach would probably be the easiest for you. Since there are only four users and they are not multiple users the first question is why do this? Unless there are mission-critical reasons (you want to gain access to SQL Server's backup/restore and security capabilities) then, from your description there isn't a compelling reason to upgrade.
Ok, back to the types of upgrade.
The two-tier type generates a lot of network traffic. It does that because Access queries are client-based. When you run an Access query it retrieves ALL of the information from the requested tables and brings it to the client machine where it runs the query. It does that every time.
Client-Server - an older program design (it was all the rage in 1995) that breaks the application into separate pieces. This would probably require re-writing the code in some places.
The last is completely re-writing the user interface to work with SQL Server. How much Visual Basic or ASP do you want to know?
If you are doing this to learn about SQL Server with the minimum of fuss, the two-tier seems the best way. It's hard to say exactly without knowing the architecture, hardware, etc.
Bonne Chance!
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
December 17, 2003 at 3:59 am
First thing is to use Access XP (2002) upsizing wizard to transref data and structure (not access 2000)
the simplest solution is to "Create a two-tier application" by linking Access to SQL Server ... that will work as you were in Aceesss .. But Note: you must examine all your queries and Forms .. because you may change some of them to work perfectly with SQL Server 2000
then you can call Stored procedure and views from Access in a simple way ...
and in the same time you work in VB ... until VB Application is OK ... then remove Access.
If you need any help ... contact me
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 17, 2003 at 8:47 am
Well, the thing is - I don't want to take the simplest route, I want to take the most stable route, with a result that will last a while. I'd rather do the extra work now, than do a half-a**ed job, and then have to rewrite things later.
So, having said that, what do you think would be the best route to take?
Patrick, the reason we want to switch to SQL Server is the amount of data we have - the application is getting slower all the time, and as we continue to add more records daily, it's only going to get worse. As well, we eventually want to make the application multi-user, and we're actually half-way there, I just have the code commented out until it's all workiung properly, which should be in the next year. I really think SQL Server is the way to go, as our amount of data is growing in leaps and bounds, and we're just starting out.
Right now, I'm thinking my best option is to rewrite my code to use ADO instead of DAO, and get my application working with ADO and Access. Then port the data over to SQL and try to connect to it instead of the Access database.
What are your opinions on that? Thank you all SO MUCH for your advice. 🙂
Christy
🙂
December 17, 2003 at 8:58 am
so you want to make 2 big steps in 1 atep :
the 2 steps are
1- sending Data to SQL server
2- making VB application connected to it ..
but what if your VB application have a Critical bug after you send data ... then you have to go back to your Last Aceess MDB.
so I recommended making the 2 steps in 2 levels :
level1 : sending Data to SQL server , and connect Access on it
2- making VB application connected to it .. and with every Form you add in VB .. close it in Access .. (so you will work with Access and VB together .. until all forms are transfered to VB ..
believe me .. transfering Data to Sql Server and link data in Access is not very easy as you think ... so you want to make VB application also with it .. in 1 step ?? How ??
this is my openion ,, except your forms and reports are counted on your hand
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 17, 2003 at 9:04 am
Thanks, Alamir. 🙂 No, I didn't want to do it in one step - I still plan to do it in three steps, I just figured that the best thing to do first would be rewrite my code to use ADO. Then after I get that working, I can port my data over to SQL. Then I cna connect to my SQL database in my application.
I already have a VB front-end (I've never used an Access font-end), so I won't be creating any new forms or reports. Just altering my VB code to use ADO instead of DAO.
Do you still think I shoudl do it differently?
Thanks!
Christy
🙂
December 17, 2003 at 9:54 am
quote:
the reason we want to switch to SQL Server is the amount of data we have - the application is getting slower all the time, and as we continue to add more records daily, it's only going to get worse.
With that as a reason, it would seem to me that 2-tier is out of the question- it would inherit architectural performance problems.
My only experience with upsizing was with VFP, and in short it was rather pointless. Hopefully the VB version is better. Unfortunately VFP upsizing wizard just duplicated table structure and data from fox to SQL and created remote views to that data. This is pointless in a way, because all of the logic to access stuff was still in the application- the network would still be overloaded because I would have been treating SQL just like a file-server-table-source, not a data server.
If your VB upsizing wizard can circumvent the above issue under your option 3, and actually put all the traffic/cursors on the server, then short of inefficeint table/index design you may be a world better off.
Upsizing wizards still make me wary though.
December 17, 2003 at 9:56 am
When I said two tier would be out of the question I meant within the perspective of this topic - two teir meaning the data is downloaded and processed on the client.
I think this must just be an access thing because its data isn't stored by a data server, because normally I think of two tier as Client (Results/Entry) <-> SQL (Processing).
December 18, 2003 at 1:20 am
ok Christy .. go a head on what you said ...
all you have to do is
1- reading in upsizing wizard (use access XP .. it is the best , with no bugs in the wizard)
2- make a Local SQL DB on any other Server ...
3- make a System DSN .. to that Local Db
4- change your VB Application to work on this System DSN (ADO and connection, ...)
after you test and every thing is ok ... do this:
1- transer Data to SQL server
2- change your System DSN to the new DB server ...
and every thing will be ok ..
by the way : use SQL server 2000 (not 7) because it support relationships and more powerful.
if you need any other help or resources.. contact me
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 18, 2003 at 6:00 am
Thanks so much, Alamir!
Ok, so let me make sure I have this straight. I will do some more research into the upsizing wizard - I have Access XP (2002) on my machine so I will definitely use that.
I'm a little confused about what you mean when you say make a local SQL DB on another server. Do you mean a local SQL db with the same structure and data as the one I intend to upsize? I only have the one server machine - do you mean use a different machine?
I think I follow all your other instructions fine, but that part confuses me a bit.
Thanks for your help!
Christy
🙂
December 18, 2003 at 7:04 am
I mean use Upsizing wizard to send data to a database name "TempApplication" .. and put it on this server .. or any other server ..
so it will be your Local Db .. where you will connect your VB to. and change data in it will not affect the original data.
do you understand everything now ??
I hope this help you
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
December 18, 2003 at 7:10 am
quote:
by the way : use SQL server 2000 (not 7) because it support relationships and more powerful.
Why do you think that SQL Server 7.0 is not support relationships?
December 18, 2003 at 7:26 am
Ok, Alamir, I think I understand now. I had intended to try and make my ADO code work with my Access database first, but I guess that's really creating mroe work for myself, isn't it?
If I make a temp sql DB with the same structure as my real database, I will only have to rewrite the code once - to connect to the SQL database. Whereas if I do it the way I had intended, I will have to rewrite my code with connection strings to Access, and then later change all the connection strings to SQL.
Only thing I'm concerned about is trying to do too much at once - rewriting the code to use ADO and at the same time connecting to a SQL db instead of Access.
Given my semi-limited SQL knowledge, would you still recommend going the path of using a temp sql db?
Thanks so much, Alamir! 🙂
Christy
December 18, 2003 at 7:26 am
the relationship in Access is transfered to Triggers in SQL server 7 , and is transerefed to Relationships in SQL Server 2000
of course Relationships is more simple to use and understand than Triggers.
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply