[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Transfer recordset to Access-table

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?
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
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...

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
hke72Author Commented:
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Alpesh PatelAssistant ConsultantCommented:
You can also directly transfer data and object using Integration Service of SQL Server.
hke72Author Commented:
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"?
Jeffrey CoachmanMIS LiasonCommented:
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.
hke72Author Commented:
Well - I need it to run automatically everytime a user runs a report.
Jeffrey CoachmanMIS LiasonCommented:
Well, then this may take some time...
How much have you got...?

Again, if the loop works, why not just go with it...?
hke72Author Commented:
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 :)
Jeffrey CoachmanMIS LiasonCommented:

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now