Copy a Sql Server Table to Access Using VBA
Posted on 2005-03-29
I have been researching a way to import a table from a SQL Server to an access table without using any wizards or DTS. Every day we have to update surveys from our website to our in house access database. So the survey results are stored on a SQL Server which is located at our web hosting company.
So far I have created a stored procedure that returns a temporary table. I need to somehow copy that temp table to an Access table. I want to avoid using Import features because I want a non-tech user to click a button which will handle the import process.
Can it be done using ADO? I couldnt imagine looping through 50,000 records and copying each field to an Access table. That sounds like a nightmare!
Also, I have heard DTS could schedule stuff but it sounds really hard. I am new at all of this.