PDA

View Full Version : Export From Excel To Access!



gents
10-13-2017, 10:38 PM
Hi everyone,

title should be "Having trouble EXPORTING a range from Excel into a field in Access"

so i have to export a range (A2:A7) from a worksheet defined as wksCLient, into a table in Access which is tblClient. Now I am able to export a value from one cell, e.g. (A2), and the value comes up in the desired first row of the specific field in the access table. However, when ever i try to export a whole range (A2:A7), i get a 'Mismatch error'.

My code is here:

Private Sub cmdExportData_Click()
Dim rstExport As ADODB.Recordset
Dim strClientDatabase As String
Dim strConnection As String
Dim wksClient As Worksheet






Set wksClient = Application.Workbooks("Cheng_104_FIT1013A2.xlsm").Worksheets("Client")




strClientDatabase = ActiveWorkbook.Path & "\FIT1013 S2 2017 Assignment 2.accdb"
strConnection = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & _
strClientDatabase & ";Persist Security Info=False"

'Create a recordset
Set rstExport = New ADODB.Recordset

'Delete all records in Client table
rstExport.Open "Delete * from tblClient", strConnection, adOpenStatic
Set rstExport = Nothing

'recreate same recordset again
'Open up interface for data transfer
Set rstExport = New ADODB.Recordset
rstExport.Open Source:="tblClient", ActiveConnection:=strConnection, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable

With rstExport
.AddNew
.Fields![Client No] = wksClient.Range(A2)
.Save


End With

'destroy the record set
rstExport.Close
Set rstExport = Nothing

End Sub


The error is stemming from the line '.Fields![Client No] = ....

It is only this line that is causing the error, all the other code is working erfectly fine