asked on

Flush ADODB.Record into Access Table

I have an ADODB.Recordset in Access VBA that I need flushed out to a table in Access.

Dim RowSet as ADODB.Recordset
comm.CommandText = "[qryA042 new claims payors - Need Groups]"
comm.CommandType = adCmdTable
Set RowSet = comm.Execute

Open in new window

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

PatHartman

You would need to use an append query. 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.

If you want to stick with ADODB, you probably need to write code to open a recordset and insert rows into the local table. 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.

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

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.

JamesNT

ASKER

ATTENTION MODERATORS:

Another IT emergency has arisen. I'll circle back around to this question ASAP.

James

JamesNT

ASKER

Thank you all for your assistance. By the way, in your code above, it should be:

for i = 0 to rs.Field.Count -1

James

PatHartman

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

JamesNT

ASKER

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

James