Importing Tables from SQL Server to Access with Keys and Indices preserved

Sujeet

Hey guys,
I want to import a database from SQL Server 2000 to MS Access with all
indices and keys along with the tables. DTS does not export indices
and keys, only the structure and the data.

When used "Import External Data" through Access(linking through ODBC)
it creadted liked tables, however with indices and keys. Can remove
these links and preserve only th stucture back. How do I do it.

Thanks

Nov 12 '05 #1

Subscribe Reply

5591

Rich P

Upsizing from Access to Sql Server is not uncommon. But downsizing from
Sql Server to Access is a different story. As far as RDBMS's go, Sql
Server supports a much wider array of RDBMS things than Access (since
Sql Server is a real RDBMS and has not extended support (in a manner of
speaking) for VBA inside the Sql Server mdf). Sql Server is designed
specifically for RDBMS stuff. Access has a much smaller support for
RDBMS but supports other stuff like VBA/OLE automation (from within the
mdb) and so forth. They are different tools. Additionally, Access is
the size of a molecule compared to Sql Server. If your Sql Server
tables don't exceed the limitations of Access (less than 255 columns,
less than 1 gig of data - less than 200 megs of data really) you can
duplicate such a table in Access, but you can't duplicate clustered
indices and so forth.

I would create tables in Sql Server to match the exact structure of the
tables in Access. Populate these tables with your Actual Sql Server
data. Then you can use DTS to export this data to your Access tables.
If you have a lot of data, you will have to create a lot of mdb's.

Anyway, this is how I would approach it (if I had to). Disclaimer: it
is possible that there is a utility that I am not aware of that can do
what you need. It is also possible that there are methods in Access/Sql
Server that I am not aware of for doing this. So the above is just a
suggestion.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #2

Sujeet Varakhedi

Rich P,
Thats true in every sense of it. Our database in really huge housing
around 350 tables and surely more that 3GB of data. We just needed the
structure of these tables in MS Access for training our clients.
I did get the structure, keys and indices through to access from SQL
Server but required a lot of manual effort. Here are the steps. I will
be making a tool to do this if I get the scripts

1)Create linked tables in access connecting them to the SQL Server
Database through ODBC. In this case the strcture and the other
objects(like indices,keys,co nstraints etc. are also imported)
2)Still these are links and not tables. Individually go to each table
design view and click "Save As" to a table with a diffrent name.
3)Delete the linked tables and compact the database.

I will need scripts for all the steps to create a tool. I am sure I will
find it.
I will post it as soon as as I am done.

Thanks
Sujeet

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #3

Ted Theodoropoulos

DTS would be the only facility i would think would accommodate this
and it obviously doesn't. the problem is sql server has different
data types and speaks a different dialect of sql. in access you don't
have clustered indexes and fill factors, etc so there has to be some
utility to translate these special indexes in to generic access
indexes.

maybe there's some shareware utility out there that does. i think
that's your only hope. if u do find a way to do this, please post
your solution.

sv******@yahoo. com (Sujeet) wrote in message news:<c6******* *************** ****@posting.go ogle.com>...

Hey guys,
I want to import a database from SQL Server 2000 to MS Access with all
indices and keys along with the tables. DTS does not export indices
and keys, only the structure and the data.

When used "Import External Data" through Access(linking through ODBC)
it creadted liked tables, however with indices and keys. Can remove
these links and preserve only th stucture back. How do I do it.

Thanks

Nov 12 '05 #4

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the SQL Server scripting utility (right-click the Database
name in Enterprise Manager & select "All Tasks" > "Generate SQL
Script") it will generate SQL DDL scripts that you can use in Access
to create tables & indexes & constraints. When you run the utility
you will have to limit what can be created - nothing Access can't
"understand ." The results of the utility can be saved to a text file,
edited (to get rid of anthing Access doesn't understand) and run as
queries in Access to create the structure of the SQL Server db. Then
use DTS to transfer the data to the Access tables.

HTH,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP+Jtx4echKq OuFEgEQIg3gCfTw SeUY+34w10rAmik R39TC8LxnwAnjbL
VguTvxC6PIfmOON dPJB/iY/y
=coIr
-----END PGP SIGNATURE-----
Sujeet Varakhedi wrote:

Rich P,
Thats true in every sense of it. Our database in really huge housing
around 350 tables and surely more that 3GB of data. We just needed the
structure of these tables in MS Access for training our clients.
I did get the structure, keys and indices through to access from SQL
Server but required a lot of manual effort. Here are the steps. I will
be making a tool to do this if I get the scripts

1)Create linked tables in access connecting them to the SQL Server
Database through ODBC. In this case the strcture and the other
objects(like indices,keys,co nstraints etc. are also imported)
2)Still these are links and not tables. Individually go to each table
design view and click "Save As" to a table with a diffrent name.
3)Delete the linked tables and compact the database.

