Solved

Transfer recordset to Access-table

Posted on 2010-11-29
10
377 Views
Last Modified: 2012-05-10
Hi experts,
I have an SQL server 2008 R2.

From an MS Access db 2007 (on XP) i am trying to transfer a recordset that I got from the server into a lokal table. I cannot link the table because it gives me the wrong resultset - some numbers are gone!?)

With OPENROWSET I got a Jet problem.

I can of course insert it in a loop, but there must be a faster more pritty way to do it?
0
Comment
Question by:hke72
[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 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34235852
1. "I cannot link the table because it gives me the wrong resultset - some numbers are gone!"
Then why not investigate why this standard approach does not work?
perhaps it is as simple as changing a datatype?

2. "I can of course insert it in a loop, but there must be a faster more pritty way to do it?"
Beauty is in the eye of the beholder.
;-)
If a loop works, then use it...


JeffCoachman
0
 
LVL 9

Expert Comment

by:Ramanhp
ID: 34236686
i understood ur concern, here is a way out i use to do in such a scenario

1. create a linked table
2. create a 'Make Table' query appending all the records from the 'linked table' within the same database

that's all

and now you may refer the new make table for accessing the records of ur sql table

3. in case, u do a refresh, make a delete table to delete the table made in step 2

well, that's the fastest/smartest way u may like
0
 
LVL 1

Author Comment

by:hke72
ID: 34236747
Hi JeffCoachman and Ramanhp: The problem is the linked table not working. I will explain:

I do not have the days to poke around in it. There definitely is something with regional settings/language/data types etc. etc. It works in my colleagues  English version, but not in my Norwegian. What we did was to change the data type in the SQL server to 'money'. When I reconnected to the table the columns that had been changed where empty (except for some places where it was zero?). When I opened the table in design view it showed the correct data type (currency). When I tried to connect using ADO i got the data - so the data is there, but I need to use ADO to get it.

I hoped there was some fast way to say this recordset=source of table ... or something. I tried OPENROWSET which I found on Microsoft’s webpage, but when copying the code I got "MS OLE.Jet.40" is not registered (something like that) and when I googled that error I only found frustrated people and some tip if 32 and 63-bit.

So...in the end I will just loop through the recordset like planned and live with that :)

Thank you both for your comments and please share more thoughts if you have them.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34238674
You can also directly transfer data and object using Integration Service of SQL Server.
0
 
LVL 1

Author Comment

by:hke72
ID: 34238731
Thank you PatelAlpesh, I will have a look at it, but since I am in a hurry and this is something completly new I do not know if I will be able to use it this time :)

Do you know of any "easy howto"?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34238958
If you need this in a hurry, then try a brute force copy from SQL and Past into Access...

Or copy the SQL to Excel, then bring the Excel into Access.
0
 
LVL 1

Author Comment

by:hke72
ID: 34238984
Well - I need it to run automatically everytime a user runs a report.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 34240254
Well, then this may take some time...
How much have you got...?

Again, if the loop works, why not just go with it...?
0
 
LVL 1

Author Closing Comment

by:hke72
ID: 34255500
Well I looped it using ADO for the SQL server and DAO for the Access-table - it was fast :)

Some times the simplest solution is the best :)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34264334
;-)
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

736 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