VB Forum / Database Access / October 2007

Hello,,

Iam just trying to read a sheet from excel and transfer that excel
information into my Access database.

When i try to import the excel data Iam getting Object variable or with
block variable not set...

Iam using VB6.0 and Ms-Access .

The folloiw is my code

Dim rs2 As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Private Sub cmdBrowse_Click()
   cmndFilePath.CancelError = True
   cmndFilePath.Filter = "Databases|*.XLS"
   On Error Resume Next
   cmndFilePath.ShowOpen
   Data1.DatabaseName = cmndFilePath.FileName
   txtFilePath.Text = cmndFilePath.FileName
   Data1.Refresh
End Sub
Private Sub cmdImport_Click()
   If Data1.RecordSource <> "Sheet1$" Then
       MsgBox "Invalid Sheet"
       Exit Sub
   Else
      Set rs2 = Nothing
      rs2.Open "Select * from TrackingDetails ", con, adOpenKeyset, 2
   
      While Not Data1.Recordset.EOF
           rs2.AddNew
           rs2(0) = Data1.Recordset(1)
           rs2(1) = Data1.Recordset(2)
           rs2.Update
           Data1.Recordset.MoveNext
       Wend
   End If
   MsgBox "Transferred completely"
 
   Call Display
End Sub
Public Sub Display()
   'Set rs1 = New Recordset
   rs1.Open "select *  from TrackingDetails", con, adOpenStatic,
adLockOptimistic
   Set DataGrid1.DataSource = rs1
End Sub

Private Sub Form_Load()
   Call GetConn
End Sub

The below code isin Differnt module

Public con As ADODB.Connection
'Public con As New ADODB.Connection
Public Sub GetConn()
   Set con = New ADODB.Connection
   Dim dbname As String
   dbname = App.Path & "\TrackingStatus.mdb"
   con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbname & ";Persist Security Info=False"
   con.Open
End Sub

Can anyone help me in solving the above said problem???

Thanks and Regards
Nick,