Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transfer recordset to Access-table

Posted on 2010-11-29
10
Medium Priority
?
381 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
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 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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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: …

824 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