HELP: VBA Code to extract data from an SQL Server database and put it into Access Database

[Clinton M James]

Hi all,

Is there a quick way to do a query on an SQL Server database and have the
output put direct into an Access Database?

I know how to do it by grabbing the records and then inserting each result
one by one, but this takes forever to complete and I am after a quick
solution, if it exists.

Is anybody able to assist ? is it possible?

Thanks in advance for any help!

Regards
Clint

 

[Guest]

If you have the option of pulling it in to Access from SQL-server rather than
pushing it from SQL Server to Access, then the easiest way is to do an "sql
pass through query" in Access.
1. In Access, create a new blank query in design mode.
2. In the menu bar Query - SQL Specific - Pass-Through (NB can also get to
this from the right-click menu in the tables section of the query editor or
from the SQL button on the query toolbar).
3. The view should now be a blank text entry. Paste your sql code here. NB
this text box makes no attempt to be an editor, so I always write the query
in a text editor or in Query Analyzer and then paste it in.
4. Click the Properties icon on the toolbar (or right click in the title bar
of the SQL text box, and choose Properties, or View-Properties from the menu).
5. Click in the "ODBC Connect Str" field and a [...] button will appear to
the right - choose this and set up your SQL Server connection (or just enter
the connect string if you know it).
6. Make sure the "Returns Records" field is Yes (this is probably the
default).
7. Close the properties box and close/save the query, then run it.