How to copy a Variant table

[artefact]

hello,

I've the following function which runs in Access 2010 and returns a variant table. This function runs well
Function dataquery() As Variant()
Dim appExcel As Object
'Excel.Application
Dim lngLastDataRow As Long
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim RS As DAO.Recordset
Dim qry As Variant
Dim aFoo As Variant

Set dbs = CurrentDb
Set appExcel = CreateObject("Excel.Application")
'Set RS = CurrentDb.OpenRecordset("Carnet de commande all fnr", dbOpenSnapshot)
'Set RS = dbs.OpenRecordset("SELECT [Carnet de commande all fnr].Référence FROM [Carnet de commande all fnr];", dbOpenSnapshot)
varFileName = outputfilename
qry = "SELECT [OtifDataToExport].[Mois], " & _
"[OtifDataToExport].[In Advance], " & _
"[OtifDataToExport].[Late], " & _
"[OtifDataToExport].[On time], " & _
"[OtifDataToExport].[Somme] FROM [OtifDataToExport];"
Set RS = dbs.OpenRecordset(qry, dbOpenSnapshot)
With RS
dataquery = .GetRows(.RecordCount)


End With


RS.Close


Set RS = Nothing
Set appExcel = Nothing
End Function


I pass it to excel like via this function:


Function getdata(ByRef databasename As String, bddobjet As Object)
Dim strDBName As String
Dim strMessage As String
Dim appAccess As Object
Dim vartest As String
Dim hd As String
Dim hf As String
Dim recup() As Variant
Set appAccess = bddobjet
hd = Time
'strDBName = "E:\00 - Management\40 - Appro\60 - Tools\30 - Prev\prev.accdb"
strDBName = ThisWorkbook.path & "" & databasename
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
With appAccess
'.OpenCurrentDatabase strDBName
'.Visible = False ' Useful for debugging when true
'.Run "integrationfichierxl", pathOTIFDATA, pathOTIFOUTCOMES
'.Eval ("dataquery()")

End With
recup = appAccess.Eval("dataquery()")


For I = 1 To UBound(appAccess.Eval("dataquery()"), 1)
For J = 1 To UBound(appAccess.Eval("dataquery()"), 2)
K = K + 1
Next J
Next

It seems that I'm unable to copy the variant table as the excel keeps throwing a error, Incompatibility type.

Have you got any clue about this issue?

Thanks.

Arte

 

[ranman256]

you dont need any of this code,
run a COPYFROMRECORDSET function

 

[artefact]

Hello what do mean by copyfromrecordset? As i'm calling this fonction from excel, i need to perform action from à copy of this recordset .

 

[xenou]

Why are you creating an excel object in this function.
Why are you even creating the function in Access. Can't you just get the recordset data from Excel using the same approach?
Also why are you using eval()?
And probably you don't know what your data type is since you are calling it a table and its actually a variant array. You need to at least figure out what datatypes you are using, and preferably don't use variants.


Note that it probably would be better to use the recordset rather than .getrows() to turn it into an array - but not really sure what you are doing with this data.

I'd suggest you put your function in Excel, not in Access, and possibly (at least at first) right in the subroutine in Excel, rather than in a function, so you can get it working. Then later on move it out into a function (if you want to - but working with variant arrays is extremely unpleasant for beginners).