Transfering Data

[Ro]

I've been given the task of transfering data from an access db to sql
server 2000. The previous access db was not structured very well so
we reconstructed the tables in sql server 2000 to add better table
structure and more functionality. What I want to be able to do is
transfer the actual data in the access tables to the new tables in
the sql server 2000 db. Does anyone know of a simple way to do this?
I've been looking into CA solutions' Allfusion Modeling Suite but i'm
not sure if it could handle what it is i'm trying to do. If anyone has
any suggestions please help me out.

Thanks in advance,

 

[Jeff Boyce]

Open the Access db to the database window.

Using a DSN (create an ODBC driver/DSN if needed), connect to the new table
in SQL Server 2000.

Create an append query from the existing Access table to the (linked)
SQL-Server table.

Run it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ro]

Open the Access db to the database window.

Using a DSN (create an ODBC driver/DSN if needed), connect to the new table
in SQL Server 2000.

Create an append query from the existing Access table to the (linked)
SQL-Server table.

Run it.

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Thanks Jeff, I should have explained that I'm a pretty novice user. I
need you to break it down a little bit more for me. Sorry
This is my first time working with the sql and access database. I'm
much more familiar with sql then access so I only understand how to
open the db in access the window............

 

[Jeff Boyce]

I'm not clear which pieces you are looking for more detail on.

The "DSN" step builds a "translater" that Access uses when speaking with
SQL-Server. First build the ODBC/DSN, then use it from within Access to
"see" the SQL-Server table. (You'll have to have the permissions from the
SQL-Server side first.)

If you don't understand creating queries in Access, consider searching out a
tutorial on how to get started. I believe Access even has tutorials,
accessible via HELP.

What, specifically, were you looking for clarification on?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Open the Access db to the database window.

Using a DSN (create an ODBC driver/DSN if needed), connect to the new
table
in SQL Server 2000.

Create an append query from the existing Access table to the (linked)
SQL-Server table.

Run it.

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Thanks Jeff, I should have explained that I'm a pretty novice user. I
need you to break it down a little bit more for me. Sorry
This is my first time working with the sql and access database. I'm
much more familiar with sql then access so I only understand how to
open the db in access the window............

 

[Ro]

I'm not clear which pieces you are looking for more detail on.

The "DSN" step builds a "translater" that Access uses when speaking with
SQL-Server.  First build the ODBC/DSN, then use it from within Access to
"see" the SQL-Server table.  (You'll have to have the permissions from the
SQL-Server side first.)

If you don't understand creating queries in Access, consider searching outa
tutorial on how to get started.  I believe Access even has tutorials,
accessible via HELP.

What, specifically, were you looking for clarification on?

Regards

Jeff Boyce
Microsoft Office/Access MVP









Thanks Jeff, I should have explained that I'm a pretty novice user. I
need you to break it down a little bit more for me. Sorry
This is my first time working with the sql and access database. I'm
much more familiar with sql then access so I only understand how to
open the db in access the window............- Hide quoted text -

- Show quoted text -

Thanks again Jeff, you were very helpful. I found a solution using the
sql server's DTS. It did exactly what i needed.