?
Solved

Export 1000 first records from all tables in an SQL Server database to MS Access

Posted on 2005-03-08
20
Medium Priority
?
987 Views
Last Modified: 2008-03-06
Dear experts,
I need a way to take an extract of a huge SQL Server database offline.
I think the best way is to automaticly export, by a script maybe, the 1000 first records of every table to Access. Then I'll be able to copy this small Access database to my computer, then recreate a small
SQL server database on my computer. This will allow me to test my programs against an offline version
of the huge original SQL server database. By the way, I need to do the same for other ODBC data sources,
like AS400, Oracle, etc.
Thanks in advance.
0
Comment
Question by:prismabi
[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
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13486489
If the MSSQL database is online, you can do the call to it from home and import directly.  Otherwise, it sounds like you have the idea.  Use Access to import a sample from the table, then export from Access into your personal MSSQL installation.  It is essentially the same process for your other data sources, just with different ODBC drivers.

With which part do you need help?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13486578
You can select the records in a query and link the query to access (or import the result)

SELECT TOP 1000 *
FROM myTable

0
 

Author Comment

by:prismabi
ID: 13486794
Thanks. It's amazing how quickly you answer!
I need an access VBA snippet that will do all the job automaticly.
It should ask me only to connect to SQL Server database.
Tell me if you can do it for me, and if you want more points.
Thanks
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13487041
>  think the best way is to automaticly export, by a script maybe, the 1000 first records of every table to Access

Assuming this is a relational database, this may not be of any use to you. The first 1000 records in one table may be related to the last 1000 records in another table, and doing a simple export like this will extract a non-consistent dataset. What are you trying to achieve?
0
 

Author Comment

by:prismabi
ID: 13487322
You're right. But in fact I don't care of dataset consistency. I just need the Tables definitions and some
data to better understand the ODBC database. I can not explain here what I am exactly doing.
0
 

Author Comment

by:prismabi
ID: 13487341
I just added 250 more points for the Access vba code snippet.
Does somebody want to write it? Just tell me, and how many points do you want?
Thank again
0
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 800 total points
ID: 13487560
Ok, you have several ways to do this.  Here's a relatively simple method:

1) In your MSSQL, create a view of table using this SQL:
    SELECT TOP 1000 * FROM MyTable
2) In Access, use the "Get External Data" wizard to import data from MSSQL, using the view you created as your source.
3) In your home installation of MSSQL, use DTS to import the table from Access.

No code needed, and the process is very straight-forward.  The wizards should do all the work for you.  If you need more help, let me know.
0
 

Author Comment

by:prismabi
ID: 13488194
Dear Routinet,
I'll give you the 500 points for your good answer.
But the men that is collecting the SQL data extract for me is a very busy person,
so I still need an automation for him, and of couse I'll pay more points for it.
Just tell if you want me to add more points here or open a new ticket?
thanks
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 1200 total points
ID: 13488533
To do the same thing in VBA, create your query in SQL Server:

SELECT TOP 1000 *
From myTable

Then in Access have:

Dim strImportFile as String
strImportFile = "Location and name of your SQL Server database"
DoCmd.SetWarnings False
On Error Resume Next
DoCmd.DeleteObject acTable, "tblImported_Data"
DoCmd.TransferDatabase acImport, "Microsoft SQL Server", strImportFile, acQuery, "QueryNameinSQLServer", "tblImported_Data"
DoCmd.SetWarnings True


Idea?
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13488541
This version will import the resultant query into your db as a table called "tblImported_Data"
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13488684
You can also create a DTS package in MSSQL to do all of the steps I listed.  Actually, you would create two: one for the office, and one for your home.  The office package would simply export the view to a predetermined Access database, likely in a share you can access from your workstation.  The home package would be just the reverse - importing the data from Access into your MSSQL.

Danny's solution will work also, though it is from the Access side.  You'll need to be able to connect to the MSSQL installation.  Perhaps this link will help you out with that:

http://www.able-consulting.com/
0
 

Author Comment

by:prismabi
ID: 13488899
Dear dannywareham and routinet,

I am a new premium user at Experts Exchange, so please forgive me if I made a mistake.
I splited the points:
300 for the code of dannywareham
200 for the great help of routinet
Tell me if it happens like I wanted.

Is there any way to get a complete coded solution from you here?
Are a lot of points an honest way to pay for it?
Again, forgive me for asking embarrassing questions.

And thanks again for your great competence and your quick answers!
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 13488984
>>Is there any way to get a complete coded solution from you here?
No problem.
My example should be placed in Access.
The SQL example should be placed in SQL Server. That should work.
There is another way, where a query is created only in Access. I'll figure it out, but it's something like:

SELECT TOP 1000 *
FROM ("C:\Location of database\databasename.sql /\ queryobjectname");


>>Are a lot of points an honest way to pay for it?
Of course.
We're all here as volunteers to help others (and to improve our own knowledge).
Also, the EE agreement means that we're not allowed to accept payment for our services.

>>forgive me for asking embarrassing questions
There are no embarrasing questions here (as we're no doctors and you haven't got a strange growth in your pants - I hope)
0
 

Author Comment

by:prismabi
ID: 13489917
Ok, i will place a new question ticket on a Completly Automated Program, all in Access,
so my boss need only to press a button, select an ODBC system data source and he
got a nice little MDB ready to copy and go home.
How much points do you want for such a tool?
0
 

Author Comment

by:prismabi
ID: 13491049
How much points do you want for such a tool?
0
 

Author Comment

by:prismabi
ID: 13493716
Am i allowed to give 10000 points to somethink i need?
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13496160
prismabi: EE's forums are not really conducive to coding an entire project.  We can help with particular, specific questions, and occasionally with something a little more involved, but if you want a project created, I would advise you to seek out a programmer local to your area and contract them to create it.  If you would like to create the project yourself, we can surely help you along the way, but I do not believe you'll find anyone here willing to create one for you...after all, we are mostly IT professionals volunteering our knowledge and skills.

For the points, the maximum you can provide with any one question is 500 points.  Occasionally, for a very in-depth question or a question in which several experts gave invaluable assistance, you would be allowed to create a second "points for" question to provide more award.  You can see all this information, and more, in the help section.  Here's a link to the relevent part:

http://www.experts-exchange.com/help.jsp#hi50
0
 

Author Comment

by:prismabi
ID: 13501286
dannywareham,
I apologize to dannywareham for asking such a long question!
I didn't realize it was completly volontary.
thanks for your patience

 routinet,
thanks for clarifying the point to a newbie like me!
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13507691
No problem, it's what we're here for.  :)  The help section contains quite a few hints and tips for newbies, and is an easy read.

Good luck with the rest of your project!
0
 

Author Comment

by:prismabi
ID: 13509813
Thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

752 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