Saving a ado recordset without a loop

Results 1 to 9 of 9

Thread: Saving a ado recordset without a loop

  1. #1
    Join Date
    Dec 1969
    Posts
    19

    Default Saving a ado recordset without a loop

    I&#039;ve got some data on a SQL server that i need to transfer to an access database. At the moment i&#039;ve got the code below. It opens up the record set then loops through each row saving each row to the access database. I was wondering if anyone knew a better way because it&#039;s a little slow and a little ugly.<BR> <BR>[code language="VbScript"]<BR>&#039;ADODB Declerations<BR> Set oconn = New ADODB.Connection<BR> Set oComm = New ADODB.Command<BR> Set rst = New ADODB.Recordset<BR> <BR> &#039;ADODB Connection<BR> oconn.ConnectionString = DSNString<BR> oconn.Open<BR> <BR> &#039;ADODB Command<BR> Set oComm.ActiveConnection = oconn<BR> oComm.CommandType = adCmdStoredProc<BR> oComm.CommandText = "stoNewMachineQuestions"<BR> <BR> &#039;ADODB Recordset<BR> rst.Open oComm, , adOpenStatic, adLockReadOnly<BR> <BR> For intI = 0 To noOfMachines<BR> rst.MoveFirst<BR> Do Until rst.EOF<BR> CurrentDb.Execute ("INSERT INTO tblChkListExistMCLocal ( QID, MachineID, Title, Question, SectionNo) SELECT 1 AS QID, " & macArray(intI) & " AS MachineID, &#039;hgfhgfhfg&#039; AS Title, &#039;hgfhgf&#039; AS Question, 1 AS SectionNo;")<BR> rst.MoveNext<BR> Loop<BR> Next<BR> <BR> Set oComm = Nothing<BR> Set oconn = Nothing<BR>[/code]

  2. #2
    Join Date
    Dec 1969
    Posts
    16,931

    Default RE: Saving a ado recordset without a loop

    A much better solution would be to add the MS Access database as a linked server.<BR><BR>Then have a stored procedure that does this directly as a batch. Something like:<BR>[language code="T-SQL"]<BR>INSERT MyLinkedServer.tblChkListExistMCLocal<BR> (QID, MachineID, Title, Question, SectionNo<BR> SELECT 1, FieldA, FieldB, FieldC, FieldD FROM MySQLTable<BR>[/code]<BR><BR>That&#039;d be a LOAD faster.<BR><BR>Craig.

  3. #3
    Join Date
    Dec 1969
    Posts
    16,931

    Default LOL

    I&#039;m an idiot.<BR><BR>[code language="T-SQL"]<BR>INSERT MyLinkedServer.tblChkListExistMCLocal<BR> (QID, MachineID, Title, Question, SectionNo<BR> SELECT 1, FieldA, FieldB, FieldC, FieldD FROM MySQLTable<BR>[/code]<BR><BR>Lets try that.<BR><BR>Craig.

  4. #4
    Join Date
    Dec 1969
    Posts
    18,177

    Default OT: Bit dyslexic? (eop)

    .


  5. #5
    Join Date
    Dec 1969
    Posts
    16,931

    Default RE: OT: Bit dyslexic? (eop)

    Not that I know of.<BR><BR>Just not paying attention.<BR>:-)<BR><BR>Craig.

  6. #6
    Join Date
    Dec 1969
    Posts
    19

    Default RE: LOL

    linkedServer now your talking, never tried that. how do i declare it/set it up?

  7. #7
    Join Date
    Dec 1969
    Posts
    16,931

    Default RE: LOL

    In Enterprise Manager it&#039;s quite simple.<BR><BR>My suggestion is to read the articles in Books Online on the subject. They&#039;re not exceptionally good, but they&#039;re a decent starting point.<BR><BR>Craig.

  8. #8
    Join Date
    Dec 1969
    Posts
    19

    Default RE: LOL

    Right just had a few looks at the code to do that, might not be appropriate since i&#039;m sending this little beast out to like 20 users, and don&#039;t think it&#039;d work i&#039;d never be able to keep it managed.

  9. #9
    Join Date
    Dec 1969
    Posts
    18,177

    Default DTS an option?

    In SQL Server, you could setup a DTS job to create the MS Access database.<BR><BR>Then, you could have ASP pick up the file and send it out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •