Solved

Transfer recordset to Access-table

Posted on 2010-11-29
10
376 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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