<div>
You would need to use an append query. &nbsp;The simple solution is to link to the SQL Server table and use an Access - NOT T-SQL pass-through query to select data from the linked server table and append it to the local table.<br />
<br />
If you want to stick with ADODB, you probably need to write code to open a recordset and insert rows into the local table. &nbsp;This will almost certainly be much slower (depending on the number of records you are copying) than an Access querydef using a linked and a local table.
</div>


Owner, Dev-Soln LLC

Dale Fye

JamesNT

<div>
I didn't write the code because the code loop will always be slower than an append query with large recordsets so it would never be my first choice for a solution.
</div>


<div>
ATTENTION MODERATORS:<br />
<br />
Another IT emergency has arisen. &nbsp;I'll circle back around to this question ASAP.<br />
<br />
James
</div>


<div>
Thank you all for your assistance. &nbsp;By the way, in your code above, it should be:<br />
<br />
for i = 0 to rs.Field.Count -1<br />
<br />
James
</div>


<div>
As long as you don't have too many rows, the code loop will be fine. &nbsp;Queries are always more efficient for bulk operations so I would never use code for this.
</div>


<div>
The most we'll ever have is 2000 rows for now. &nbsp;The data and the queries are already in SQL Server. &nbsp;By the time this becomes a problem, we'll be out of Access and into a full .Net app.<br />
<br />
James
</div>


<div>
<div class="content wysiwyg-content">
I have an ADODB.Recordset in Access VBA that I need flushed out to a table in Access. &nbsp;<br />
<br />

<pre><code class="code-1 nolinks" id="code-10-29136382-1"><span>Dim RowSet as ADODB.Recordset</span>
<span>comm.CommandText = &quot;[qryA042 new claims payors - Need Groups]&quot;</span>
<span>comm.CommandType = adCmdTable</span>
<span>Set RowSet = comm.Execute</span>
</code></pre>
<br />
The data is coming from a SQL Server view hence why using ADODB. &nbsp;So how do I get that into a table in the current Access database?<br />
<br />
James
</div>
</div>


I have an ADODB.Recordset in Access VBA that I need flushed out to a table in Access.  

(CODE)

The data is coming from a SQL Server view hence why using ADODB.  So how do I get that into a table in the current Access database?

James

Flush ADODB.Record into Access Table

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.