Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Converting MS ACCESS DATABASE TO SQL DATABASE.

Posted on 2004-03-22
5
Medium Priority
?
1,105 Views
Last Modified: 2008-03-17
Hi There,

I need few more information on this topic for converting the access db to ms sql db.

When i went through the information on using upzing tool and DTS in sql server, i found few different option available, which make me confuse.

I used MS ACCESS upsizing tool to convert only table defination and structure, relation ship etc,

and  DTS in sql server to import data in existing database created with upsizing tool.

below is the few information i need.
- should i use trigger or  DRI for table relationship
-should i use upsing tool for complet importing of table defination as well as data or upzing only for table defination, relationship and DTS for data.
-when converting application there are three option,
1.- no application change
2. Link sql server table to existing application
3. create a new access client/server application

keeping in mine that we want to use our existing front end after moving to sql server.

after i move data what and how i can use the exising front end, as i have not done this before, i really need information.

i already move data to the sql server, but this is only testing database. please give me brief information how can i move, configure to use existing front end.

Thanks.


Converting MS Access database to the SQL...
0
Comment
Question by:Indra77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10651431
should i use trigger or  DRI for table relationship
* better to use DRI than a trigger, trigger consumes memory and has a higher overhead than a DRI.

-should i use upsing tool for complet importing of table defination as well as data or upzing only for table defination, relationship and DTS for data.
* It would be better to use the tool, if it fails then use DTS.

-when converting application there are three option,
1. no application change
2. Link sql server table to existing application
3. create a new access client/server application

* Personally in this situation I would link sql server table to existing application.
If linking the tables, all the objects (Forms/Reports etc) just need to point to the linked tables, the table is stored in sql-server (the only main difference) but access can use the table as if it was still in the local database system.
It should remain compatible without any back-end access problems, if the table names are the same as they were originally within MS Access.
0
 

Author Comment

by:Indra77
ID: 10652604
Do i have to change any things in term of configuration on different machine and on server using this database when i use second option, link sql server table to existing application.

As i read on a book saying using DRI for relation is not very good, can you give me bit more explaination.

If i want to go for option 3 in case of application change, what i need to do to able to access our front end from different computer.

Thansk.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10653560
As i read on a book saying using DRI for relation is not very good, can you give me bit more explaination
Which one ?

Refering to here: (Discussion on this topic -- 2002)
http://www.dbforums.com/archives/t317082.html

It is also possible that triggers are used for various taks -- but it does depend what DRI tasks you are looking to perform in a transaction-- Generally use the FKs/PKs inbuilt constriants if possible, than using triggers they are a lot easier to debug and consume less memory resources in checking for the same task.
0
 

Author Comment

by:Indra77
ID: 10688168

Hi There,

As i am on the testing phase of data migration. I need small information. Once i update or migrate our database to sql server. I want to have synchronization between .mdb FILE AND DATA IN SQL SERVER. Is it possible to do this. I need this one for few month so that i can build for or other necessary things to use .adp and sql server.

Thanks.
0
 
LVL 13

Accepted Solution

by:
danblake earned 60 total points
ID: 10688523
I want to have synchronization between .mdb FILE AND DATA IN SQL SERVER --
Yes use DTS -- import/export wizard it goes both ways to/from Access to/from SQL Server
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question