Options when migrating from Access to SQL

Hi everyone,

I'm back with yet another question. :-)

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
:-)

Hi,

In my view lots will depend on how much time you have before you need the application up and running.
Generally speaking the 3-d option is surely the most "solid".And I don't think you have to be that afraid of MS SQL as such. MS SQL is big, that's true, and there might be plenty of things to be aware about.
But once you have the Access database migrated to MS SQL, all you'll need to do is to change your ADO connection and you are ready to execute "SELECT Field1 FROM Table1". The more you'll be getting to know about MS SQL the more things can be moved to the server side
(think of Stored Procedures, Views, etc).
I think this is the way to go.

Regards,
Shurik.

Thanks, Shurik!

So basically you would suggest going with option 3 - eliminating Access altogether, and going with a complete VB-SQL solution? That's what I had planned on doing, but then saw option 1 and wondered if it was a better way to go.

Okay, so now my plan is this:

1) Create device and database in SQL.
2) Migrate my database to SQL using the Access Upsizing wizard.
3) Change my VB connections from DAO to ADO, and alter my code to manage the recordsets through code instead of data controls.
4) Hope for the best. :-)

One more question - I've been "testing" my upgrade with the upsizing wizard, and one of my tables always uploads with no data. The report says it skipped that table or the import failed, but doesn't give me any more information besides that. It's my biggest table - if I remove half the records, the data goes into SQL with no problems. But I've made the SQL database quite large, and give it the option of increasing size as needed. Any ideas as to why the table goes into SQL empty???

Thanks!!!
Christy
:-)

Thanks, Shurik!

So basically you would suggest going with option 3 - eliminating Access altogether, and going with a complete VB-SQL solution? That's what I had planned on doing, but then saw option 1 and wondered if it was a better way to go.

Okay, so now my plan is this:

1) Create device and database in SQL.
2) Migrate my database to SQL using the Access Upsizing wizard.
3) Change my VB connections from DAO to ADO, and alter my code to manage the recordsets through code instead of data controls.
4) Hope for the best. :-)

One more question - I've been "testing" my upgrade with the upsizing wizard, and one of my tables always uploads with no data. The report says it skipped that table or the import failed, but doesn't give me any more information besides that. It's my biggest table - if I remove half the records, the data goes into SQL with no problems. But I've made the SQL database quite large, and give it the option of increasing size as needed. Any ideas as to why the table goes into SQL empty???

Thanks!!!
Christy
:-)

you should also look for replication and dts feature of sql server this thing might also help I think...

About that table. Don't know exactly what's wrong.
No data with a wrong format or so somewhere in that big table?
Anyway try the following

-link the MS SQL table to Access
-set up a usual Append query

Just out of the interest, ho big the table is and how manu fields (what data type?) it has?

Shurik.

The table has 45 fields and approximately 15,000 records. Many different data types including Number (mostly Long Integer, but one field is Double), Date/Time, Text, Currency, and Yes/No.

There *shouldn't* be any data in the wrong format in any of the fields, because Access allows the data fine.

I'll try the Append Query thing you suggested, Shurik.

Thanks,
Christy