Solved

Transfer recordset to Access-table

Posted on 2010-11-29
10
375 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

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.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

773 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