copying data from Microsoft Access to SQL Server

Hello

I am developing a web application that will allow users to upload a .mdb file and from that file I need to populate an SQL database. I know the table name of the .mdb file, but I am unclear how to structure my data access layer correctly. How do I pull data from the .mdb file and once I have it how do i populate the SQL database?

Any advice would be greatly appreciated.

thanks!

There is one problem with moving Access data on the fly Access let people to just point and click what they wish, if that is your situation you need DTS because in SQL Server your data must be clean and relational. So if you have clean table definitions and clean inserts you could just use OpenRowset or OpenQuery. If not then DTS is your friend. So check your setup and post specifics so I can give you the resources as needed. Hope this helps.

copying a database across servers

I'm developing a web application locally using SQL Server 2000. When it's time for me to throw it up on my host, how do I copy the database from my computer to their server? Thanks.use the DTS tools provided with SQL Server (import/export data).

What I usually do is script the database structure (and objects), set that up and check it over, THEN transfer the data, but whether you want to do it that way is up to you

j|||Another way is to simply stop your sql server and copy the database and log files to the other location and then attach the database.

Regards|||There are multiple ways as in using DTS or other tools. BUt if the other server is far apart and you donot want to use the internet bandwidth then I feel the best bet is to backup the database and then transfer/FTP the DB backup to be restored into the destination server. Copying the data and the log files are fine. But not the cleanest of approaches AFAIK.|||All solutions provided here are good, I would personally go with Atrax' Scripting solution, it is probably the best way to do it if you don't need to transfer any data and bandwidth is an issue, but you will need the admin on the remote server to launch your .sql script. Here's what I do: Select all your user tables, right-click and copy. Paste them into the Query Analyzer and boom you've got the script to create your tables. Same with SPROC's and any other objects you have created. You can paste them all into one file.

Add this to the top of your SQL Script if the DB does not exist on the server:

CREATE DATABASE [YourDBName]--Skip these two lines if the DB does exist
Go
USE YourDBName -- Add this regardless

and Save it. The script can now be executed from the QA on any SQL server to create your DB.
Piece-o-cake.

If you did need to transfer any data, you could create a DTS package to export the data to the remote server. But of course you have to have access and it depends on the amount of data you have stored and bandwidth as to how effective a transfer would be.

But again, all of the solutions provided here by the other posters so far are viable. It's a matter of personal preference. All though, if you script it and something blows up locally, you can easily recreate your database without having to copy it back from the remote server. An unlikey scenario, but entirely possible... just a thought.

Good luck.

Copy Table Problem

Hi I am developing and web app for my final year in college but I am having problems trying to keep my 2 databases up-to-date (one in college and the other at home) I'm just wondering if there is anyway to copy a single table from the college db to the home including the table design and data etc. So I'm just wondering if anyone has any ideas of how I would go about doing this

Thanks in advance

Tim

Sql Server 2000 had aDTS Import/Export Wizard which allowed you to copy data between two registered Sql Servers (these could be on remote systems). I haven't used Sql Server 2005 but I would presume it would have something similair.