Data view in excel from database

I data input in excel cells then view from database.. below my code….. please answer me

 

Sub getdatafromaccess()
Dim dbfullname As String
Dim connect As String, source As String
Dim connection As ADODB.connection
Dim recordset As ADODB.recordset
Dim col As Integer

 

‘database path info
dbfullname = “C:UsersELCOT-LenovoDocumentsDatabase1.accdb”

‘open the connection
Set connection = New ADODB.connection
connect = “provider=microsoft.ace.oledb.12.0;”
connect = connect & “data source=” & dbfullname & “;”
connection.Open ConnectionString:=connect

‘create recordset
Set recordset = New ADODB.recordset
With recordset

‘filter data
source = “select * from table1 where[username]=’sathiya'”
‘source = “select * from table1 where[Process]=’fa'”

.Open source:=source, ActiveConnection:=connection

‘write field name
For col = 0 To recordset.Fields.Count – 1
  Range(“a1”).Offset(0, col).Value = recordset.Fields(col).Name
  Next
  
  ‘write recordset
  Range(“a1”).Offset(1, 0).CopyFromRecordset recordset
  
  End With
  
  
  ActiveSheet.Columns.AutoFit
  
  Set recordset = Nothing
  connection.Close
  Set connection = Nothing
  
 

 

End Sub

By: Sathiyaseelan

Leave a Reply

Your email address will not be published. Required fields are marked *