DAO TransferSpreadsheet

[xlStar]

I am in the middle on building a VBA in Excel that have DAO connection to Access. I am just started to learn about DAO and ADO.

I am confused.

Is there a DAO version of ADO's DoCmd.Transferspreadsheet (import) VBA script?

 

[PHV]

DoCmd.Transferspreadsheet is a method of the Access.Application object and has nothing to do with ADO or DAO.
Have a look at the CopyFromRecordset method of the Excel.Range object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlStar]

I have manage to use CopyFromRecordSet which worked absolutely fine.

But I would like a quick way of the transfer being the otherway round, i.e. CopyToRecordSet. But no such thing!

I have manage it by using the .AddNew function, see below.


Dim Data(1 To 13)
rowno = 2
Do
If Not Wks.Range("A" & rowno).Value = Empty Then
For colno = 1 To 13
Data(colno) = Wks.Cells(rowno, colno).Value
Next colno

Set rs = db.OpenRecordset("Current OPEN Amounts")
With rs
.AddNew
!DocNumber = Data(1)
!Company = Data(2)
!Item = Data(3)
!InvDate = Data(4)
!DueDate = Data(5)
!DateOfReveral = Data(6)
!OriginalAmount = Data(7)
!OpenAmount = Data(8)
!d15to28 = Data(9)
!d29to42 = Data(10)
!d43to90 = Data(11)
!d90to180 = Data(12)
!Over180 = Data(13)
.Update
End With
Set rs = Nothing
End If
rowno = rowno + 1

Application.StatusBar = "Uploading Data " & rowno
Loop Until Wks.Range("A" & rowno).Value = Empty



This works, but there are 6000 rows and it takes about a minutes to feed the data into Access.

 

[JerryKlmns]

A different approach would be to link this worksheet as a table in your access database and run an append query

Insert Into [Current OPEN Amounts](fields you need comma separated) Select (fields you need in the corresponding order comma separated) From LinkedWorksheet;"

When linking check "First Row Contains Field Names"