Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1191
  • Last Modified:

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

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
prismabi
Asked:
prismabi
  • 10
  • 5
  • 4
  • +1
2 Solutions
 
Steve BinkCommented:
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
 
dannywarehamCommented:
You can select the records in a query and link the query to access (or import the result)

SELECT TOP 1000 *
FROM myTable

0
 
prismabiAuthor Commented:
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.

 
shanesuebsahakarnCommented:
>  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
 
prismabiAuthor Commented:
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
 
prismabiAuthor Commented:
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
 
Steve BinkCommented:
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
 
prismabiAuthor Commented:
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
 
dannywarehamCommented:
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
 
dannywarehamCommented:
This version will import the resultant query into your db as a table called "tblImported_Data"
0
 
Steve BinkCommented:
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
 
prismabiAuthor Commented:
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
 
dannywarehamCommented:
>>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
 
prismabiAuthor Commented:
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
 
prismabiAuthor Commented:
How much points do you want for such a tool?
0
 
prismabiAuthor Commented:
Am i allowed to give 10000 points to somethink i need?
0
 
Steve BinkCommented:
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
 
prismabiAuthor Commented:
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
 
Steve BinkCommented:
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
 
prismabiAuthor Commented:
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now