473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
4 5575
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
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,constraints 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
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.google. 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
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP+Jtx4echKqOuFEgEQIg3gCfTwSeUY+34w10rAmikR39TC 8LxnwAnjbL
VguTvxC6PIfmOONdPJB/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,constraints 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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
0
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...
2
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...
18
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...
3
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...
2
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...
2
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...
1
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...
5
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...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.