by Karthick @ The Hindu (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Wed 5th July 2006
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Getting the excel file in to the grid and store that data in Access DB
API Declarations
Dim con1 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
VBcoders
Private Sub Command1_Click()
con1.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\karthik\mydb.mdb;Uid=Admin;Pwd=;")
Set rs1 = New ADODB.Recordset
rs1.Open Source:="grid", ActiveConnection:=con1, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
'grid is the tablename in access
Do While Not rs1.EOF()
rs1.Delete
rs1.MoveNext
Loop
datagrid1.Cols = rs.Fields.Count
Dim increase, row As Integer
increase = rs.Fields.Count - 1
rs.MoveFirst
j = 1
Do While Not rs.EOF
rs1.AddNew
For i = 0 To increase
rs1(i) = datagrid1.TextMatrix(j, i)
Next
rs1(i) = Format(Now, "dd/mm/yyyy")
j = j + 1
rs.MoveNext
Loop
rs1.Update
rs1.Close
con1.Close
Set rs1 = Nothing
Set con1 = Nothing
datagrid1.Rows = 0
End Sub
Private Sub Command2_Click()
End
End Sub
Private Sub html_Click()
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\karthik\testfiles\ipo290406.xls;Extended Properties=""Excel 8.0;HDR=no;IMEX=1""")
Set rs = con.Execute("select * from [Sheet1$]")
i = 1
datagrid1.Rows = 2
datagrid1.Cols = rs.Fields.Count
Do While Not rs.EOF
For j = 0 To rs.Fields.Count - 1
'datagrid1.TextMatrix(0, j) = IIf(IsNull(rs.Fields(j).Name), "", rs.Fields(j).Name)
If j = rs.Fields.Count - 1 Then
On Error Resume Next
datagrid1.TextMatrix(i, j) = IIf(IsDate(CDate(rs.Fields(j).Value)), Format(CDate(rs.Fields(j).Value), "dd/mm/yyyy"), "")
Else
datagrid1.TextMatrix(i, j) = IIf(IsNull(rs.Fields(j).Value), "", rs.Fields(j).Value)
End If
Next
i = i + 1
If datagrid1.Rows <= i Then
datagrid1.Rows = datagrid1.Rows + 1
End If
rs.MoveNext
Loop
datagrid1.Rows = datagrid1.Rows - 1
datagrid1.AllowUserResizing = flexResizeBoth
End Sub
No comments have been posted about Getting the excel file in to the grid and store that data in Access DB. Why not be the first to post a comment about Getting the excel file in to the grid and store that data in Access DB.