I will need scripts for all the steps to create a tool. I am sure I will
find it.
I will post it as soon as as I am done.

Thanks
Sujeet

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #5

Similar topics

2623

what level of server access is required in order to view tables with Server Explorer?

by: Bennett Haselton | last post by:

I want to get an ASP.Net hosting account with my ISP, and I'm trying to find out what level of access to the server is requried in order for me to view the server in Server Explorer in Visual Studio .Net, where you can see the available database, expand it to get a list of tables, etc. What level of access is required? Of course you need to have an account with the right user rights, but is there some specific service that needs to be...

.NET Framework

2441

Importing Tables Using MySQL Control Center

by: Campbell's List | last post by:

Hi, I'm completely new to MySql and need help importing tables from Access or Dbase. I am using Dreamweaver MX to create a data-driven web site. With the VPS hosting plan we're on, our remote FreeBSD server only supports the PHP application server. PHP requires MySql. Thus, I installed a copy of MySQL Control Center 0.9.2 beta. (I prefer a GUI and not command prompts unless necessary.) The information I plan on using in developing...

MySQL Database

2679

Access Keys Need to show up as underlined by default

by: AlanF | last post by:

We have defined access keys for both our menu choices and buttons. However, when the menu displays, the access key "underline" on the menu does not display until we press the ALT key. We can toggle the access key underline display by hitting the ALT key again. How do we get the access keys to be underlined by default and stay underlined whether we hit the ALT key or not? Default behavior in VB6 was that the access keys always showed up...

.NET Framework

3067

Slow performance progromatically linking tables in Access

by: Mark P | last post by:

I have a bit of vb code that uses Tables.Append to programatically link tables from Oracle and DB2 datasources. The problem I am having on some client machines is that the link will take anywhere from 1 to 3 minutes to link the table. When I manually link the tables,through Access, it takes no time at all. Note that only certain client have the problem, and the problem will sometimes go away. These clients will usually get an ODBC Call...

Microsoft Access / VBA

1963

Security & Linking/Importing Tables in Access 2000

by: patcho | last post by:

Hello, I have a problem that I was hoping to get some assistance with. I have built a split database (back end with all the tables and a password to protect the information & a front end to link to it). I have followed the Access Security model to create security over my Front End database (create a new workgroup, remove the admin user from the admins group, change permissions on the users group to very limited access etc).

Microsoft Access / VBA

9156

How to list tables with Primary keys

by: Danny | last post by:

Hello, We imported a bunch of tables from a database and realized that the primary keys weren't copied to the destination db. In order to re- create the keys, we need to know which tables have them. Is there a command that I can use (on the source db) to find out which tables contain primary keys? The db has hundreds of tables and I'd rather not go through each one to see which has a primary key. Also, for future reference, is there a...

Microsoft SQL Server

8255

SQL linked tables in Access FE occasionally go read only

by: Bobby | last post by:

Hi, Not sure if this is Access, SQL or ODBC. I have a SQL database with an Access Front End. They are linked with ODBC. Occasionally (it's happened 3 times in 4 months) some of the linked tables in Access become read only and I can't add new records to them. If I go into design view (in Access) the tables have lost their Primary Key. In SQL, they still work just fine. The only way I have found to get the links working again is to delete...

Microsoft Access / VBA

1517

Linked Tables in Access pointing to MSSQL server

by: dwjongbloed | last post by:

We have a third party application that uses an Access front end but the tables in Access are pointed to our MSSQL Server. I have no idea how these are pointed to our server and am looking for any help I can get to figure this out. We are going to be upgrading our servers and I need to know where to fix this connection string out as we will be assigning the new server a differenty name.

Microsoft Access / VBA

1576

Exporting, Emaling and importing tables in Access

by: KingKen | last post by:

I have a database that my users populate over a wide area. this database is not networked as it is not possible to do so given finance and location of users. I however want to have all the information in one data repository. To achieve this I am attempting to have a table of updated data exported, emailed central office, then imported to the main database and appended to the existing records. I want to accomplish the export and email function...

Microsoft Access / VBA

8231

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8672

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8330

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7153

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

5561

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4075

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4167

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2603

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1780

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP