Bulk Insert ADO Recordset into Linked SQL Server table (1 Viewer)

workindan

New member
Local time
Today, 02:29
Joined
May 26, 2011
Messages
4
I've been crawling the web but I can't seem to pin an answer down for my situation...

Currently I use an ADODB.Recordset to gather info from a Sybase connection

with the ADO recordset, I then loop through each record and perform an insert.

Do while Adodata.eof = false
docmd.runsql "INSERT INTO tblName VALUES '" & adoddata("fieldname") & "','" ...
adodata.movenext
Loop


Is there a faster way to take the ADO recordset and insert it into the linked table on Access?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:29
Joined
Jan 20, 2009
Messages
12,849
Short of linking the SQL table and using a query instead, no.
But I would suggest some changes to your coding style.
Use Execute instead of RunSQL. it doesn't need Warnings off and it is faster.
Use a With block.

Code:
With Adodata
   Do While Not .EOF
      CurrentDb.Execute "INSERT INTO tblName VALUES '" & !fieldname & "','" ... , adFailOnError
      .MoveNext
   Loop
End With
 

vbaInet

AWF VIP
Local time
Today, 09:29
Joined
Jan 22, 2010
Messages
26,374
How many records are you dealing with? Depending on the number of records that need inserting, it might be quicker to perform a data dump from Sybase and upload that file into Access.
 

workindan

New member
Local time
Today, 02:29
Joined
May 26, 2011
Messages
4
I'm dealing with 100-10,000 records at a time. I'm trying to break it up as I go through over 1,000,000 records that need processing, each one returning 8-10 records out of Sybase.

I've linked the Sybase tables and am attempting to run Access/Jet sql from the Linked Sybase tables to the Linked SQL Server table...is there anything wrong with this code/syntax:


Currentdb.Execute "INSERT INTO tblSqlServer (col1,col2...) " & _
"select a.col, b.col, c.col, .... " & _
"FROM schema_tblSybase a noholdlock " & _
"join schema_tblSybase b noholdlock on a.joinfield = b.joinfield " & _
"....(other joins that work fine in sybase)..." & _
"where a.id IN (" & IdSET & ") and b.date >= '2012-01-01' and b.date < dateadd(month,3,'2012-01-01') "

(idSET is a string of ID's produced with a vba loop that pulls IDs from a local table in the form of 'id','id','id' )

I get a syntax error in the FROM clause and can't seem to figure out what's wrong...I hope the jetsql/sybase odbc driver isn't having issues passing anything.

Thoughts?
 
Last edited:

mdlueck

Sr. Application Developer
Local time
Today, 05:29
Joined
Jun 23, 2011
Messages
2,631
Currently I use an ADODB.Recordset to gather info from a Sybase connection

adoCMD objects are a bit cleaner to code than adoRS.

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Though I can not guarantee a speed improvement. It is still individual transactions to be executed. Oh, and that syntax is for going against a local Access table. You will need to provide a different (BE DB) connection in your case.

Oh, or are you trying to download records from a SQL BE DB into an Access FE table? If that is the case then nested DAO.QueryDef objects are definitely the way to go, and AVOID loop / INSERT code!! I do that and am able to download 145MB worth of records from an AS/400 in 40 seconds!

"Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them"
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605
 

vbaInet

AWF VIP
Local time
Today, 09:29
Joined
Jan 22, 2010
Messages
26,374
If you're getting a syntax error Debug.Print the SQL statement to the Immediate Window so you can see what's missing. Post the result here.
 

Users who are viewing this thread

Top Bottom